asked on

TSQL Challenge...

I have participated in finding a solution for the following question:
Given:

create table #Data1 (id int, StartDate datetime, EndDate datetime);

INSERT INTO #Data1  -- populate table with your data data (start - end range)
    (id, StartDate, EndDate)
VALUES
    (1, '2017-01-01 00:00:00', '2017-01-04 00:00:00'),
    (2, '2017-01-19 00:00:00', '2017-01-22 00:00:00'),
    (3, '2017-02-01 00:00:00', '2017-02-04 00:00:00');

Select * From #Data1;
id          StartDate                  EndDate
1	2017-01-01 00:00:00.000	2017-01-04 00:00:00.000
2	2017-01-19 00:00:00.000	2017-01-22 00:00:00.000
3	2017-02-01 00:00:00.000	2017-02-04 00:00:00.000

Note: The working date-range used later to create table #FullRange is: 
'2017-01-01', Min. StartDate is 
Max. EndDate is   '2017-02-04'

Open in new window

Find, "how do I write a SQL to find gaps in date ranges?"

--------------
The challenge is to accomplish the same task (in the above link, see my last comment starting with "Ok, I have it now:" without the use of the temp tables. Use either derived tables or CTEs.

Thank you,

Mike

bcnagel

Maybe there is more to the question than I understand right now, but doesn't the original Oracle code (with some tweaks over to T-SQL) work just fine?

create table #icr_tmp
	(
		ID varchar(15),
		NMI varchar(50),
		INVOICE_START_DATE date,
		INVOICE_END_DATE date
	); 

insert into #icr_tmp
	values ('12345', '4444444', '01/03/2015', '02/02/2015'); 

insert into #icr_tmp
	values ('12346', '4444444', '02/03/2015', '03/02/2015'); 

insert into #icr_tmp
	values ('12347', '4444444', '04/03/2015', '05/01/2015'); 

insert into #icr_tmp
	values ('12348', '4444444', '05/02/2015', '06/01/2015'); 

insert into #icr_tmp
	values ('12349', '4444444', '06/02/2015', '07/01/2015'); 

insert into #icr_tmp
	values ('12310', '4444444', '07/02/2015', '07/31/2015'); 

insert into #icr_tmp
	values ('12310', '4444444', '09/01/2015', '09/30/2015'); 

-- 
select *
	from (
		select #icr_tmp.NMI, dateadd(day, 1, max(#icr_tmp.INVOICE_END_DATE) over (partition by #icr_tmp.NMI order by #icr_tmp.INVOICE_START_DATE)) start_gap,
			dateadd(day, -1, lead(#icr_tmp.INVOICE_START_DATE) over (partition by #icr_tmp.NMI order by #icr_tmp.INVOICE_START_DATE)) end_gap
			from #icr_tmp
	) der
	where der.start_gap <= der.end_gap;

Open in new window

Mike Eghtebas

ASKER

@bcnagel,

I have the solution using temp tables:

SELECT DaysFullRange 
FROM #FullRange      --table containing date range from min. start data through max. end date in a single column. 
EXCEPT
SELECT UsedDays     
FROM #RangeUsed  --table containing dates used in a single column.

Open in new window

I want to do it without the temp tables. Use cte and/or derived tables. I think there is no need for the temp tables if one knows how to juggle a few CTEs and/or Derived tables to make this work.

Thanks.

bcnagel

Got it, Mike. I guess I'm just wondering what kind of output you're looking for. In the original post, it looks like the questioner wanted the output to look like this...

NMI      START_GAP   END_GAP
4444444  2015-03-03  2015-04-02
4444444  2015-08-01  2015-08-31

Open in new window

...is that what you are looking for, too?

If that is what you are looking for, doesn't the code below give that output without any temp tables?

if object_id('dbo.icr_tmp') is not null drop table dbo.icr_tmp

-- 
create table dbo.icr_tmp
	(
		ID varchar(15),
		NMI varchar(50),
		INVOICE_START_DATE date,
		INVOICE_END_DATE date
	); 

-- 
insert into dbo.icr_tmp (ID, NMI, INVOICE_START_DATE, INVOICE_END_DATE)
values 
('12345', '4444444', '01/03/2015', '02/02/2015'),
('12346', '4444444', '02/03/2015', '03/02/2015'),
('12347', '4444444', '04/03/2015', '05/01/2015'),
('12348', '4444444', '05/02/2015', '06/01/2015'),
('12349', '4444444', '06/02/2015', '07/01/2015'),
('12310', '4444444', '07/02/2015', '07/31/2015'),
('12310', '4444444', '09/01/2015', '09/30/2015')

-- 
;with cte_Gaps as (
	select i.NMI, dateadd(day, 1, max(i.INVOICE_END_DATE) over (partition by i.NMI order by i.INVOICE_START_DATE)) start_gap,
		dateadd(day, -1, lead(i.INVOICE_START_DATE) over (partition by i.NMI order by i.INVOICE_START_DATE)) end_gap
		from dbo.icr_tmp i
	)

select *
	from cte_Gaps g
	where g.start_gap <= g.end_gap;

Open in new window

Mike Eghtebas

ASKER

My final output is:

SELECT DaysFullRange 
FROM #FullRange  
EXCEPT
SELECT UsedDays 
FROM #RangeUsed 

  DaysFullRange
2017-01-05 00:00:00.000
2017-01-06 00:00:00.000
2017-01-07 00:00:00.000
2017-01-08 00:00:00.000
2017-01-09 00:00:00.000
2017-01-10 00:00:00.000
2017-01-11 00:00:00.000
2017-01-12 00:00:00.000
2017-01-13 00:00:00.000
2017-01-14 00:00:00.000
2017-01-15 00:00:00.000
2017-01-16 00:00:00.000
2017-01-17 00:00:00.000
2017-01-18 00:00:00.000
2017-01-23 00:00:00.000
2017-01-24 00:00:00.000
2017-01-25 00:00:00.000
2017-01-26 00:00:00.000
2017-01-27 00:00:00.000
2017-01-28 00:00:00.000
2017-01-29 00:00:00.000
2017-01-30 00:00:00.000
2017-01-31 00:00:00.000
2017-02-05 00:00:00.000
2017-02-06 00:00:00.000
2017-02-07 00:00:00.000
2017-02-08 00:00:00.000
2017-02-09 00:00:00.000
2017-02-10 00:00:00.000
2017-02-11 00:00:00.000
2017-02-12 00:00:00.000
2017-02-13 00:00:00.000
2017-02-14 00:00:00.000
2017-02-15 00:00:00.000
2017-02-16 00:00:00.000
2017-02-17 00:00:00.000
2017-02-18 00:00:00.000
2017-02-19 00:00:00.000
2017-02-20 00:00:00.000
2017-02-21 00:00:00.000
2017-02-22 00:00:00.000
2017-02-23 00:00:00.000
2017-02-24 00:00:00.000
2017-02-25 00:00:00.000
2017-02-26 00:00:00.000
2017-02-27 00:00:00.000
2017-02-28 00:00:00.000
2017-03-01 00:00:00.000
2017-03-02 00:00:00.000
2017-03-03 00:00:00.000
2017-03-04 00:00:00.000
2017-03-05 00:00:00.000
2017-03-06 00:00:00.000
2017-03-07 00:00:00.000
2017-03-08 00:00:00.000
2017-03-09 00:00:00.000
2017-03-10 00:00:00.000
2017-03-11 00:00:00.000
2017-03-12 00:00:00.000
2017-03-13 00:00:00.000
2017-03-14 00:00:00.000
2017-03-15 00:00:00.000
2017-03-16 00:00:00.000
2017-03-17 00:00:00.000
2017-03-18 00:00:00.000
2017-03-19 00:00:00.000
2017-03-20 00:00:00.000
2017-03-21 00:00:00.000
2017-03-22 00:00:00.000
2017-03-23 00:00:00.000
2017-03-24 00:00:00.000
2017-03-25 00:00:00.000
2017-03-26 00:00:00.000
2017-03-27 00:00:00.000
2017-03-28 00:00:00.000
2017-03-29 00:00:00.000
2017-03-30 00:00:00.000
2017-03-31 00:00:00.000
2017-04-01 00:00:00.000
2017-04-02 00:00:00.000

Open in new window

Could you do the same using CTEs and or Derived Tables (and without two of the temp tables, #FullRange and #RangeUsed )?

The use of #Data1 (id int, StartDate datetime, EndDate datetime) is okay. This is the data supplied and we have to work with it.

bcnagel

How about something like this?

if object_id('tempdb..#Data1') is not null
	drop table #Data1

-- table containing your data (start - end range)
create table #Data1 (
		id int,
		StartDate datetime,
		EndDate datetime
	)


insert into #Data1  -- populate table with your data data (start - end range)
		(id, StartDate, EndDate)
	values	(1, '2017-01-01 00:00:00', '2017-01-04 00:00:00'),
			(2, '2017-01-19 00:00:00', '2017-01-22 00:00:00'),
			(3, '2017-02-01 00:00:00', '2017-02-04 00:00:00')


-- 
;with CTE_GenDate as (
	select datefromparts(2017, 1, 1) DayYear
	union all
	select dateadd(day, 1, CTE_GenDate.DayYear)
		from CTE_GenDate
		where CTE_GenDate.DayYear < datefromparts(2017, 12, 31)
	)

select *
	from CTE_GenDate g
	where g.DayYear >= '01-01-2016'
		and g.DayYear <= '04-02-2017' -- table containing days in full min/max range from #Data1.
	and not exists (
		select *
			from #Data1 as d
			where g.DayYear between d.StartDate and d.EndDate
		)
option (maxrecursion 366)

Open in new window

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Eghtebas

ASKER

Thank you very much. It produces what I havebeen looking for (80 rows):

2017-01-05
2017-01-06
2017-01-07
2017-01-08
2017-01-09
2017-01-10
.
.
2017-03-30
2017-03-31
2017-04-01
2017-04-02