Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    37

    Unanswered: Number of 'Periods' in a date range

    Guys
    Date brain killer - I have a startdate and enddate and need, for a given period length in months, to identify the number
    of periods betwen the startdate and enddate
    so for example

    DECLARE @Periods TABLE
    (
    StartDate datetime,
    Enddate datetime,
    NumberOfMonthsInPeriod INT ,
    TotPeriods INT
    )

    -- Initial data
    insert @Periods
    (
    StartDate ,
    Enddate ,
    NumberOfMonthsInPeriod ,
    TotPeriods -- for this illustration, initialised to 0 but need to be UPDATEd as per detail below
    )

    select
    '30-Sep-2005',
    '10-Apr-2009',
    1 -- 1 month period
    0
    union all
    select
    '30-Sep-2005',
    '10-Apr-2009',
    3 -- 3 month period
    0
    union all
    select
    '30-Sep-2005',
    '10-Apr-2009',
    6 -- 6 month period
    0

    The following rules regarding periods apply
    Each 1 (NumberOfMonthsInPeriod) month period is as would be expected ie
    01-Jan - 31 Jan
    01-Feb - 28-Feb (ie 1st March - 1 day which would deal with leap years - dateadd(...)
    and so on to December

    Each 3 (NumberOfMonthsInPeriod) month period is one of the following 'bands' per year
    01-Jan - 31-Mar
    01-Apr - 30-Jun
    01-Jul - 30-Sep
    01-Oct - 31-Dec

    Each 6 (NumberOfMonthsInPeriod) month period is one of the following
    01-Jan - 30-Jun
    01-Jul - 31-Dec

    I need to derive TotPeriods as follows:

    For the row where NumberOfMonthsInPeriod = 1, the first period ie the one the start date falls within is 01-Sep-2005 to 30-Sep-2005, second is 01-Oct-2005 - 31-Oct-2005 and so on until
    last period ie the one the end datye falls within is 01-Apr-2009 - 30-Apr-2009, a TotPeriods value of 44

    For the row where NumberOfMonthsInPeriod = 3, first period is 01-Jul-2005 to 30-Sep-2005, second is 01-Oct-2005 - 31-Dec-2005 and so on until
    last period is 01-Apr-2009 - 30-Jun-2009, a TotPeriods value of 16

    For the row where NumberOfMonthsInPeriod = 6, first period is 01-Jul-2005 to 31-Dec-2005, second is 01-Jan-2006 - 30-Jun-2006 and so on until
    last period is 01-Jan-2009 - 30-Jun-2009, a TotPeriods value of 8

    Hope this is clear and thanks in advance

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You started off good, but I still don't get it. What' sthe expect results, and what defines a period again?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2005
    Posts
    37
    Brett
    Expected results is the TotPeriods value - I've come up with a solution (below) but wonder if there isn't a more elegant or less verbose way of doing it

    DECLARE @Periods TABLE
    (
    StartDate datetime,
    Enddate datetime,
    NumberOfMonthsInPeriod INT ,
    TotPeriods INT,
    periodstartdate datetime ,
    periodenddate datetime
    )

    -- Initial data
    insert @Periods
    (
    StartDate ,
    Enddate ,
    NumberOfMonthsInPeriod ,
    TotPeriods
    )

    select
    '02-Jul-2004',
    '10-Dec-2005',
    1 ,-- 1 month period
    0
    union all
    select
    '02-Jul-2004',
    '10-Dec-2005',
    3 ,-- 3 month period
    0
    union all
    select
    '02-Jul-2004',
    '10-Dec-2005',
    6 ,-- 6 month period
    0




    update @Periods
    set periodstartdate =
    case when NumberOfMonthsInPeriod = 1 then cast('01' + '-' +
    cast (datepart(mm,startdate) as varchar) + '-' +
    cast (datepart(yyyy,startdate) as varchar)
    as datetime)

    when NumberOfMonthsInPeriod = 3 then cast(
    '01' + '-' +
    case when datepart(mm,startdate) in (1,2,3) then 'Jan'
    when datepart(mm,startdate) in (4,5,6) then 'Apr'
    when datepart(mm,startdate) in (7,8,9) then 'Jul'
    when datepart(mm,startdate) in (10,11,12) then 'Oct'
    end
    + '-' +
    cast (datepart(yyyy,startdate) as varchar)
    as datetime)

    when NumberOfMonthsInPeriod = 6 then cast(
    '01' + '-' +
    case when datepart(mm,startdate) in (1,2,3,4,5,6) then 'Jan'
    when datepart(mm,startdate) in (7,8,9,10,11,12) then 'Jul'
    end
    + '-' +
    cast (datepart(yyyy,startdate) as varchar)
    as datetime)
    end ,
    --convert enddate to first of whatever instead of 16th, add a month and knock off a day
    periodenddate =
    case when NumberOfMonthsInPeriod = 1 then
    dateadd(dd , -1 ,
    dateadd (mm , 1 ,
    cast(
    '01' + '-' + cast(datepart(mm,enddate) as varchar) + '-' +
    cast(datepart (yyyy, enddate) as varchar)
    as varchar)
    )
    )

    when NumberOfMonthsInPeriod = 3 then
    dateadd(dd , -1 ,
    dateadd (mm , 1 ,
    cast(

    '01' + '-' +
    case when datepart(mm,enddate) in (1,2,3) then 'Mar'
    when datepart(mm,enddate) in (4,5,6) then 'Jun'
    when datepart(mm,enddate) in (7,8,9) then 'Sep'
    when datepart(mm,enddate) in (10,11,12) then 'Dec'
    end
    + '-' +

    cast(datepart (yyyy, enddate) as varchar)
    as varchar)
    )
    )

    when NumberOfMonthsInPeriod = 6 then
    dateadd(dd , -1 ,
    dateadd (mm , 1 ,
    cast(

    '01' + '-' +
    case when datepart(mm,enddate) in (1,2,3,4,5,6) then 'Jun'
    when datepart(mm,enddate) in (7,8,9,10,11,12) then 'Dec'
    end
    + '-' +
    cast(datepart (yyyy, enddate) as varchar)
    as varchar)
    )
    )

    end

    update @Periods
    set TotPeriods =
    datediff(mm , periodstartdate , dateadd(dd,1,periodenddate) ) / NumberOfMonthsInPeriod

    select * from @Periods

  4. #4
    Join Date
    Oct 2005
    Posts
    37
    PS Brett - sorry to answer your question, a period is the length of time based on the NumberOfMonthsInPeriod value so if the value is 1 it's a 1 month period, if 3 it's a 3 month period etc

    Thx
    SW

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •