If Then or Case - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

If Then or Case

Author

Topic

rmrper99
Starting Member

29 Posts

Posted - 2008-11-14 : 08:35:48

I have a table that has multiple types of orders in it. The options are DE, DWE, CE, & CWE. For each project, there can be one order that is a DE, one order that is a DWE, one order that is a CE, and one order that is a CWE.

When I run the following statement, I get one line for each record (which I understand). However, I would like to get one line that shows the project, customer, DE, DWE, CE, and CWE. I can figure out how to make this happen since that is 4 separate records in the table.

SELECT projectid, custid,
CASE WHEN SOTYPEID LIKE 'DWE%' THEN ORDNBR ELSE NULL END AS DWE,
CASE SOTYPEID WHEN 'DE' THEN ORDNBR ELSE NULL END AS DE,
CASE SOTYPEID WHEN 'CWE' THEN ORDNBR ELSE NULL END AS CWE,
CASE SOTYPEID WHEN 'CE' THEN ORDNBR ELSE NULL END AS CE,
FROM SALESORDER

Can someone tell me what I am missing?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 08:58:06

[code]SELECT projectid, custid,
MAX(CASE WHEN SOTYPEID LIKE 'DWE%' THEN ORDNBR ELSE NULL END) AS DWE,
MAX(CASE SOTYPEID WHEN 'DE' THEN ORDNBR ELSE NULL END) AS DE,
MAX(CASE SOTYPEID WHEN 'CWE' THEN ORDNBR ELSE NULL END) AS CWE,
MAX(CASE SOTYPEID WHEN 'CE' THEN ORDNBR ELSE NULL END) AS CE
FROM SALESORDER
GROUP BY projectid, custid[/code]

rmrper99
Starting Member

29 Posts

Posted - 2008-11-14 : 09:27:26

You are AWESOME. I tried the group by but didn't realize the MAX piece!
Thank you!!!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 11:10:44

Cheers

rmrper99
Starting Member

29 Posts

Posted - 2008-11-14 : 15:28:46

Ok, now I have another question. For each header record, I have multiple line records. For the

DWE order number from the MAX statement, I want to sum the c.TOTORD but only where the c.invtid meets certain criteria.

This is what I have. Can someone tell me what I am missing or doing wrong?

SELECT a.PROJECTID, a.CUSTID,
MAX(CASE a.SOTYPEID WHEN 'DWE' THEN a.ORDNBR ELSE NULL END) AS DWE,

--In the below piece, I want to sum the total of the line items that don't have certain inventory items
--using the DWE order number that came from the MAX statement above
SUM(CASE a.SOTYPEID WHEN 'DWE' THEN
SUM(c.TOTORD WHEN c.INVTID not like '2%' and (c.INVTID <'30050' or c.INVTID > '30079') )
ELSE NULL END) AS DWE_EST_COST,
MAX(CASE a.SOTYPEID WHEN 'DE' THEN a.ORDNBR ELSE NULL END) AS DE,
FROM SOHEADER a INNER JOIN SOLINE c ON a.ordnbr = c.ordnbr
WHERE a.cancelled = 0
Group By a.PROJECTID, a.CUSTID

rmrper99
Starting Member

29 Posts

Posted - 2008-11-14 : 15:54:02

I think I figured it out in a new view.
Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 23:56:00

no need of a new view. just use

SUM(CASE a.SOTYPEID WHEN 'DWE' AND c.INVTID not like '2%' and (c.INVTID <'30050' or c.INVTID > '30079') THEN c.TOTORD ELSE NULL END) AS DWE_EST_COST

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources