April 23, 2018 at 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
April 23, 2018 at 3:53 pm
tinausa - Monday, April 23, 2018 3:43 PMI 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.
April 23, 2018 at 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
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".
April 24, 2018 at 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
Far away is close at hand in the images of elsewhere.
Anon.
April 24, 2018 at 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.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 24, 2018 at 8:11 am
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".
April 24, 2018 at 9:00 am
Jeff Moden - Tuesday, April 24, 2018 7:33 AMShifting 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.
April 24, 2018 at 9:12 am
tinausa - Tuesday, April 24, 2018 9:00 AMJeff Moden - Tuesday, April 24, 2018 7:33 AMShifting 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.
April 24, 2018 at 9:18 am
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.
April 24, 2018 at 10:59 am
ScottPletcher - Tuesday, April 24, 2018 8:11 AMNice, 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.
April 24, 2018 at 12:36 pm
tinausa - Tuesday, April 24, 2018 9:00 AMJeff Moden - Tuesday, April 24, 2018 7:33 AMShifting 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
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply