Dynamic Date (Best thing I can think to describe it)

  • I have a requirement where I need to compare our billing periods side by side, our periods run from the 1st to the 15th and the 16th to the end of the month

    So in Jan it is 2011-01-01 to 2011-01-15 and 2011-01-16 to 2011-01-31, etc etc

    But we need to compare the current billing period to the previous, which is alright if your doing the later period to the ealier in the same month, but when it spans different months and years (last period in Dec, first period in Jan) becomes a bit tricker

    So far I have this

    DECLARE @ThisBillingPeriodDate DATE, @ThisBillingPeriodEndDate DATE, @LastBillingPeriodDate DATE, @LastBillingPeriodEndDate DATE

    SET @ThisBillingPeriodDate = GETDATE()

    IF DATEPART(MONTH,@ThisBillingPeriodDate) = 2 AND DATEPART(DD,@ThisBillingPeriodDate) > 15

    BEGIN

    SET @LastBillingPeriodDate = CONVERT(VARCHAR,DATEPART(YEAR,@ThisBillingPeriodDate))+'-02-01'

    SET @LastBillingPeriodEndDate = CONVERT(VARCHAR,DATEPART(YEAR,@ThisBillingPeriodDate))+'-02-15'

    END

    ELSE

    BEGIN

    SET @LastBillingPeriodDate = CONVERT(VARCHAR,DATEPART(YEAR,@ThisBillingPeriodDate))+'-01-16'

    SET @LastBillingPeriodEndDate = CONVERT(VARCHAR,DATEPART(YEAR,@ThisBillingPeriodDate))+'-01-31'

    END

    IF DATEPART(MONTH,@ThisBillingPeriodDate) = 1 AND DATEPART(DD,@ThisBillingPeriodDate) > 15

    BEGIN

    SET @LastBillingPeriodDate = CONVERT(VARCHAR,DATEPART(YEAR,@ThisBillingPeriodDate))+'-01-01'

    SET @LastBillingPeriodEndDate = CONVERT(VARCHAR,DATEPART(YEAR,@ThisBillingPeriodDate))+'-01-15'

    END

    ELSE

    BEGIN

    SET @LastBillingPeriodDate = CONVERT(VARCHAR,(DATEPART(YEAR,@ThisBillingPeriodDate)-1))+'-12-16'

    SET @LastBillingPeriodEndDate = CONVERT(VARCHAR,(DATEPART(YEAR,@ThisBillingPeriodDate)-1))+'-12-31'

    END

    IF (DATEPART(MONTH,@ThisBillingPeriodDate) BETWEEN 3 AND 12) AND DATEPART(DD,@ThisBillingPeriodDate) > 15

    BEGIN

    SET @LastBillingPeriodDate = CONVERT(VARCHAR,DATEPART(YEAR,@ThisBillingPeriodDate))+'-'+CONVERT(VARCHAR,(DATEPART(MONTH,@ThisBillingPeriodDate)))+'-01'

    SET @LastBillingPeriodDate = CONVERT(VARCHAR,DATEPART(YEAR,@ThisBillingPeriodDate))+'-'+CONVERT(VARCHAR,(DATEPART(MONTH,@ThisBillingPeriodDate)))+'-15'

    END

    ELSE

    BEGIN

    SET @LastBillingPeriodDate = CONVERT(VARCHAR,DATEPART(YEAR,@ThisBillingPeriodDate))+'-'+CONVERT(VARCHAR,(DATEPART(MONTH,@ThisBillingPeriodDate)-1))+'-16'

    SET @LastBillingPeriodDate = CONVERT(VARCHAR,DATEPART(YEAR,@ThisBillingPeriodDate))+'-'+CONVERT(VARCHAR,(DATEPART(MONTH,@ThisBillingPeriodDate)-1))+'-31'

    END

    Any ideas how to make it better, be a bit more dynamic with saying going between 30 and 31 day months etc

  • I would cheat and create a table with StartDate and EndDate that listed all the periods.

    John

  • Add that info into your calendar table. (like an Id for the period).

    Then you can either join to it or prepopulate the variables using that id to navigate up & down & between months.

    That also takes care of the # of days during the month.

  • anthony.green (11/18/2011)


    I have a requirement where I need to compare our billing periods side by side, our periods run from the 1st to the 15th and the 16th to the end of the month

    So in Jan it is 2011-01-01 to 2011-01-15 and 2011-01-16 to 2011-01-31, etc etc

    But we need to compare the current billing period to the previous, which is alright if your doing the later period to the ealier in the same month, but when it spans different months and years (last period in Dec, first period in Jan) becomes a bit tricker

    So far I have this

    DECLARE @ThisBillingPeriodDate DATE, @ThisBillingPeriodEndDate DATE, @LastBillingPeriodDate DATE, @LastBillingPeriodEndDate DATE

    SET @ThisBillingPeriodDate = GETDATE()

    IF DATEPART(MONTH,@ThisBillingPeriodDate) = 2 AND DATEPART(DD,@ThisBillingPeriodDate) > 15

    BEGIN

    SET @LastBillingPeriodDate = CONVERT(VARCHAR,DATEPART(YEAR,@ThisBillingPeriodDate))+'-02-01'

    SET @LastBillingPeriodEndDate = CONVERT(VARCHAR,DATEPART(YEAR,@ThisBillingPeriodDate))+'-02-15'

    END

    ELSE

    BEGIN

    SET @LastBillingPeriodDate = CONVERT(VARCHAR,DATEPART(YEAR,@ThisBillingPeriodDate))+'-01-16'

    SET @LastBillingPeriodEndDate = CONVERT(VARCHAR,DATEPART(YEAR,@ThisBillingPeriodDate))+'-01-31'

    END

    IF DATEPART(MONTH,@ThisBillingPeriodDate) = 1 AND DATEPART(DD,@ThisBillingPeriodDate) > 15

    BEGIN

    SET @LastBillingPeriodDate = CONVERT(VARCHAR,DATEPART(YEAR,@ThisBillingPeriodDate))+'-01-01'

    SET @LastBillingPeriodEndDate = CONVERT(VARCHAR,DATEPART(YEAR,@ThisBillingPeriodDate))+'-01-15'

    END

    ELSE

    BEGIN

    SET @LastBillingPeriodDate = CONVERT(VARCHAR,(DATEPART(YEAR,@ThisBillingPeriodDate)-1))+'-12-16'

    SET @LastBillingPeriodEndDate = CONVERT(VARCHAR,(DATEPART(YEAR,@ThisBillingPeriodDate)-1))+'-12-31'

    END

    IF (DATEPART(MONTH,@ThisBillingPeriodDate) BETWEEN 3 AND 12) AND DATEPART(DD,@ThisBillingPeriodDate) > 15

    BEGIN

    SET @LastBillingPeriodDate = CONVERT(VARCHAR,DATEPART(YEAR,@ThisBillingPeriodDate))+'-'+CONVERT(VARCHAR,(DATEPART(MONTH,@ThisBillingPeriodDate)))+'-01'

    SET @LastBillingPeriodDate = CONVERT(VARCHAR,DATEPART(YEAR,@ThisBillingPeriodDate))+'-'+CONVERT(VARCHAR,(DATEPART(MONTH,@ThisBillingPeriodDate)))+'-15'

    END

    ELSE

    BEGIN

    SET @LastBillingPeriodDate = CONVERT(VARCHAR,DATEPART(YEAR,@ThisBillingPeriodDate))+'-'+CONVERT(VARCHAR,(DATEPART(MONTH,@ThisBillingPeriodDate)-1))+'-16'

    SET @LastBillingPeriodDate = CONVERT(VARCHAR,DATEPART(YEAR,@ThisBillingPeriodDate))+'-'+CONVERT(VARCHAR,(DATEPART(MONTH,@ThisBillingPeriodDate)-1))+'-31'

    END

    Any ideas how to make it better, be a bit more dynamic with saying going between 30 and 31 day months etc

    Calendar table would be best, but it can be done like this -

    DECLARE @ThisBillingPeriodDate DATE, @ThisBillingPeriodEndDate DATE, @LastBillingPeriodDate DATE, @LastBillingPeriodEndDate DATE

    SET @ThisBillingPeriodDate = GETDATE()

    SELECT @LastBillingPeriodDate =

    CASE WHEN DATEADD(DAY, 0, DATEDIFF(DAY, 0, @ThisBillingPeriodDate)) < DATEADD(DAY, 14, DATEADD(MONTH, DATEDIFF(MONTH, 0, @ThisBillingPeriodDate), 0))

    THEN DATEADD(MONTH, DATEDIFF(MONTH, 0, @ThisBillingPeriodDate), 0)

    ELSE DATEADD(DAY, 15, DATEADD(MONTH, DATEDIFF(MONTH, 0, @ThisBillingPeriodDate), 0)) END,

    @LastBillingPeriodEndDate =

    CASE WHEN DATEADD(DAY, 0, DATEDIFF(DAY, 0, @ThisBillingPeriodDate)) < DATEADD(DAY, 14, DATEADD(MONTH, DATEDIFF(MONTH, 0, @ThisBillingPeriodDate), 0))

    THEN DATEADD(DAY, 14, DATEADD(MONTH, DATEDIFF(MONTH, 0, @ThisBillingPeriodDate), 0))

    ELSE DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @ThisBillingPeriodDate) + 1, 0)) END


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Ninja's_RGR'us (11/18/2011)


    Add that info into your calendar table. (like an Id for the period).

    Then you can either join to it or prepopulate the variables using that id to navigate up & down & between months.

    That also takes care of the # of days during the month.

    fully agree with this advice (John Mitchell as well)

    calendar tables are very useful and can accommodate a multitude of business period reporting requirements...and realtively simple to add/amend as new anlaysis is requested.

    search SSC for "calendar tables"

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • if you are going to calculate this ont eh fly, i think this works:

    --Results

    /*

    DateToTest LastBillingPeriodStartDate LastBillingPeriodEndDate ThisBillingPeriodStartDate ThisBillingPeriodEndDate

    ---------- -------------------------- ------------------------ -------------------------- ------------------------

    2011-11-03 2011-10-15 00:00:00.000 2011-10-31 00:00:00.000 2011-11-01 00:00:00.000 2011-11-15 00:00:00.000

    */

    DECLARE @DateToTest DATE

    SET @DateToTest = GETDATE() - 15

    SELECT

    @DateToTest AS DateToTest,

    LastBillingPeriodStartDate = CASE

    WHEN DATEPART(dd,@DateToTest) > 15

    THEN DATEADD(mm,-1,DATEADD(dd,-1,DATEADD(mm,1,DATEADD(mm, DATEDIFF(mm,0,@DateToTest), 0))))

    ELSE

    DATEADD(mm,-1,DATEADD(dd,14,DATEADD(mm, DATEDIFF(mm,0,@DateToTest), 0)) )

    END ,

    -- > 15 =

    --< =15 =

    LastBillingPeriodEndDate = CASE

    WHEN DATEPART(dd,@DateToTest) > 15 --it's the 15th

    THEN dateadd(mm,-1,DATEADD(dd,14,DATEADD(mm, DATEDIFF(mm,0,@DateToTest), 0)) )

    ELSE --it's the first.

    dateadd(dd,-1,DATEADD(mm, DATEDIFF(mm,0,@DateToTest), 0) )

    END ,

    -- > 15 = the 15th of this month(beginning of this month plus 14.

    --< =15 = the first of the month

    ThisBillingPeriodStartDate = CASE

    WHEN DATEPART(dd,@DateToTest) > 15 --it's the 15th

    THEN DATEADD(dd,14,DATEADD(mm, DATEDIFF(mm,0,@DateToTest), 0))

    ELSE --it's the first.

    DATEADD(mm, DATEDIFF(mm,0,@DateToTest), 0)

    END ,

    -- > 15 = the end of the month,(beginning of next month minus 1)

    --< =15 = the 15th of this month(beginning of this month plus 14.

    ThisBillingPeriodEndDate = CASE

    WHEN DATEPART(dd,@DateToTest) > 15

    THEN DATEADD(dd,-1,DATEADD(mm,1,DATEADD(mm, DATEDIFF(mm,0,@DateToTest), 0)))

    ELSE

    DATEADD(dd,14,DATEADD(mm, DATEDIFF(mm,0,@DateToTest), 0))

    END

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Just to add my own version of the same code 😀

    Edit: this of course being the version that doesn't work... :Whistling:

    DECLARE @CurrentDatePart INT, @ThisBillingPeriodDate DATE, @ThisBillingPeriodEndDate DATE, @LastBillingPeriodDate DATE, @LastBillingPeriodEndDate DATE

    SET @ThisBillingPeriodDate = GETDATE() -5

    SET @CurrentDatePart = DATEPART(DD, @ThisBillingPeriodDate)

    SET @ThisBillingPeriodDate =

    DATEADD(DD, -DATEPART(DD, @ThisBillingPeriodDate) + CASE WHEN @CurrentDatePart <= 15 THEN 1 ELSE 16 END, @ThisBillingPeriodDate)

    SET @ThisBillingPeriodEndDate =

    DATEADD(MM, 1, DATEADD(DD, -DATEPART(DD, @ThisBillingPeriodDate) + CASE WHEN @CurrentDatePart <= 15 THEN 15 ELSE 1 END, @ThisBillingPeriodDate))

    SET @LastBillingPeriodDate = DATEADD(MM, -1, @ThisBillingPeriodEndDate)

    SET @LastBillingPeriodEndDate = DATEADD(DD, -1, @ThisBillingPeriodDate)

    SELECT @ThisBillingPeriodDate, @ThisBillingPeriodEndDate, @LastBillingPeriodDate, @LastBillingPeriodEndDate

  • Thanks alot guys

    using this link http://www.sqlservercentral.com/articles/T-SQL/70482/ which is great for month based calendars, how would I adapt it to handle for the number of days in a month and have it split the 1st to the 15th and the 16th to EOM, I adapted the script to the below, which works in most cases, but is dumping extra rows in for xxxx-02-01 and a few other things for 31 day months, my first time working with calendar tables so I do appologise.

    SET NOCOUNT ON;

    CREATE TABLE BillingPeriods

    ( BillingPeriodID INT IDENTITY(1, 1)

    , BillingPeriodStart DATETIME

    , NextBillingPeriodStart DATETIME

    );

    DECLARE

    @BillingPeriodStart DATETIME,

    @NextBillingPeriodStart DATETIME

    SET @BillingPeriodStart = '2011-01-01'

    SET @NextBillingPeriodStart = DATEADD(DAY, 15, @BillingPeriodStart);

    WHILE @BillingPeriodStart < '2100-01-01'

    BEGIN

    INSERT INTO BillingPeriods

    ( BillingPeriodStart, NextBillingPeriodStart )

    SELECT

    @BillingPeriodStart, @NextBillingPeriodStart

    IF DATEPART(DAY,@NextBillingPeriodStart) >= 30

    BEGIN

    SET @BillingPeriodStart = DATEADD(DAY,1,@NextBillingPeriodStart)

    END

    ELSE

    BEGIN

    SET @BillingPeriodStart = @NextBillingPeriodStart

    END

    SET @NextBillingPeriodStart = DATEADD(DAY, 15, @NextBillingPeriodStart)

    IF DATEPART(DAY,@NextBillingPeriodStart) <= 15

    BEGIN

    SET @NextBillingPeriodStart = CONVERT(VARCHAR,DATEPART(YEAR,@NextBillingPeriodStart))+'-'+CONVERT(VARCHAR,DATEPART(MONTH,@NextBillingPeriodStart))+'-01'

    END

    IF @BillingPeriodStart = @NextBillingPeriodStart AND DATEPART(MONTH,@BillingPeriodStart) <> 2

    BEGIN

    SET @NextBillingPeriodStart = DATEADD(DAY, 16, @NextBillingPeriodStart)

    END

    END

    GO

    ALTER TABLE [dbo].[BillingPeriods] ADD CONSTRAINT [pkBillingPeriod] PRIMARY KEY CLUSTERED

    (

    BillingPeriodID ASC

    )

    GO

    CREATE UNIQUE NONCLUSTERED INDEX [ixBillingPeriods_BPS_NBPS] ON [dbo].[BillingPeriods]

    (

    BillingPeriodStart ASC,

    NextBillingPeriodStart ASC

    )

    GO

    Script Output

    12011-01-01 00:00:00.0002011-01-16 00:00:00.000

    22011-01-16 00:00:00.0002011-01-31 00:00:00.000

    32011-02-01 00:00:00.0002011-02-01 00:00:00.000

    42011-02-01 00:00:00.0002011-02-16 00:00:00.000

    52011-02-16 00:00:00.0002011-03-01 00:00:00.000

    62011-03-01 00:00:00.0002011-03-16 00:00:00.000

    Require Output

    12011-01-01 00:00:002011-01-16 00:00:00

    22011-01-16 00:00:002011-02-01 00:00:00

    32011-02-01 00:00:002011-02-16 00:00:00

    42011-02-16 00:00:002011-03-01 00:00:00

    52011-03-01 00:00:002011-03-16 00:00:00

    62011-03-16 00:00:002011-04-01 00:00:00

  • I have managed to "bodge" a solution together.

    created a while loop with the dates static and looped through

    then worked out leap years and updated feb to 29 days not 28

    something like this

    create table billingperiods

    (

    ID int,

    LastID int,

    StartDate date,

    EndDate date

    )

    go

    declare @id int, @year int, @year1 char(4)

    set @year = 2013 --already got 2011 and 2012 dates in the table

    set @year1 = @year

    set @id = 48

    while @year <2101

    begin

    insert into billingperiod (@id, @year1+'-01-01',@year1+'01-15')

    set @id = @@identity

    insert into billingperiod (@id, @year1+'-01-16',@year1+'01-31')

    set @id = @@identity

    insert into billingperiod (@id, @year1+'-02-01',@year1+'02-15')

    set @id = @@identity

    insert into billingperiod (@id, @year1+'-02-16',@year1+'02-28')

    set @id = @@identity

    insert into billingperiod (@id, @year1+'-03-01',@year1+'03-15')

    set @id = @@identity

    insert into billingperiod (@id, @year1+'-03-16',@year1+'03-31')

    set @id = @@identity

    ...

    ...

    ...

    ...

    set @year = @year + 1

    end

    declare @leapyears table (leapyear int)

    declare @ly int = 2012

    while @ly < 2101

    begin

    insert into @leapyears @ly

    set @ly = @ly + 4

    end

    update billingperiods set enddate = convert(char(4),datepart(year, enddate))+'-02-29' where left(enddate,4) in (select leapyear from @leapyears) and enddate like '%-02-28'

  • Or you can do it like this. I've used spt_values in master for my numbers, but if you prefer you can generate a numbers CTE on the fly or use your own numbers (tally) table.

    WITH numbers AS (

    SELECT number FROM master.dbo.spt_values

    WHERE name IS NULL

    )

    SELECT DATEADD(month,number,'20130101'),DATEADD(month,number,'20130115')

    FROM numbers

    UNION ALL

    SELECT DATEADD(month,number,'20130116'),DATEADD(month,number,'20130131')

    FROM numbers

    John

  • This gives you 82,138 dates and is correct for leap-years.

    IF object_id('tempdb..#testBillingPeriods') IS NOT NULL

    BEGIN

    DROP TABLE #testBillingPeriods

    END

    SELECT IDENTITY(INT,1,1) AS ID,

    startFirstMonth, startMidMonth, finishMidMonth, finishEndMonth

    INTO #testBillingPeriods

    FROM (SELECT TOP 2500000

    DATEADD(MONTH, DATEDIFF(MONTH, 0, (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)), 0) AS startFirstMonth,

    DATEADD(DAY, 15, DATEADD(MONTH, DATEDIFF(MONTH, 0, (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)), 0)) AS startMidMonth,

    DATEADD(DAY, 14, DATEADD(MONTH, DATEDIFF(MONTH, 0, (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)), 0)) AS finishMidMonth,

    DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)) + 1, 0)) AS finishEndMonth

    FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3) a

    GROUP BY startFirstMonth, startMidMonth, finishMidMonth, finishEndMonth

    ORDER BY startFirstMonth

    SELECT * FROM #testBillingPeriods

    WHERE startFirstMonth >= '2012-02-01' AND startFirstMonth < '2012-03-01'

    /* Returns

    ID startFirstMonth startMidMonth finishMidMonth finishEndMonth

    ----------- ----------------------- ----------------------- ----------------------- -----------------------

    1346 2012-02-01 00:00:00.000 2012-02-16 00:00:00.000 2012-02-15 00:00:00.000 2012-02-29 00:00:00.000

    */

    Or if you prefer : -

    IF object_id('tempdb..#testBillingPeriods') IS NOT NULL

    BEGIN

    DROP TABLE #testBillingPeriods

    END

    SELECT IDENTITY(INT,1,1) AS ID,

    startDate, finishDate

    INTO #testBillingPeriods

    FROM (SELECT TOP 2500000

    DATEADD(MONTH, DATEDIFF(MONTH, 0, (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)), 0) AS startDate,

    DATEADD(DAY, 14, DATEADD(MONTH, DATEDIFF(MONTH, 0, (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)), 0)) AS finishDate

    FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3

    UNION ALL

    SELECT TOP 2500000

    DATEADD(DAY, 15, DATEADD(MONTH, DATEDIFF(MONTH, 0, (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)), 0)) AS startDate,

    DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)) + 1, 0)) AS finishDate

    FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3

    ) a

    GROUP BY startDate, finishDate

    ORDER BY startDate

    SELECT * FROM #testBillingPeriods

    WHERE startDate >= '2012-02-01' AND startDate < '2012-03-01'

    /*Returns

    ID startDate finishDate

    ----------- ----------------------- -----------------------

    2691 2012-02-01 00:00:00.000 2012-02-15 00:00:00.000

    2692 2012-02-16 00:00:00.000 2012-02-29 00:00:00.000

    */


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Yet another approach...

    DECLARE @ThisBillingPeriodDate DATE, @ThisBillingPeriodEndDate DATE, @LastBillingPeriodDate DATE, @LastBillingPeriodEndDate DATE

    SET @ThisBillingPeriodDate = GETDATE();

    with cte_cldr_past as (

    select CAST(@ThisBillingPeriodDate as date) as cldr_dt, 0 as offset

    union all

    select DATEADD(day,-1,cldr_dt) as cldr_dt, offset-1 as offset

    from cte_cldr_past

    where offset > -31

    )

    , cte_cldr_frwd as (

    select CAST(@ThisBillingPeriodDate as date) as cldr_dt, 0 as offset

    union all

    select DATEADD(day,1,cldr_dt) as cldr_dt, offset+1 as offset

    from cte_cldr_frwd

    where datepart(day,cldr_dt) < 15

    )

    , cte_cldr as (

    select cldr_dt from cte_cldr_past

    union select cldr_dt from cte_cldr_frwd

    )

    , cte_perd as (

    select cldr_dt, last_day, ROW_NUMBER() OVER(order by cldr_dt desc) as row_id

    from (

    select cldr_dt,

    cast(case when datepart(day,cldr_dt) = 1 then DATEADD(day,14,cldr_dt)

    else

    DATEADD(day,-1,CAST(convert(varchar(4),datepart(year,DATEADD(month,1,cldr_dt))) + '-' +

    convert(varchar(2),datepart(MONTH,DATEADD(month,1,cldr_dt))) + '-' + '01' as date))

    end as date) as last_day

    from cte_cldr) c1

    where DATEPART(day,c1.cldr_dt) in (1,16)

    )

    select

    @ThisBillingPeriodDate= curr_perd_strt_dt,

    @ThisBillingPeriodEndDate = curr_perd_end_dt,

    @LastBillingPeriodDate = prior_perd_strt_dt,

    @LastBillingPeriodEndDate = prior_perd_end_dt

    from

    (select cldr_dt as curr_perd_strt_dt, last_day as curr_perd_end_dt

    from cte_perd

    where row_id = 1) curr_perd

    cross join (

    select cldr_dt as prior_perd_strt_dt, last_day as prior_perd_end_dt

    from cte_perd

    where row_id = 2) prior_perd

    select @ThisBillingPeriodDate [@ThisBillingPeriodDate],

    @ThisBillingPeriodEndDate [@ThisBillingPeriodEndDate],

    @LastBillingPeriodDate [@LastBillingPeriodDate],

    @LastBillingPeriodEndDate [@LastBillingPeriodEndDate]

Viewing 13 posts - 1 through 12 (of 12 total)

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