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.
| Author |
Topic |
|
vivekkam
Starting Member
5 Posts |
 Posted - 2012-09-05 : 22:22:41
|
| I have two identical queries with only one filter difference between the two queries. Can anybody suggest me a solution using "case" statement as how I can reduce the two queries into one.Here are the queries:SELECT @sum1 = SUM(CASE (LEFT(RIGHT(Rtrim(summary), 12), 5)) WHEN 'EURO/' THEN OrdItem.PriceEach * OrdItem.Quan * CONVERT(float(7), RIGHT(summary, 7)) ELSE OrdItem.PriceEach * OrdItem.Quan END) FROM Org INNER JOIN OrdMain ON Org.Org_ID = OrdMain.Org_ID INNER JOIN OrdItem ON OrdMain.Order_ID = OrdItem.Order_ID INNER JOIN Groups ON OrdMain.Group_ID = Groups.Group_IDWHERE (Org.OrgName = 'Endress+Hauser Process Solutions AG') AND (Groups.GroupName LIKE 'Switzer%') AND (Groups.Country LIKE '%Switzer%') AND (OrdMain.AddDate - 1 < CONVERT(char, '12/31/2011', 101)) AND (OrdMain.OrderStat <> 'cancelled') AND (OrdMain.OrderStat NOT LIKE '%WebQuote%') AND (OrdItem.PartNum NOT LIKE 'ADVANCE%') AND (OrdMain.AddDate >= CONVERT(char, '07/30/2009', 101)) AND (NOT (OrdItem.PartNum LIKE 'SHIPMENT' OR OrdItem.PartNum LIKE 'TRSITE%' OR OrdItem.PartNum LIKE 'WARMWARE' OR OrdItem.PartNum LIKE 'OEM%' OR OrdItem.PartNum LIKE 'ACTIVEX TOOLW%' OR OrdItem.PartNum LIKE 'OPCTOOLW%' OR OrdItem.PartNum LIKE 'UPSYSOPCTOOLW%' OR OrdItem.PartNum LIKE '%MANUAL%')) AND (LEFT(OrdItem.PartNum, 3) NOT IN ('APP', 'ENG', 'TR-')) AND (OrdMain.OrderStat NOT LIKE '%inactive%') AND (OrdMain.SubTotal = 0.01)SELECT @sum2 = ( (SUM(CASE (LEFT(RIGHT(Rtrim(summary), 12), 5)) WHEN 'EURO/' THEN OrdItem.PriceEach * OrdItem.Quan * CONVERT(float(7), RIGHT(summary, 7)) ELSE OrdItem.PriceEach * OrdItem.Quan END)) - (SUM(DISTINCT OrdMain.GrandTotal) - SUM(DISTINCT OrdMain.ShipHandle) ) )FROM Org INNER JOIN OrdMain ON Org.Org_ID = OrdMain.Org_ID INNER JOIN OrdItem ON OrdMain.Order_ID = OrdItem.Order_ID INNER JOIN Groups ON OrdMain.Group_ID = Groups.Group_IDWHERE (Org.OrgName = 'Endress+Hauser Process Solutions AG') AND (Groups.GroupName LIKE 'Switzer%') AND (Groups.Country LIKE '%Switzer%') AND (OrdMain.AddDate - 1 < CONVERT(char, '12/31/2011', 101)) AND (OrdMain.OrderStat <> 'cancelled') AND (OrdMain.OrderStat NOT LIKE '%WebQuote%') AND (OrdMain.AddDate >= CONVERT(char, '07/30/2009', 101)) AND (OrdMain.OrderStat NOT LIKE '%inactive%') AND (LEFT(OrdItem.PartNum, 3) NOT IN ('APP', 'ENG', 'TR-', 'UP-')) AND (NOT (OrdItem.PartNum LIKE 'SHIPMENT' OR OrdItem.PartNum LIKE 'TRSITE%' OR OrdItem.PartNum LIKE 'WARMWARE' OR OrdItem.PartNum LIKE 'OEM%' OR OrdItem.PartNum LIKE 'ACTIVEX TOOLW%' OR OrdItem.PartNum LIKE 'OPCTOOLW%' OR OrdItem.PartNum LIKE 'UPSYSOPCTOOLW%' OR OrdItem.PartNum LIKE '%MANUAL%')) AND (OrdItem.PartNum NOT LIKE 'ADVANCE%') AND (OrdMain.SubTotal <> 0.01)SELECT ( @sum1 + @sum2)The only difference between the two queries are one requires the filter of (OrdMain.SubTotal <> 0.01) and other equal to 0.01. How can I merge it into one using CASE ?Waiting for reply.Vivek |
|
|
sateeshGenpact
Starting Member
6 Posts |
Posted - 2012-09-06 : 03:21:25
|
| Hi Vivek,I didn't get onething here..if you are Summing up at the end that means you would need sum of both except null..the try using "is not null " in your query.Sateesh |
 |
|
|
|
|
|
|
|
CASE STATEMENT