Solved

case if sql2008

Posted on 2013-11-14
3
293 Views
Last Modified: 2013-11-15
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
Comment
Question by:W.E.B
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 37

Accepted Solution

by:
ValentinoV earned 300 total points
ID: 39650759
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
 

Author Closing Comment

by:W.E.B
ID: 39650812
Thank you,
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question