Post reply

Help with Daterange split please

  • 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.

  • June 1, 2017 at 11:02 pm

    #1945005

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

    _____________
    Code for TallyGenerator

  • June 1, 2017 at 11:23 pm

    #1945010

    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.

  • June 1, 2017 at 11:36 pm

    #1945011

    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

  • June 2, 2017 at 12:02 am

    #1945012

    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

  • June 2, 2017 at 1:13 am

    #1945018

    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

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

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