Bring back additions

Question

Bring back additions

ByronOne

SSCarpal Tunnel

Points: 4656
More actions
March 10, 2016 at 6:14 am

#308928

Hi everyone
I have a Sales table called dbo.AllSales which contains amongst other fields the following: PURCHASE_DATE, ITEM, STORE, REGION, PRICE, PURCHASER_ID and SALES_STAFF.
I want to be able to bring back all records from this table where the additional PURCHASE_DATE(s) exceed the original PURCHASE_DATE by more than 6 months. So If I have a the following:
PURCHASE_DATE - ITEM - STORE - REGION - PRICE - PURCHASER_ID - SALES_STAFF
01/30/2015 - TRC123 – North - $800 – 123456 - 0089
09/30/2015 - TRC126 – North - $500 – 123456 - 0063
10/30/2015 - TRC124 – North - $300 – 123456 – 0047
From the example above I want to bring back the bottom 2 records and not the top one ie the 2 purchases that occurred more than 6 months after the initial purchase. All single purchases would be excluded from the results as the logic would not apply to those – only the additional purchases.
Hope this makes sense and someone can help.
BO

Viewing 7 posts - 1 through 6 (of 6 total)

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

djj SSCoach Points: 18831 More actions · Answer 1

ByronOne (3/10/2016)
Hi everyone
I have a Sales table called dbo.AllSales which contains amongst other fields the following: PURCHASE_DATE, ITEM, STORE, REGION, PRICE, PURCHASER_ID and SALES_STAFF.
I want to be able to bring back all records from this table where the additional PURCHASE_DATE(s) exceed the original PURCHASE_DATE by more than 6 months. So If I have a the following:
PURCHASE_DATE - ITEM - STORE - REGION - PRICE - PURCHASER_ID - SALES_STAFF
01/30/2015 - TRC123 – North - $800 – 123456 - 0089
09/30/2015 - TRC126 – North - $500 – 123456 - 0063
10/30/2015 - TRC124 – North - $300 – 123456 – 0047
From the example above I want to bring back the bottom 2 records and not the top one ie the 2 purchases that occurred more than 6 months after the initial purchase. All single purchases would be excluded from the results as the logic would not apply to those – only the additional purchases.
Hope this makes sense and someone can help.
BO

First find the original purchase then return anything that has the date you want. One way to find the original would be use ROW_NUMBER in a cte.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Yes, and you might also try the LEAD and LAG functions if you have access to SQL Server 2012 or higher.

John

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 3

Thanks for the pointers. Using 2008 so will try using the row number function in a cte.

BO

sgmunson SSC Guru Points: 110640 More actions · Answer 4

ByronOne (3/10/2016)
Thanks for the pointers. Using 2008 so will try using the row number function in a cte.
BO

I'm not sure that ROW_NUMBER() is necessary. I looked at your supplied data, and it appears there's a column missing in the actual data. I assumed it was the STORE field's data that was missing, and put the following together:

WITH AllSales (PURCHASE_DATE, ITEM, STORE, REGION, PRICE, PURCHASER_ID, SALES_STAFF) AS (

SELECT '01/30/2015', 'TRC123', 4, 'North', 800.00, 123456, '0089' UNION ALL

SELECT '09/30/2015', 'TRC126', 4, 'North', 500.00, 123456, '0063' UNION ALL

SELECT '10/30/2015', 'TRC124', 4, 'North', 300.00, 123456, '0047'

),

FIRST_PURCHASE AS (

SELECT MIN(A.PURCHASE_DATE) AS ORIG_PURCH_DATE, A.PURCHASER_ID

FROM AllSales AS A

GROUP BY PURCHASER_ID

HAVING COUNT(A.ITEM) > 1

)

SELECT A.*

FROM FIRST_PURCHASE AS FP

INNER JOIN AllSales AS A

ON FP.PURCHASER_ID = A.PURCHASER_ID

WHERE A.PURCHASE_DATE > DATEADD(mm, 6, FP.ORIG_PURCH_DATE)

ORDER BY A.PURCHASER_ID, A.ITEM, A.PURCHASE_DATE;

This code produces the two rows needed from the data supplied. Let me know if that works or if I have an assumption incorrect.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 5

This is perfect - just what I was looking for!

Thanks for taking time out to write the code - much appreciated.

sgmunson SSC Guru Points: 110640 More actions · Answer 6

You're welcome. Enjoy... Some thoughts on potentially improving its performance:

1.) Perhaps generate the additional 6 months in the 2nd CTE instead of in the WHERE clause of the final query...

2.) Potentially optimize the indexing, based on your real-world details.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)