CODEWITHSUNDEEP
sqlsql-server
Hanu
Hanu

Reputation: 55

Difference of dates between two dates starting everytime at first row date

I have table like this

I got difference from consecutive dates but I want to calculate difference of dates starting every time at 1. like (1-2 and 1-3 again 1-4) 

ChildID   ID      Date
1         4    2005-12-07 00:00:00.000
1         3    2005-11-07 00:00:00.000
1         2    2005-10-09 00:00:00.000
1         1    2005-04-09 00:00:00.000

Expected 

ChildID   ID      Date                         Difference
1         4    2005-12-07 00:00:00.000            243 days (1-4)
1         3    2005-11-07 00:00:00.000            207 days (1-3)
1         2    2005-10-09 00:00:00.000            183 days (1-2)
1         1    2005-04-09 00:00:00.000            NULL

Upvotes: 1

Views: 78

Answers (4)

Bogdan
Bogdan

Reputation: 16

1) SQL2012:

SELECT DATEDIFF(DAY, FIRST_VALUE(DATECOL) OVER(ORDER BY ID),  DATECOL) AS DIFF ...
FROM ...

2) If ID column has unique values:

SELECT DATEDIFF(DAY, (SELECT A.DATECOL FROM TABLE1 A WHERE A.ID=1), B.DATECOL) AS DIFF ...
FROM TABLE1 B ...

Upvotes: 0

Edward N
Edward N

Reputation: 997

You can do it by simple query

SELECT t1.*, datediff(day, t2.date, t1.date) AS DateDifference
FROM dbo.your_table AS t1
INNER JOIN dbo.your_table AS t2 ON t1.childId = t2.id

Update: Add full example

declare @temp table(childId int, id int, date datetime)

insert into @temp values(1,4,'2005-12-07 00:00:00.000')
insert into @temp values(1,3,'2005-11-07 00:00:00.000')
insert into @temp values(1,2,' 2005-10-09 00:00:00.000')
insert into @temp values(1,1,'2005-04-09 00:00:00.000')


SELECT t1.*, datediff(day, t2.date, t1.date) AS DateDifference
FROM @temp AS t1
INNER JOIN @temp AS t2 ON t1.childId = t2.id

Result

1   4   2005-12-07 00:00:00.000 242
1   3   2005-11-07 00:00:00.000 212
1   2   2005-10-09 00:00:00.000 183
1   1   2005-04-09 00:00:00.000 0

Upvotes: 1

Mazhar
Mazhar

Reputation: 3837

with additional sample data included

DECLARE @tab TABLE (ChildId INT, Id INT, Date DATETIME)

INSERT INTO @tab
(ChildId, Id, Date)
SELECT 1,4,'2005-12-07 00:00:00.000'
UNION
SELECT 1,3,'2005-11-07 00:00:00.000'
UNION
SELECT 1,2,'2005-10-09 00:00:00.000'
UNION
SELECT 1,1,'2005-04-09 00:00:00.000'
UNION
SELECT 2,1,'2005-12-09 00:00:00.000'
UNION
SELECT 2,2,'2005-12-15 00:00:00.000'
UNION
SELECT 2,3,'2005-12-31 00:00:00.000'

;WITH cteX
AS(
    SELECT
          T.ChildId
        , T.Id
        , T.Date
        , Diff = DATEDIFF(DAY, MIN(T.Date) OVER ( PARTITION BY T.ChildId), T.Date)
    FROM @tab T
)
SELECT 
      X.ChildId
    , X.Id
    , X.Date
    , Diff = CASE WHEN X.Diff = 0 THEN NULL ELSE X.Diff END
FROM cteX X
ORDER BY
    X.ChildId , id DESC

output is

table

if you want the final column to look like this

242 days (1-4)

then modify Diff like so

Diff = CAST(CASE WHEN X.Diff = 0 THEN NULL ELSE X.Diff END AS VARCHAR(10)) + ' days' + ' (' + CAST(1 AS VARCHAR(2)) + '-' + CAST(X.Id AS VARCHAR(2)) + ')'

Upvotes: 0

JNevill
JNevill

Reputation: 50034

Using a Window Function to get the earliest date for this record's child_id:

 SELECT datediff(day, min(date_field) OVER (PARTITION BY child_id) - date_field), child_id, id FROM your_table

This doesn't take into account that the min(date) for the child_id might not be the record with id=1. So if that is a concern, then a subquery solution:

SELECT datediff(day, t2.datefield, t1.date_field), child_id, id
FROM your_table t1
    JOIN (SELECT child_id, date_field FROM your_table where id = 1) t2
         ON t1.child_id = t2.child_id;

Upvotes: 0

Related Questions