W.E.B
asked on
case if sql2008
Hello,
can you please help,
how can I input a case statement to below code.
case if SameDay is NULL then SameDay_Cost is Null Else SameDay_Cost end
can you please help,
how can I input a case statement to below code.
case if SameDay is NULL then SameDay_Cost is Null Else SameDay_Cost end
SELECT Table1.AccountstartDate,Table1.AccountNumber,Table1.AccountCode,Table1.Name,
[OVN] = SUM(Table1.[OVN]), [OVN_Cost] = SUM(Table1.[OVN]) - (SUM(Table1.[OVN])*0.25),[OVN_NET] = SUM(Table1.[OVN]) - (SUM(Table1.[OVN])*0.75),
[SameDay] = SUM(Table1.[SameDAY]),
[SameDay_Cost] = (SUM(Table1.[PUDriverCommission])+SUM(Table1.[DELDriverCommission])+SUM(Table1.[Sales1Commission])+SUM(Table1.[Sales2Commission])),
[SameDay_NET] = (SUM(Table1.[SameDAY]) - (SUM(Table1.[PUDriverCommission])+SUM(Table1.[DELDriverCommission])+SUM(Table1.[Sales1Commission])+SUM(Table1.[Sales2Commission])))
FROM ( SELECT fo.AccountNumber,cl.AccountCode,cl.AccountstartDate, cl.Name,
(Select SUM(fo.SubTotalAmount) where fo.ServiceTypeID in (122,123,124)) AS [OVN],
(Select SUM(fo.SubTotalAmount) * 0.25 where fo.ServiceTypeID in (122,123,124)) AS [OVN_Cost],
(Select SUM(fo.SubTotalAmount) * 0.75 where fo.ServiceTypeID in (122,123,124)) AS [OVN_Net],
(Select SUM(fo.SubTotalAmount) where fo.ServiceTypeID NOT in (122,123,124)) AS [SameDay],
(Select (SUM(fo.PUDriverCommission) + SUM(fo.DELDriverCommission) + SUM(fo.Sales1Commission) + SUM(fo.Sales2Commission)) where fo.ServiceTypeID NOT in (122,123,124)) AS [SameDay_Cost],
(Select SUM(fo.SubTotalAmount) - (SUM(fo.PUDriverCommission) + SUM(fo.DELDriverCommission) + SUM(fo.Sales1Commission) + SUM(fo.Sales2Commission)) where fo.ServiceTypeID NOT in (122,123,124)) AS [SameDay_NET],
SUM(fo.TotalAmount) AS [TotalAmount] ,SUM(fo.SubTotalAmount) AS [SubTotalAmount], SUM(fo.FuelAmount) AS [FuelAmount],
SUM(fo.PUDriverCommission) AS [PUDriverCommission],SUM(fo.DELDriverCommission) AS [DELDriverCommission],SUM(fo.PUDriverCommission) AS [Driver3Commission],SUM(fo.DELDriverCommission) AS [Driver4Commission],
SUM(fo.Sales1Commission) AS [Sales1Commission],SUM(fo.Sales2Commission) AS [Sales2Commission]
FROM FinalizedOrders fo JOIN Clients cl ON fo.AccountNumber = cl.AccountNumber
WHERE ((fo.OrderDate >= '10-01-2013') And (fo.OrderDate <='11-01-2013'))
AND fo.AccountNumber Not in (9999,715,3335,6,511,818,987,1988,1999,9995,8500,8501,8502,1006,99990,99991,99992,99993,99994,99995,99996,99997,99998,99999,4629,9992,9996,9997,8421894)
GROUP BY fo.AccountNumber,cl.AccountCode,cl.AccountstartDate, cl.Name,fo.ServiceTypeID
) Table1
GROUP BY Table1.AccountstartDate,Table1.AccountNumber,Table1.AccountCode,Table1.Name
ORDER BY Table1.AccountCode
thanks,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER