Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Query - Where clause with Case

Posted on 2015-01-15
9
Medium Priority
?
85 Views
Last Modified: 2015-01-20
I have a table called Purchase_line.  The query is simple, but it's taking forever whenever I add my where clause with the case statement.  Is there an easier way to do this?  Takes about 20 minutes to run once I add my where clause with the case statement.

SELECT	P1.ITEM_ID, P1.UNIT_PRICE, P1.RECEIVE_DATE, P1.YR
FROM	PURCHASE_LINE P1
WHERE	P1.RECEIVE_DATE = CASE WHEN (
									SELECT MIN(P2.YR)
									FROM	PURCHASE_LINE P2
									WHERE	P2.ITEM_ID = P1.ITEM_ID
									) < DATEPART(YEAR, GETDATE())
							THEN 
									(
									SELECT	MAX(P2.RECEIVE_DATE)
									FROM	PURCHASE_LINE P2
									WHERE	P2.ITEM_ID = P1.ITEM_ID
											AND P2.YR < DATEPART(YEAR, GETDATE())
									)		
							ELSE
									(
									SELECT	MAX(P2.RECEIVE_DATE)
									FROM	PURCHASE_LINE P2
									WHERE	P2.ITEM_ID = P1.ITEM_ID
											AND P2.YR = DATEPART(YEAR, GETDATE())
									)
							END

Open in new window


So with the above query, the case statement is that if the year when the Purchase Order line was generated prior to current year, then only pull all PO lines generated prior to current year.  However, if there's no PO generated from prior year, then pull from current year.  It is working, but takes so long.  Anything I can do to improve performance?
0
Comment
Question by:holemania
[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
9 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40552203
theres's quite a lot you can do.

However, which version of SQL Server do you have? If 2012/14, there's one approach, if not, there's another.
0
 

Author Comment

by:holemania
ID: 40552375
I am on SQL Server 2008.
0
 

Author Comment

by:holemania
ID: 40552378
Sorry it's a mix of 2005 and 2008.  We have different sql instances and mix version of 2005 and 2008, but the database is the same.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40552416
SELECT  ITEM_ID, UNIT_PRICE, RECEIVE_DATE, YR
FROM (
    SELECT      P1.ITEM_ID, P1.UNIT_PRICE, P1.RECEIVE_DATE, P1.YR,
            ROW_NUMBER() OVER(PARTITION BY P1.ITEM_ID ORDER BY P2.RECEIVE_DATE DESC) AS row_num
    FROM      PURCHASE_LINE P1
) AS derived
WHERE
    row_num = 1
--ORDER BY ITEM_ID
0
 
LVL 32

Expert Comment

by:awking00
ID: 40553470
Can you provide some sample data and the expected results so we have something with which to test?
0
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40554997
Hi,

You can have case in the where clause

for Example

Select * from table where case when col1 = 'value1' then case when col2 = 'condition' then 1 else 0 end else 1 end =1
0
 

Author Comment

by:holemania
ID: 40558465
Scott,

I tried your example, and that'll not work for what I need.  Here's the criteria or to help clarify.  

I am looking for the last purchase price from the previous year.  If there's no last purchase price from previous year, then use current year or any other year.

Example from my query sample below,  I purchase item "BBB9332" from 2012, 2013, 2014, and 2015.  My last purchase from previous year was 2014.  So I need that on my report and disregard all the other.

However, item AAC9934 was only purchased once in 2013.  Since there's no purchase from 2014, it uses the last time it was purchased.  Even if there's purchase in 2015, it's going to use 2013.

Now I have BBA0223 which was never purchased until 2015.  So it uses that if there's no prior year purchase.  

My query is giving me the result I am looking for, but it's taking forever since the number of records it is processing is over 30K, and need to see if there's a way to improve the performance.  If I don't use the case in my where clause, it takes 10 seconds to run.  However, with the case statement to do the filtering, it takes a good hour to run.

Providing some sample data:
DECLARE @SPTEMP TABLE(
		ITEM_ID VARCHAR(30),
		UNIT_PRICE DECIMAL(15,4),
		RECEIVE_DATE DATETIME,
		YR INT
		);

INSERT INTO @SPTEMP
VALUES('AAA1234', '5', '1/1/2014', '2014'),
('AAA1234', '5.50', '5/8/2014', '2014'),
('AAA1234', '6.00', '1/5/2015', '2015'),
('AAC9934', '10', '6/28/2013', '2013'),
('AAC0221', '100', '8/24/2014', '2014'),
('AAC0221', '110', '9/2/2014', '2014'),
('BBA0223', '150', '1/12/2015', '2015'),
('BBB9332', '90', '1/28/2012', '2012'),
('BBB9332', '91', '1/15/2013', '2013'),
('BBB9332', '89', '1/27/2014', '2014'),
('BBB9332', '93', '1/15/2015', '2015')


SELECT	P1.ITEM_ID, P1.UNIT_PRICE, P1.RECEIVE_DATE, P1.YR
FROM	@SPTEMP P1
WHERE	P1.RECEIVE_DATE = CASE WHEN (
									SELECT MIN(P2.YR)
									FROM	@SPTEMP P2
									WHERE	P2.ITEM_ID = P1.ITEM_ID
									) < DATEPART(YEAR, GETDATE())
							THEN 
									(
									SELECT	MAX(P2.RECEIVE_DATE)
									FROM	@SPTEMP P2
									WHERE	P2.ITEM_ID = P1.ITEM_ID
											AND P2.YR < DATEPART(YEAR, GETDATE())
									)		
							ELSE
									(
									SELECT	MAX(P2.RECEIVE_DATE)
									FROM	@SPTEMP P2
									WHERE	P2.ITEM_ID = P1.ITEM_ID
											AND P2.YR = DATEPART(YEAR, GETDATE())
									)
							END
ORDER BY P1.ITEM_ID

Open in new window

0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 40558527
Sorry, you're right, I didn't fully understand, I need to add to the ORDER BY to handle prior year(s) correctly:



SELECT  ITEM_ID, UNIT_PRICE, RECEIVE_DATE, YR
 FROM (
     SELECT      P1.ITEM_ID, P1.UNIT_PRICE, P1.RECEIVE_DATE, P1.YR,
             ROW_NUMBER() OVER(PARTITION BY P1.ITEM_ID
                          ORDER BY CASE WHEN P2.YR < DATEPART(YEAR, GETDATE()) THEN 1 ELSE 2 END, P2.RECEIVE_DATE DESC) AS row_num
     FROM      PURCHASE_LINE P1
 ) AS derived
 WHERE
     row_num = 1
 --ORDER BY ITEM_ID
0
 

Author Closing Comment

by:holemania
ID: 40560781
Thank you.  That worked.
0

Featured Post

Google Certified Professional - Cloud Architect

This course (1 of 3) is designed to help students who are interested in Google Cloud Platform (GCP) to become familiar with the platform, navigate the console and learn its capabilities. It will also prepare students for the Google Cloud Architect certification exam.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

688 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