CODEWITHSUNDEEP
t-sqlrowsubtraction
Woody_1983
Woody_1983

Reputation: 159

Subtract the previous row of data where the id is the same as the row above

I have been trying all afternoon to try and achieve this with no success.

I have a db in with info on customers and the date that they purchase products from the store. It is grouped by a batch ID which I have converted into a date format.

So in my table I now have:

CustomerID|Date
1234      |2011-10-18
1234      |2011-10-22
1235      |2011-11-16
1235      |2011-11-17

What I want to achieve is to see the number of days between the most recent purchase and the last purchase and so on.

For example:

CustomerID|Date       |Outcome
1234      |2011-10-18 |
1234      |2011-10-22 | 4
1235      |2011-11-16 |
1235      |2011-11-17 | 1

I have tried joining the table to itself but the problem I have is that I end up joining in the same format. I then tried with my join statement to return where it did <> match date.

Hope this makes sense, any help appreciated. I have searched all the relevant topics on here.

Upvotes: 0

Views: 870

Answers (1)

SQLMason
SQLMason

Reputation: 3275

Will there be multiple groups of CustomerID? Or only and always grouped together?

DECLARE @myTable TABLE
(
    CustomerID INT,
    Date DATETIME
)

INSERT INTO @myTable
SELECT 1234, '2011-10-14' UNION ALL
SELECT 1234, '2011-10-18' UNION ALL
SELECT 1234, '2011-10-22' UNION ALL
SELECT 1234, '2011-10-26' UNION ALL
SELECT 1235, '2011-11-16' UNION ALL
SELECT 1235, '2011-11-17' UNION ALL 
SELECT 1235, '2011-11-18' UNION ALL
SELECT 1235, '2011-11-19'

SELECT  CustomerID, 
        MIN(date), 
        MAX(date), 
        DATEDIFF(day,MIN(date),MAX(date)) Outcome
FROM @myTable 
GROUP BY CustomerID

SELECT  a.CustomerID, 
        a.[Date], 
        ISNULL(DATEDIFF(DAY, b.[Date], a.[Date]),0) Outcome
FROM 
(
    SELECT  ROW_NUMBER() OVER(PARTITION BY [CustomerID] ORDER BY date) Row,
            CustomerID,
            Date
    FROM @myTable 
) A
LEFT JOIN 
(
    SELECT  ROW_NUMBER() OVER(PARTITION BY [CustomerID] ORDER BY date) Row,
            CustomerID,
            Date
    FROM @myTable 
) B ON a.CustomerID = b.CustomerID AND A.Row = B.Row + 1

Upvotes: 2

Related Questions