Help with Daterange split please

Question

Post reply

Help with Daterange split please

misstryguy

Ten Centuries

Points: 1052
More actions
June 1, 2017 at 9:48 pm

#321759

CREATE TABLE table1 (
PersonID int,
startdate datetime,
enddate datetime
);
INSERT INTO [DBA].[dbo].[table1]([PersonID],[startdate],[enddate])VALUES(1,'01/01/2016','12/31/2016')
INSERT INTO [DBA].[dbo].[table1]([PersonID],[startdate],[enddate])VALUES(2,'01/01/2015','12/31/2016')
INSERT INTO [DBA].[dbo].[table1]([PersonID],[startdate],[enddate])VALUES(3,'09/01/2015','12/31/2017')
GO
select * from table1
I am looking for results like below. Basically, I need to split the start and end date by 6 month periods and I am interested only in start dates that are in 2016
PersonID    startdate    enddate        Startdate1    Startdate2
1        01/01/16    12/31/16    01/01/16    07/01/16
2        01/01/15    12/31/16    01/01/16    07/01/16
3        09/01/15    12/31/17    03/01/16    09/01/16
Any help is much appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Can you explain the logic in records for PersonID 2 and 3?
They seem like contradicting each other.

_____________
Code for TallyGenerator

misstryguy Ten Centuries Points: 1052 More actions · Answer 2

for person 2 (01/01/15 -12/31/16 ) (when split by 6 months becomes)
01/01/16 - 06/30/2016
07/01/2016 - 12/31/2016

for person 3 (09/01/15 -12/31/17) (when split by 6 months becomes)
09/01/2015 - 02/28/2016
03/01/2016 - 08/31/2016
09/01/2016 - 02/28/2017
03/01/2017 - 08/31/2017
09/01/2017 - 12/31/2017 (this case is less than 6 months)

In both cases I am interested in getting the start dates that are in 2016 year and don't need any other dates.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

misstryguy - Thursday, June 1, 2017 11:23 PM
for person 2 (01/01/15 -12/31/16 ) (when split by 6 months becomes)
01/01/16 - 06/30/2016
07/01/2016 - 12/31/2016
for person 3 (09/01/15 -12/31/17) (when split by 6 months becomes)
09/01/2015 - 02/28/2016
03/01/2016 - 08/31/2016
09/01/2016 - 02/28/2017
03/01/2017 - 08/31/2017
09/01/2017 - 12/31/2017 (this case is less than 6 months)
In both cases I am interested in getting the start dates that are in 2016 year and don't need any other dates.

OK, makes sense now.

Here we go:

select * , DATEADD(mm, N*6, [startdate]) from table1 inner join dbo.TallyGenerator (0, 1000, null, 1) on [enddate] >= DATEADD(mm, N*6, [startdate]) where YEAR(DATEADD(mm, N*6, [startdate]) ) = 2016 order by PersonID, N

TallyGenerator is an inline table function.
You may use the one I posted in "Scripts" section on this site, or use any other version of it, or a static Tally table.
Any one would do.

_____________
Code for TallyGenerator

misstryguy Ten Centuries Points: 1052 More actions · Answer 4

This works great Sergiy!. Thank you so much!

I need to convert the multiple rows for single person to single person and multiple columns

Thanks
Kris

Sergiy SSC Guru Points: 110208 More actions · Answer 5

You're gonna need ROWNUMBER to the SELECT part (ordering by start date) and then do cross tab query on top of it.

_____________
Code for TallyGenerator