I'm unclear on the CustId and Id fields - not sure how the joins are really supposed to work...Code:Select dJobDate "Date" ,sName "Customer" ,Sum(Case When bCleaning<>0 Then mCleaningCost Else 0 End) "Cleaning" ,Sum(Case When bWash<>0 Then mWashCost Else 0 End) "Wash" ,Sum(Case When bService<>0 Then mServiceCost Else 0 End) "Service" ,Sum(Case When bCleaning<>0 Then mCleaningCost Else 0 End +Case When bWash<>0 Then mWashCost Else 0 End +Case When bService<>0 Then mServiceCost Else 0 End) "Total" From Jobs JO Left Join Contracts CO on CO.iId=JO.iId and CO.iCustId=JO=iCustId Left Join Customer CS on CS.iId=CO.iId Group by dJobDate,sName
Also - not sure if a customer can have more than one job row for a date - so I did a SUM() and GROUP BY - that's not really needed if only one job row...




Reply With Quote