Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

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

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 

Open in new window

thanks,
0
W.E.B
Asked:
W.E.B
1 Solution
 
ValentinoVBI ConsultantCommented:
How about this:

[SameDay_Cost] = case when SUM(Table1.[SameDAY]) = 0
    then 0
    else (SUM(Table1.[PUDriverCommission])+SUM(Table1.[DELDriverCommission])+SUM(Table1.[Sales1Commission])+SUM(Table1.[Sales2Commission]))
  end

Open in new window

Replace 0 by null if you really meant null...
0
 
W.E.BAuthor Commented:
Thank you,
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now