CODEWITHSUNDEEP
sqlsql-server
Alexander Hristov
Alexander Hristov

Reputation: 311

How to get the difference in dates in SQL Server

I'm having trouble with writing a query to get difference between the UpdateDate and the CreationDate of 2 records if the ID is the lowets and the difference between the most recent and second most recent UpdateDate. Here's my Query:

SELECT 
    a.ID, a.RequestID, b.KrStatus, b.CrDate , b.UpdateDate, 
    DATEDIFF (HOUR, b.CrDate, b.UpdateDate) AS TimeDifference,
CASE WHEN a.ID = (SELECT MAX(a.ID) FROM [dbo].[Krdocs_hist] a WHERE a.RequestID = 1)
    THEN 'YES'
    ELSE 'NO'
END AS isMax,
CASE WHEN a.ID = (SELECT MIN(a.ID) FROM [dbo].[Krdocs_hist] a WHERE a.RequestID = 1)
    THEN 'YES'
    ELSE 'NO'
END AS isMi
FROM [dbo].[Krdocs_hist] a, [dbo].Krdocs_Details_hist b
WHERE 
    a.RequestId = b.RequestId
    and a.ID = b.ID 
    and a.RequestId = 1
ORDER BY b.RequestID

Here's my current result: enter image description here

What I'd like to do is get the last possible record, check to see if there was an existing one before it. If there wasn't compare the UpdateDate and CrDate (UpdateDate minus CrDate. If there was a record before this I want to do the UpdateDate minus the previous UpdateDate.

Using this query:

SELECT b.Id, b.RequestId, b.UpdateDate, b.KrStatus
FROM [dbo].[Krdocs_Details_hist] b
WHERE b.RequestId = 1

Has this result: enter image description here

And using this query:

SELECT a.*
FROM [dbo].[Krdocs_hist] a
WHERE RequestId = 1

Has this result: enter image description here

Upvotes: 0

Views: 59

Answers (1)

Thang Pham
Thang Pham

Reputation: 621

UPDATE Since LAG is available from SQL 2012, you can use like below:

SELECT
        ID, 
        RequestID, 
        CrDate, 
        UpdateDate, 
        KrStatus, 
        DATEDIFF(HOUR, PreviousUpdateDate, UpdateDate) as TimeDifference
FROM 
    (SELECT 
        ID, 
        RequestID, 
        CrDate, 
        UpdateDate, 
        KrStatus, 
        LAG(UpdateDate, 1, CrDate) OVER (ORDER BY YEAR(ID)) AS PreviousUpdateDate 
    FROM [dbo].Krdocs_Details_hist) as tmp

I think you can try like this:

 SELECT 
    CASE 
        WHEN COUNT(*) <= 1 THEN  DATEDIFF(HOUR, 
            (SELECT CrDate FROM [dbo].Krdocs_Details_hist),
            (SELECT UpdateDate FROM [dbo].Krdocs_Details_hist))
        WHEN COUNT(*) > 1 THEN DATEDIFF(HOUR, 
            (SELECT MAX(UpdateDate) FROM [dbo].Krdocs_Details_hist WHERE UpdateDate < ( SELECT MAX(UpdateDate) FROM [dbo].Krdocs_Details_hist)),
            (SELECT MAX(UpdateDate) FROM [dbo].Krdocs_Details_hist))
    END AS TimeDifference
 FROM [dbo].Krdocs_Details_hist

Upvotes: 1

Related Questions