CASE STATEMENTS

Question

CASE STATEMENTS

vivekkam

SSC Enthusiast

Points: 152
More actions
September 5, 2012 at 8:33 pm

#280819

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_ID
WHERE (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_ID
WHERE (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 the highlighted text. How can I merge it into one using CASE ?
Waiting for reply.
Vivek
Vivek Kamath

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

t.brown 89142 SSCommitted Points: 1530 More actions · Answer 1

If the where clause in both is the same, you can simply select both @Sums in the same statment, put all the common parts in the WITH CTE, and put the different filters in subqueries to create the totals.

... something like this ... (not tested as I don't have your table definitions)

DECLARE @TOTAL FLOAT;

;WITH Part1 AS (

SELECT -- ... whatever fields needed in the calculations ...

Summary, OrdMain.AddDate, OrdItem.PriceEach, OrdItem.Quan, OrdMain.SubTotal, GrandTotal, 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_ID

WHERE (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%')

)

SELECT

@TOTAL = (SELECT SUM(

CASE (LEFT(RIGHT(Rtrim(summary), 12), 5))

WHEN 'EURO/' THEN PriceEach * Quan * CONVERT(float(7), RIGHT(summary, 7))

ELSE PriceEach * Quan

END)

FROM Part1

WHERE SubTotal = 0.01 )

+

( SELECT (

(SUM(CASE (LEFT(RIGHT(Rtrim(summary), 12), 5))

WHEN 'EURO/' THEN PriceEach * Quan * CONVERT(float(7), RIGHT(summary, 7))

ELSE PriceEach * Quan

END))

-

(SUM(DISTINCT GrandTotal) - SUM(DISTINCT ShipHandle) )

)

FROM Part1

WHERE SubTotal <> 0.01 );

rhythmk SSCertifiable Points: 7271 More actions · Answer 2

vivekkam (9/5/2012)
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_ID
WHERE (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_ID
WHERE (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 the highlighted text. How can I merge it into one using CASE ?
Waiting for reply.
Vivek
Vivek Kamath

Please post table DDL and sample data with expected output.

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Something like this:

SELECT

SubTotalSplit = CASE WHEN OrdMain.SubTotal = 0.01 THEN '= 0.01' ELSE '<> 0.01' END,

sumx = ( (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_ID

WHERE (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%')

GROUP BY CASE WHEN OrdMain.SubTotal = 0.01 THEN '= 0.01' ELSE '<> 0.01' END

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Do you need both values?

Or you're just adding them at the end?

If you're just adding them, you just need to drop the filter and keep one query.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2