Calculating Date Difference

• tinausa

  SSCrazy

  Points: 2697

  More actions

  April 23, 2018 at 3:43 pm

  #330521

  I have eight dates and these are in ascending order. But all or some of them can be null.
  Is there a way to calculate date difference between each of these, ignoring null values. 
  The null dates are 1900-01-01.
  When not null, dateStart is the oldest and date7 is the newest.
  dateStart is not null in all cases. 
  so if date1 is null, the difference between date2 and date1 will be calculated by datediff(day, date0, date2).
  so if date1 and date0 are null, the difference between date2 and date1 will be calculated by datediff(day, dateStart, date2)
  Assuming in both of the above cases  that date2 is not null. 

  I know I can write a big case statement to check for null and calculate that way, but was hoping to find a better shorter way.

  Thanks in advance.

  
create table temp
(
    dateStart datetime
    , date0 datetime
    , date1 datetime
    , date2 datetime
    , date3 datetime
    , date4 datetime
    , date5 datetime
    , date6 datetime
    , date7 datetime

  )

  insert into temp
values('2015-05-07','1900-01-01','1900-01-01','1900-01-01','2015-05-07','2015-05-11','2015-05-12','2015-05-13','2015-05-19')
insert into temp
values('2015-05-07','1900-01-01','1900-01-01','1900-01-01','2015-05-07','2015-05-11','1900-01-01','1900-01-01','1900-01-01')
insert into temp
values('2015-05-13','2015-05-13','2015-05-13','2015-05-13','1900-01-01','1900-01-01','1900-01-01','1900-01-01','1900-01-01')

  select * from temp

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 23, 2018 at 3:53 pm

  #1987949

  tinausa - Monday, April 23, 2018 3:43 PM

  I have eight dates and these are in ascending order. But all or some of them can be null.
  Is there a way to calculate date difference between each of these, ignoring null values. 
  The null dates are 1900-01-01.
  When not null, dateStart is the oldest and date7 is the newest.
  dateStart is not null in all cases. 
  so if date1 is null, the difference between date2 and date1 will be calculated by datediff(day, date0, date2).
  so if date1 and date0 are null, the difference between date2 and date1 will be calculated by datediff(day, dateStart, date2)
  Assuming in both of the above cases  that date2 is not null. 

  I know I can write a big case statement to check for null and calculate that way, but was hoping to find a better shorter way.

  Thanks in advance.

  
create table temp
(
    dateStart datetime
    , date0 datetime
    , date1 datetime
    , date2 datetime
    , date3 datetime
    , date4 datetime
    , date5 datetime
    , date6 datetime
    , date7 datetime

  )

  insert into temp
values('2015-05-07','1900-01-01','1900-01-01','1900-01-01','2015-05-07','2015-05-11','2015-05-12','2015-05-13','2015-05-19')
insert into temp
values('2015-05-07','1900-01-01','1900-01-01','1900-01-01','2015-05-07','2015-05-11','1900-01-01','1900-01-01','1900-01-01')
insert into temp
values('2015-05-13','2015-05-13','2015-05-13','2015-05-13','1900-01-01','1900-01-01','1900-01-01','1900-01-01','1900-01-01')

  select * from temp

  So, based on the sample data provided what is the expected results.

• ScottPletcher

  SSC Guru

  Points: 101098

  More actions

  April 23, 2018 at 4:17 pm

  #1987952

  
;WITH cte_dates AS (
  SELECT t.*, dates2.*
  FROM temp t
  CROSS APPLY (
   SELECT
        MAX(CASE WHEN row_num = 0 THEN date END) AS date0packed,
        MAX(CASE WHEN row_num = 1 THEN date END) AS date1packed,
        MAX(CASE WHEN row_num = 2 THEN date END) AS date2packed,
        MAX(CASE WHEN row_num = 3 THEN date END) AS date3packed,
        MAX(CASE WHEN row_num = 4 THEN date END) AS date4packed,
        MAX(CASE WHEN row_num = 5 THEN date END) AS date5packed,
        MAX(CASE WHEN row_num = 6 THEN date END) AS date6packed,
        MAX(CASE WHEN row_num = 7 THEN date END) AS date7packed,
        MAX(CASE WHEN row_num = 8 THEN date END) AS date8packed
FROM (
        SELECT date, ROW_NUMBER() OVER(ORDER BY date) - 1 AS row_num
        FROM ( VALUES(dateStart),(date0),(date1),(date2),(date3),(date4),(date5),(date6),(date7) ) AS dates(date)
        WHERE date > '19000101'
   ) AS dates1
  ) AS dates2
)
SELECT
    DATEDIFF(DAY, date0packed, date1packed) AS diff1,
    DATEDIFF(DAY, date1packed, date2packed) AS diff2,
    DATEDIFF(DAY, date2packed, date3packed) AS diff3,
    DATEDIFF(DAY, date3packed, date4packed) AS diff4,
    DATEDIFF(DAY, date4packed, date5packed) AS diff5,
    DATEDIFF(DAY, date5packed, date6packed) AS diff6,
    DATEDIFF(DAY, date6packed, date7packed) AS diff7,
    DATEDIFF(DAY, date7packed, date8packed) AS diff8,
    dateStart, date0, date1, date2, date3, date4, date5, date6, date7
FROM cte_dates

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  April 24, 2018 at 6:40 am

  #1988003

  
SELECT
DATEDIFF(day, COALESCE(date0,dateStart),date1),
DATEDIFF(day, COALESCE(date1,date0,dateStart),date2),
DATEDIFF(day, COALESCE(date2,date1,date0,dateStart),date3),
DATEDIFF(day, COALESCE(date3,date2,date1,date0,dateStart),date4),
DATEDIFF(day, COALESCE(date4,date3,date2,date1,date0,dateStart),date5),
DATEDIFF(day, COALESCE(date5,date4,date3,date2,date1,date0,dateStart),date6),
DATEDIFF(day, COALESCE(date6,date5,date4,date3,date2,date1,date0,dateStart),date7)
FROM temp


  Far away is close at hand in the images of elsewhere.
  Anon.

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  April 24, 2018 at 7:33 am

  #1988014

  Shifting gears a bit, I probably wouldn't store the original data in such a horizontal format because you'd need to add columns to the table to add other event points.  It also makes calculations such as that requested more difficult.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• ScottPletcher

  SSC Guru

  Points: 101098

  More actions

  April 24, 2018 at 8:11 am

  #1988023

  David Burrows - Tuesday, April 24, 2018 6:40 AM

  
SELECT
DATEDIFF(day, COALESCE(date0,dateStart),date1),
DATEDIFF(day, COALESCE(date1,date0,dateStart),date2),
DATEDIFF(day, COALESCE(date2,date1,date0,dateStart),date3),
DATEDIFF(day, COALESCE(date3,date2,date1,date0,dateStart),date4),
DATEDIFF(day, COALESCE(date4,date3,date2,date1,date0,dateStart),date5),
DATEDIFF(day, COALESCE(date5,date4,date3,date2,date1,date0,dateStart),date6),
DATEDIFF(day, COALESCE(date6,date5,date4,date3,date2,date1,date0,dateStart),date7)
FROM temp


  Nice, but I think you'll need to a NULLIF(..., '19000101') to make this work based on the original data specs:
  

  The null dates are 1900-01-01.

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• tinausa

  SSCrazy

  Points: 2697

  More actions

  April 24, 2018 at 9:00 am

  #1988045

  Jeff Moden - Tuesday, April 24, 2018 7:33 AM

  Shifting gears a bit, I probably wouldn't store the original data in such a horizontal format because you'd need to add columns to the table to add other event points.  It also makes calculations such as that requested more difficult.

  Thanks Jeff, you are right. But this is the extract that I get from BI with several million rows of data dump. I am doing data cleaning first to get what I need and also calculate the date difference and put it in a different table.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 24, 2018 at 9:12 am

  #1988050

  tinausa - Tuesday, April 24, 2018 9:00 AM

  Jeff Moden - Tuesday, April 24, 2018 7:33 AM

  Shifting gears a bit, I probably wouldn't store the original data in such a horizontal format because you'd need to add columns to the table to add other event points.  It also makes calculations such as that requested more difficult.

  Thanks Jeff, you are right. But this is the extract that I get from BI with several million rows of data dump. I am doing data cleaning first to get what I need and also calculate the date difference and put it in a different table.

  That's nice, but I'd still like to see what you expect based on the sample data you provided.

• tinausa

  SSCrazy

  Points: 2697

  More actions

  April 24, 2018 at 9:18 am

  #1988054

  ScottPletcher - Monday, April 23, 2018 4:17 PM

  
  ;WITH cte_dates AS (
    SELECT t.*, dates2.*
    FROM temp t
    CROSS APPLY (
     SELECT
          MAX(CASE WHEN row_num = 0 THEN date END) AS date0packed,
          MAX(CASE WHEN row_num = 1 THEN date END) AS date1packed,
          MAX(CASE WHEN row_num = 2 THEN date END) AS date2packed,
          MAX(CASE WHEN row_num = 3 THEN date END) AS date3packed,
          MAX(CASE WHEN row_num = 4 THEN date END) AS date4packed,
          MAX(CASE WHEN row_num = 5 THEN date END) AS date5packed,
          MAX(CASE WHEN row_num = 6 THEN date END) AS date6packed,
          MAX(CASE WHEN row_num = 7 THEN date END) AS date7packed,
          MAX(CASE WHEN row_num = 8 THEN date END) AS date8packed
  FROM (
          SELECT date, ROW_NUMBER() OVER(ORDER BY date) - 1 AS row_num
          FROM ( VALUES(dateStart),(date0),(date1),(date2),(date3),(date4),(date5),(date6),(date7) ) AS dates(date)
          WHERE date > '19000101'
     ) AS dates1
    ) AS dates2
  )
  SELECT
      DATEDIFF(DAY, date0packed, date1packed) AS diff1,
      DATEDIFF(DAY, date1packed, date2packed) AS diff2,
      DATEDIFF(DAY, date2packed, date3packed) AS diff3,
      DATEDIFF(DAY, date3packed, date4packed) AS diff4,
      DATEDIFF(DAY, date4packed, date5packed) AS diff5,
      DATEDIFF(DAY, date5packed, date6packed) AS diff6,
      DATEDIFF(DAY, date6packed, date7packed) AS diff7,
      DATEDIFF(DAY, date7packed, date8packed) AS diff8,
      dateStart, date0, date1, date2, date3, date4, date5, date6, date7
  FROM cte_dates

  Thanks Scott, that works.

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  April 24, 2018 at 10:59 am

  #1988080

  ScottPletcher - Tuesday, April 24, 2018 8:11 AM

  Nice, but I think you'll need to a NULLIF(..., '19000101') to make this work based on the original data specs:
  

  The null dates are 1900-01-01.

  True, but it was not clear if 1900-01-01 was substituted for NULL for presentation of if it was in fact the data (as confirmed later)
  Any way yours is the best answer 🙂
  

  Far away is close at hand in the images of elsewhere.
  Anon.

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  April 24, 2018 at 12:36 pm

  #1988099

  tinausa - Tuesday, April 24, 2018 9:00 AM

  Jeff Moden - Tuesday, April 24, 2018 7:33 AM

  Shifting gears a bit, I probably wouldn't store the original data in such a horizontal format because you'd need to add columns to the table to add other event points.  It also makes calculations such as that requested more difficult.

  Thanks Jeff, you are right. But this is the extract that I get from BI with several million rows of data dump. I am doing data cleaning first to get what I need and also calculate the date difference and put it in a different table.

  Amazing... to get what you need, you are undoing what BI did instead of them just giving you access to the normalized data. Heh... it's one of the reasons why I sometimes refer to "BI" as an oxymoron. 😀

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)