UNION City! Can I consolidate it? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

UNION City! Can I consolidate it?

Author

Topic

katethegreat
Starting Member

13 Posts

Posted - 2012-03-30 : 17:29:45

Hello my esteemed SQL fellows. I've spent the last few days painstakingly putting together the most convoluted, multi-join, multi-union query. I'm happy to say that I've finally got it spitting out the data in a way that it needs to be, but the query itself is incredibly long and repetetive so I'm wondering if there's any way to consolidate/shorten it while still achieving the same ultimate result. Have a look and tell me if I've done something stupid:

SELECT
o.OrderID
, o.OrderDate
, o.PaymentAmount
, o.CustomerID
, o.SalesTax1
, (SELECT PaymentMethod FROM PaymentMethods WITH (NOLOCK) WHERE PaymentMethods.PaymentMethodID = o.PaymentMethodID) As PaymentMethod
, orderdetails.productcode
, orderdetails.ProductPrice
, orderdetails.quantity
, (SELECT shippingmethodname FROM shippingmethods WHERE shippingmethods.shippingmethodid = o.shippingmethodid) as shiptype

FROM Orders o WITH (NOLOCK)
LEFT JOIN ( SELECT OrderID, productcode, productprice, quantity FROM OrderDetails WITH (NOLOCK) GROUP BY OrderID, productcode, productprice, quantity) OrderDetails
ON o.OrderID = OrderDetails.OrderID
WHERE 1=1
AND o.OrderStatus <> 'Cancelled'
AND o.Total_Payment_Received > 0
AND o.OrderID > 22647

UNION ALL

SELECT DISTINCT
o.OrderID
, o.OrderDate
, o.PaymentAmount
, o.CustomerID
, o.SalesTax1
, (SELECT PaymentMethod FROM PaymentMethods WITH (NOLOCK) WHERE PaymentMethods.PaymentMethodID = o.PaymentMethodID) As PaymentMethod
, 'INSURANCE' As productcode
, '4.95' As ProductPrice
, '1' As quantity
, (SELECT shippingmethodname FROM shippingmethods WHERE shippingmethods.shippingmethodid = o.shippingmethodid) as shiptype

FROM Orders o WITH (NOLOCK)
LEFT JOIN ( SELECT OrderID FROM OrderDetails WITH (NOLOCK) GROUP BY OrderID) OrderDetails
ON o.OrderID = OrderDetails.OrderID
WHERE 1=1
AND o.OrderStatus <> 'Cancelled'
AND o.Total_Payment_Received > 0
AND (SELECT shippingmethodname FROM shippingmethods WHERE shippingmethods.shippingmethodid = o.shippingmethodid) LIKE '%INSUR%'
AND o.OrderID > 22647

UNION ALL

SELECT DISTINCT
o.OrderID
, o.OrderDate
, o.PaymentAmount
, o.CustomerID
, o.SalesTax1
, (SELECT PaymentMethod FROM PaymentMethods WITH (NOLOCK) WHERE PaymentMethods.PaymentMethodID = o.PaymentMethodID) As PaymentMethod
, 'FedEx' As productcode
, totalshippingcost As ProductPrice
, '1' As quantity
, (SELECT shippingmethodname FROM shippingmethods WHERE shippingmethods.shippingmethodid = o.shippingmethodid) as shiptype

FROM Orders o WITH (NOLOCK)
LEFT JOIN ( SELECT OrderID FROM OrderDetails WITH (NOLOCK) GROUP BY OrderID) OrderDetails
ON o.OrderID = OrderDetails.OrderID
WHERE 1=1
AND o.OrderStatus <> 'Cancelled'
AND o.Total_Payment_Received > 0
AND o.totalshippingcost > 0
AND (SELECT shippingmethodname FROM shippingmethods WHERE shippingmethods.shippingmethodid = o.shippingmethodid) NOT LIKE '%INSUR%'
AND o.OrderID > 22647

UNION ALL

SELECT DISTINCT
o.OrderID
, o.OrderDate
, o.PaymentAmount
, o.CustomerID
, o.SalesTax1
, (SELECT PaymentMethod FROM PaymentMethods WITH (NOLOCK) WHERE PaymentMethods.PaymentMethodID = o.PaymentMethodID) As PaymentMethod
, 'FedEx' As productcode
, totalshippingcost-4.95 As ProductPrice
, '1' As quantity
, (SELECT shippingmethodname FROM shippingmethods WHERE shippingmethods.shippingmethodid = o.shippingmethodid) as shiptype

FROM Orders o WITH (NOLOCK)
LEFT JOIN ( SELECT OrderID FROM OrderDetails WITH (NOLOCK) GROUP BY OrderID) OrderDetails
ON o.OrderID = OrderDetails.OrderID
WHERE 1=1
AND o.OrderStatus <> 'Cancelled'
AND o.Total_Payment_Received > 0
AND o.totalshippingcost > 0
AND (SELECT shippingmethodname FROM shippingmethods WHERE shippingmethods.shippingmethodid = o.shippingmethodid) LIKE '%INSUR%'
AND (SELECT shippingmethodname FROM shippingmethods WHERE shippingmethods.shippingmethodid = o.shippingmethodid) LIKE '%FedEx%'
AND o.OrderID > 22647

ORDER BY o.orderid

________

Again, the data is EXACTLY how I need it, but I'm wondering if there's a way to shorten this code: more specifically so that I only have to enter a new o.OrderID ONE TIME rather than once per UNION.

I look forward to any ideas or suggestions, thanks!

Kate the Great

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-30 : 17:48:39

>> Again, the data is EXACTLY how I need it

How can you POSSIBLY tell

And get rid of NOLOCK..who told to use that and why?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-30 : 17:54:51

something like this?

I didnt understand last select though
you're checking same field for two conditions

AND (SELECT shippingmethodname FROM shippingmethods WHERE shippingmethods.shippingmethodid = o.shippingmethodid) LIKE '%INSUR%'
AND (SELECT shippingmethodname FROM shippingmethods WHERE shippingmethods.shippingmethodid = o.shippingmethodid) LIKE '%FedEx%'

is this correct?



SELECT 
o.OrderID
, o.OrderDate
, o.PaymentAmount
, o.CustomerID
, o.SalesTax1
, PaymentMethods.PaymentMethod
, COALESCE(orderdetails.productcode,CASE WHEN shippingmethodname LIKE '%INSUR%' THEN 'INSURANCE' ELSE  'FedEx' END) AS productcode
, COALESCE(orderdetails.ProductPrice,CASE WHEN shippingmethodname LIKE '%INSUR%' THEN '4.95' ELSE totalshippingcost  END) AS ProductPrice
, COALESCE(orderdetails.quantity,1) AS quantity
, shippingmethods.shippingmethodname as shiptype

FROM Orders o WITH (NOLOCK)
INNER JOIN PaymentMethods WITH (NOLOCK) ON PaymentMethods.PaymentMethodID = o.PaymentMethodID
LEFT JOIN ( SELECT OrderID, productcode, productprice, quantity FROM OrderDetails WITH (NOLOCK) GROUP BY OrderID, productcode, productprice, quantity) OrderDetails 
ON o.OrderID = OrderDetails.OrderID 
LEFT JOIN shippingmethods ON shippingmethods.shippingmethodid = o.shippingmethodid
WHERE 1=1 
AND o.OrderStatus <> 'Cancelled' 
AND o.Total_Payment_Received > 0
AND o.OrderID > 22647

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-30 : 19:47:26

UNION City??? New Jersey??? Some GREAT Cuban Food down there

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-31 : 15:13:09

make sure you read this if you're using NOLOCK

http://visakhm.blogspot.com/2010/02/avoiding-deadlocks-using-new.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

katethegreat
Starting Member

13 Posts

Posted - 2012-03-31 : 16:56:48

quote:
Originally posted by visakh16

something like this?

I didnt understand last select though
you're checking same field for two conditions

AND (SELECT shippingmethodname FROM shippingmethods WHERE shippingmethods.shippingmethodid = o.shippingmethodid) LIKE '%INSUR%'
AND (SELECT shippingmethodname FROM shippingmethods WHERE shippingmethods.shippingmethodid = o.shippingmethodid) LIKE '%FedEx%'

is this correct?

Hi visakh16 - yeah, I did mean to check the same field for 2 conditions - i just didnt know how to do it properly. What I'm trying to accomplish is this: my shippingmethod name tells me if the customer bought insurance or not, and/or whether or not they paid for fedex. the shippingmethod names are like "INSURED FedDex 2nd Day" or "FedEx 3 Day", so I need a new row for "FedEx" with ONLY the amount the customer paid in fedex, and a new row for "Insurance" with the amount "4.95", so to accomplish that, if the shippingmethodname contains "fedex" and not "insur" then I know that the customer paid for fedex, but not insurance, so there needs to be a row for the "fedex" amount. Then if the the shippingmethodname contains "fedex" AND "insur" then there needs to be a row for Insurance with productprice "4.95" (that is what we charge for insurance) and a row for "fedex" in productcode and productprice is totalshippingcost - 4.95 (the fedex amount only)

I tried your code with COALESCE and the "insurance" and "fedex" rows are not in the exported data as they are with my original code. Do you know of a way to make it so the exported data still contains the "fedex" and "insurance" rows without having to update the same conditions multiple times when I want to export new orderids?

Thanks!

Kate the Great

katethegreat
Starting Member

13 Posts

Posted - 2012-04-07 : 12:35:59

Just thought I'd let you all know that I was able to figure it out. Here's the final code:

SELECT
o.OrderID
, o.OrderDate
, o.PaymentAmount
, o.BillingFirstName + ' ' + o.BillingLastName + ' ' + CONVERT(varchar(200), o.customerid) As Customer
, (SELECT PaymentMethod FROM PaymentMethods WHERE PaymentMethods.PaymentMethodID = o.PaymentMethodID) As PaymentMethod
, (SELECT CASE WHEN orderdetails.productprice < 0 THEN 'DISCOUNT' ELSE orderdetails.productcode END) AS productcode
, orderdetails.ProductPrice
, orderdetails.quantity
, o.SalesTax1
, shippingmethods.shippingmethodname as shiptype
, Coalesce (
(SELECT CASE WHEN
shippingmethods.shippingmethodname LIKE '%FedEx%'
AND o.shippingmethodid NOT LIKE '10%'
THEN o.totalshippingcost ELSE NULL END)

, (SELECT CASE WHEN
shippingmethods.shippingmethodname LIKE '%FedEx%'
AND o.shippingmethodid LIKE '10%'
THEN o.totalshippingcost - 4.95 ELSE NULL END)
) AS FedEx

, (SELECT CASE WHEN o.shippingmethodid LIKE '10%' THEN '4.95' ELSE '0' END) AS Insurance

FROM Orders o
INNER JOIN PaymentMethods ON PaymentMethods.PaymentMethodID = o.PaymentMethodID
LEFT JOIN ( SELECT OrderID, productcode, productprice, Sum(quantity)as quantity FROM OrderDetails GROUP BY OrderID, productcode, productprice, quantity) OrderDetails
ON o.OrderID = OrderDetails.OrderID
LEFT JOIN shippingmethods ON shippingmethods.shippingmethodid = o.shippingmethodid
WHERE 1=1
AND o.OrderStatus <> 'Cancelled'
AND o.Total_Payment_Received > 0

---Enter the oldest order number you want to download

AND o.OrderID > 22937

Kate the Great

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2012-04-07 : 17:45:59

It's a little non-standard to use this pattern:

, (SELECT CASE WHEN orderdetails.productprice < 0 THEN 'DISCOUNT' ELSE orderdetails.productcode END) AS productcode

You can remove the select and just do this:

, CASE WHEN orderdetails.productprice < 0 THEN 'DISCOUNT' ELSE orderdetails.productcode END AS productcode

katethegreat
Starting Member

13 Posts

Posted - 2012-04-08 : 09:54:39

quote:
Originally posted by jezemine

It's a little non-standard to use this pattern:

, (SELECT CASE WHEN orderdetails.productprice < 0 THEN 'DISCOUNT' ELSE orderdetails.productcode END) AS productcode

You can remove the select and just do this:

, CASE WHEN orderdetails.productprice < 0 THEN 'DISCOUNT' ELSE orderdetails.productcode END AS productcode

elsasoft.org

Thank you for the correction, I wasn't sure if I had to do a SELECT on that line or not, and I was so excited that the code worked I wasn't going to mess with it; but (as I'm sure you know) it works just fine with CASE rather than the SELECT. Much appreciated!

Kate the Great

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources