CODEWITHSUNDEEP
sqlsql-server
Ashesh Das
Ashesh Das

Reputation: 365

How to find the time difference between data present in two rows

I have the following table :

Customer_ID PurchaseDatetime
309         2/3/2014  12:29:00
309         2/27/2014 17:11:00
309         4/15/2014 13:24:00

I want to write a query which would calculate the difference between the datetime field of two consecutive rows. Ideally the output should be like

Customer_ID PurchaseDatetime
309         0
309         2/27/2014 17:11:00 - 2/3/2014  12:29:00 // The exact time difference in hours
309         4/15/2014 13:24:00 - 2/27/2014 17:11:00 // The exact time difference in hours

How do I write such a query?

Upvotes: 0

Views: 94

Answers (3)

Chanukya
Chanukya

Reputation: 5873

   SELECT *,
    PURCHASEDATETIME = 
    CASE CUSTOMER_ID 
        WHEN CUSTOMER_ID THEN 
            DATEDIFF(HH, LAG(PURCHASEDATETIME, 1) OVER(ORDER BY CUSTOMER_ID, PURCHASEDATETIME), PURCHASEDATETIME)
        ELSE 
            NULL
    END 
    FROM table

Upvotes: -1

Jatin Patel
Jatin Patel

Reputation: 2104

Try this...

CREATE TABLE #Purchases
(
    CustomerID     INT,
    PurchaseDate   DATETIME
)
INSERT INTO #Purchases
VALUES
(100004,'2016-05-16 08:00:00'),
(100005,'2016-05-16 09:05:00'),
(100006,'2016-05-16 10:08:40'),
(32141 ,'2016-05-16 11:18:00'),
(84230 ,'2016-05-16 12:25:10'),
(23444 ,'2016-05-16 13:40:00'),
(100001,'2016-05-16 14:50:00')

;WITH CTE AS
(
    SELECT
    CustomerID,
    PurchaseDate,
    ROW_NUMBER() OVER (ORDER BY PurchaseDate) AS Seq
    FROM #Purchases
)
SELECT
    p.CustomerID,
    p.PurchaseDate,
    pl.PurchaseDate,
    DATEADD(SECOND,DATEDIFF(SECOND, pl.PurchaseDate,p.PurchaseDate),0) AS DiffDT,
DATEDIFF(HOUR, pl.PurchaseDate,p.PurchaseDate) HourDiff
FROM CTE AS p
LEFT OUTER JOIN CTE AS pl ON pl.Seq = p.Seq - 1 -- Last batch   
ORDER BY p.PurchaseDate

Upvotes: 3

Rajesh Ranjan
Rajesh Ranjan

Reputation: 537

Try This Query...

    ;with cte as
    (select row_number() over(order by (select 100)) Id.Customer_ID,PurchaseDatetime from Table)
    select a.Customer_ID,b.PurchaseDatetime-a.PurchaseDatetime from cte a inner join cte b on a.id=b.id-1

Thanks

Upvotes: 0

Related Questions