So I currently have a table where I have a date in a single column and a corresponding bucket that are linked to an ID:

I am in need of transforming the table so that I am able to determine or calculate the datediff between two buckets/dates for each unique ID, ideally transforming the table to look like this:

Where for each bucket/date change, I am able to have in one column the date associated with the bucket before it was changed and in the other the the date that it was changed and the corresponding bucket it was changed to.

CodePudding user response：

A bit of a rudimental solution. You could try and join the table on itself, be careful as this can create a cartesian product if not done correctly.

Select * 
From Table as A
Join Table as B on A.ID = B.ID
Where A.BucketID <> B.BucketID

CodePudding user response：

Seems like you could use a use LEAD within a CTE here, and then filter out the rows that have NULL in the derived column Date2 (or Bucket2):

WITH CTE AS(
        SELECT ID,
               Bucket1,
               Date1,
               LEAD(Bucket1) OVER (PARTITION BY ID ORDER BY Date1) AS Bucket2,
               LEAD(Date1) OVER (PARTITION BY ID ORDER BY Date1) AS Date2
        FROM dbo.YourTable)
SELECT ID,
       Bucket1,
       Date1,
       Bucket2,
       Date2
FROM CTE
WHERE Date2 IS NOT NULL;