June 10, 2015 at 2:41 pm
I need to create a commission report. basically if you are a certain salesperson 0 commission, if you sell to certain customers, 0 commission, certain products have 0 commission, and finally if non of those conditions apply then look up the commission percentage based on the family code.
attached is a word document with the SQL code, and an Excel file with the answer set.
The code seems correct, but of course is not. Just wondering if someone can explain where I went wrong, thank you.
June 10, 2015 at 4:14 pm
Maybe you should read this. Most people don't want to download files from people they don't know...
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
June 15, 2015 at 1:06 pm
See if you can try posting some sample data from your table(Just a few rows should suffice) and also paste in your code.
June 15, 2015 at 2:17 pm
The column t00.[CustNo] is never NULL, so you'll always end up with a value of 0.
You don't need the nested CASE, you can just add more conditions as needed.
Select t00.[InvDate]
,t00.[CustNo]
,t00.[Salesman]
,t00.[Quantity]
,t00.[ExtendedPrice]
,t00.[ExtendedCost]
,t00.[ItemNo]
,t00.[InvoiceNo]
,t00.[LineNo]
,t00.[Warehouse]
,t00.[chargecust]
,t01.[customerid] AS CustomerIdT01
,t02.[salesman] AS SalesmanT02
,t03.itemid AS ItemIdT03
,t03.[commissionperc]AS ItemComPercT03
,t04.[productfamilyid] AS ProductFamilyIdT04
,T05.[ProductFamilyID] AS ProductFamilyIdT05
,t05.[commissionperc]as ProdFamilyComPercT05
,T06.[Customerid] AS CustomerIdT06
,t06.[commissionperc] AS CommissionPercT06
,Case when [t00].[CustNo]is not null then 0
when [t02].[salesman]is not null then 0
when [t06].[customerid] is not null then [t06].[commissionperc]
when [t03].[ItemID] is Not Null then [t03].[commissionperc]
Else [t05].[commissionperc] end AS BaseCommPerc
FROM [US02301_DWH].[dbo].[Fact_InvoiceAnalysis]t00
left join [dbo].[manualNOcommissioncustomer]as t01 on t00.[chargecust]=t01.[customerid]
left join [dbo].[manualNOcommissionsalesgroup] as t02 on t00.[salesman]=t02.[salesman]
left join [dbo].[manualcommissionitem] as t03 on t00.[itemno]=t03.[itemid]
left join [dbo].[itemmaster] as t04 on t00.[itemno] = t04.[itemid]
left join [dbo].[ManualCommisionProductFamily]as t05 on [t04].[productfamilyid] = [t05].[productfamilyid]
LEFT JOIN [dbo].[ManualFixedCommissionCustomer] as T06 on [t00].[custno]=[t06].[customerid]
where YEAR([invdate])='2015' and T00.salesman='16'
order by t00.Salesman desc
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply