We help IT Professionals succeed at work.

TSQL Challenge...

212 Views
Last Modified: 2017-03-08
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
Comment
Watch Question

Commented:
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 EghtebasDatabase and Application Developer

Author

Commented:
@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.

Commented:
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 EghtebasDatabase and Application Developer

Author

Commented:
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.

Commented:
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

IT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Mike EghtebasDatabase and Application Developer

Author

Commented:
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.