CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Antonio Jose
Antonio Jose

Reputation: 19

How to subtract two row's date within same column using a SQL query showing days?

I have two dates in the same column and I need subtract the days/hours between them. SELECT [ID],[DATA] FROM [DADOS]

I want to show like:

ID Date DiffDate 1 2017-05-12 0 2 2017-05-14 2 3 2017-05-28 14

Upvotes: 1

Views: 773

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269533

SQL Server 2008 doesn't support LAG(), which is what you really need. I would recommend using OUTER APPLY:

SELECT d.*,
       COALESCE(DATEDIFF(day, dprev.DATA, d.DATA), 0)
FROM DADOS d OUTER APPLY
     (SELECT TOP (1) d2.*
      FROM DADOS d2
      WHERE d2.DATA < d.DATA
      ORDER BY DATA DESC
     ) dprev;

Upvotes: 0

DhruvJoshi
DhruvJoshi

Reputation: 17126

For older version of SQL server you should use a query like below

 SELECT 
    d1.[ID],
    d1.[DATA], 
    DiffDate=DATEDIFF(d,ISNULL(d2.[Data],d1.[Data]),d1.[Data]) 
    FROM [DADOS] d1
     LEFT JOIN [DADOS] d2
     ON d1.[id]=d2.[id]+1

You can LEAD/LAG functions if your SQL server version supports them like below

SELECT 
[ID],
[DATA], 
DiffDate=DATEDIFF(d,ISNULL(LAG([Data]) over( order by [Id]),[Data]),[Data]) 
FROM [DADOS]

Note that ISNULL is to take care of first row's 0 value

Upvotes: 0

Thom A
Thom A

Reputation: 95554

As you're using SQL Server 2008, you'll need to use a LEFT JOIN onto the same table:

WITH VTE AS (
    SELECT ID,
           CONVERT(date, [date]) AS [date] --That's not confusing
    FROM (VALUES(1,'20170512'),
                (2,'20170514'),
                (3,'20170528')) V(ID, [date]))
SELECT V1.ID,
       V1.[date],
       ISNULL(DATEDIFF(DAY,V2.[date],V1.[date]),0) AS DiffDate
FROM VTE V1
     LEFT JOIN VTE V2 ON V1.ID -1 = V2.ID;

If, however, you have SQL Server 2012+, then you can achieve this much more easily using LAG, meaning you don't need to scan the table twice:

WITH VTE AS (
    SELECT ID,
           CONVERT(date, [date]) AS [date] --That's not confusing
    FROM (VALUES(1,'20170512'),
                (2,'20170514'),
                (3,'20170528')) V(ID, [date]))
SELECT V.ID,
       V.[date],
       ISNULL(DATEDIFF(DAY,LAG(V.[date]) OVER (ORDER BY V.ID),V.[date]),0) AS DiffDate
FROM VTE V;

Upvotes: 2

Related Questions