SQL Server How can I obtain a set of results such that each row's date is x numer of days after the previous one?

cld4siwp 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(90)

Lets say I have a table of purchases that looks like this:

Purchases
---------
Item_ID
Purchase_Date
Customer_ID

How would I be able to obtain the set of purchases, starting from the first purchase, for each customer that are at least X days after the last purcahse? For example, if we have the data below, and x=10:

Item_ID    PurchaseDate    Customer_ID    
123        07/29/23        1000
123        08/04/23        1000
123        08/16/23        1000
563        07/03/23        7785
563        07/05/23        7785
788        08/17/23        2489

The query should return:

Item_ID    PurchaseDate    Customer_ID    
123        07/29/23        1000
123        08/11/23        1000
563        07/03/23        7785
788        08/17/23        2489

So, for customer 1000, the first purchase counts, because its a new purchase. The second purchase is less than 10 days from the last purchase so it's discarded. The third purchase for this customer counts because it is more than or equal to 10 days after the last purchase. For customer 7785, only the first counts because the second purcahse is less than 10 days after the last purcahse, and finally for customer 2489, it only has one new purchase so that just counts as the new purchase.(note the first purchase always counts)

I was thinking along the lines of using the lag function

SELECT
    t.Item_ID,
    t.PurchaseDate,
    t.Customer_ID
FROM (
    SELECT
        p.Item_ID,
        p.PurchaseDate,
        p.Customer_ID,        
        LAG OVER(PARTITION BY Customer_ID ORDER BY PurchaseDate) next_purchase
        ROW_NUMBER() OVER(PARTITION BY Customer_ID ORDER BY PurchaseDate) rownum

        
    FROM
        PurchaseTable p
) t
WHERE
    DATEDIFF(DAY, t.PurchaseDate, t.next) >= 10) or rownum=1

EDIT: Apologies, I had the description incorrect. I changed the question and example to the correct details.

sql-server

来源：https://stackoverflow.com/questions/77269252/how-can-i-obtain-a-set-of-results-such-that-each-rows-date-is-x-numer-of-days-a

1条答案

按热度按时间

axr492tv1#

Ignoring the fact that your sample data and results don't align with your description, I guess you'll need something like this.

DECLARE @t TABLE (Item_ID INT, PurchaseDate Date, Customer_ID INT)
INSERT INTO @t VALUES
(123, '2023-07-29', 1000),
(123, '2023-08-04', 1000),
(123, '2023-08-16', 1000),
(563, '2023-07-03', 7785),
(563, '2023-07-05', 7785),
(788, '2023-08-17', 2489)

DECLARE @limit int = 10

SELECT * 
    FROM (
        SELECT 
            * 
            , IsOverLimit = IIF( 
                                DATEDIFF(d, 
                                            LAG(Purchasedate, 1, '1999-01-01') OVER(PARTITION BY Customer_ID ORDER BY Purchasedate),
                                            PurchaseDate) > @limit
                                , 1
                                , 0
                                )
            FROM @t 
        ) r 
    WHERE IsOverLimit = 1

I've expanded the code out so you can see each step from in to out. Use lag to get the previous date, if no date found, set this to some arbitrary date in the past. Then use datediff to get the number of days between the purchase date and the previous date, then compare this result against the @limit variable. Finally select from the subquery where IsOverLimit is onw.

Your sample data does not show any variation within a single customer purchasing different items so if you want to extend this to check for specific item purchases, just add Item_ID to the PARTITION BY clause.

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server How can I obtain a set of results such that each row's date is x numer of days after the previous one?

1条答案

相关问题

热门标签

最新问答