holemania
asked on
SQL Query - Where clause with Case
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.
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?
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())
)
ENDSo 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?
ASKER
I am on SQL Server 2008.
ASKER
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.
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
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
Can you provide some sample data and the expected results so we have something with which to test?
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
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
ASKER
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:
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_IDASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you. That worked.
However, which version of SQL Server do you have? If 2012/14, there's one approach, if not, there's another.