T SQL Advance Report Month Range on the 16th - Looking For More Effect. approach

  • rdsb_2170

    SSC Eights!

    Points: 812

    Greetings All,

    I'm looking for suggestions on how to improve the script below.

    Requirements: From the 1st of the month to the 15th, report on the current month plus 4 additional months ( total of five months). From the 16th of the month to the end of the month, advance the month range and report on the current month + 1 plus 4 additional months (total of five months). Example: August 1st - 15th would be August through December. On August 16th we would report on September through January 2020. The query needs to account for crossing the year divide.

    I have scripted the following and I'm still testing, but would be interested in know if I can use a more efficient approach with maybe a While Loop or something. I included a screenshot of the data that would be returned. Summary total would happen after this section of script.

    DECLARE @MONTHNUM varchar(2);

    DECLARE @CurrentYear char(4);

    DECLARE @NextYear char(4);

    DECLARE @Daynum varchar(2);

    SET @MonthNum = DatePart(month,getdate());

    SET @CurrentYear = DatePart(Year,getdate());

    SET @NextYear = DatePart(Year,getdate()) +1;

    SET @Daynum = DatePart(day,getdate())-1;

    IF @Daynum <= 15 and @Monthnum between 1 and 7

    Select * From #MonthProjectedCounts Where monthnum between currentmonth and currentmonth +4;

    else

    IF @Daynum > 15 and @Monthnum between 1 and 7

    Select * From #MonthProjectedCounts Where monthnum between currentmonth +1 and currentmonth +5;

    IF @Daynum <= 15 and @Monthnum = 8

    Select * From #MonthProjectedCounts Where Yearnum = 2019 and monthnum between currentmonth and currentmonth +4;

    else

    IF @Daynum > 15 and @Monthnum = 8

    (

    Select * From #MonthProjectedCounts Where (Yearnum = 2019 and monthnum between currentmonth +1 and currentmonth +3)

    UNION

    Select * From #MonthProjectedCounts Where (Yearnum = 2020 and Monthnum = 1)

    );

    IF @Daynum <= 15 and @Monthnum = 9

    (

    Select * From #MonthProjectedCounts Where Yearnum = 2019 and monthnum between currentmonth and currentmonth +3

    UNION

    Select * From #MonthProjectedCounts Where (Yearnum = 2020 and Monthnum = 1)

    );

    else

    IF @Daynum > 15 and @Monthnum = 9

    (

    Select * From #MonthProjectedCounts Where (Yearnum = 2019 and monthnum between currentmonth +1 and currentmonth +2)

    UNION

    Select * From #MonthProjectedCounts Where (Yearnum = 2020 and Monthnum in (1,2))

    );

    ...additional script for month 10,11,12Capture

    • This topic was modified 3 months, 1 week ago by  rdsb_2170.
  • rdsb_2170

    SSC Eights!

    Points: 812

    rdsb_2170@cfl.rr.com

  • Phil Parkin

    SSC Guru

    Points: 243785

    Use something like this to get your start and end dates:

    DECLARE @StartDate DATE = DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0);
    DECLARE @DayNo TINYINT = DAY(GETDATE());

    IF @DayNo > 15
    SET @StartDate = DATEADD(MONTH, 1, @StartDate);

    DECLARE @Enddate DATE = DATEADD(MONTH, 5, @StartDate);

    From then on, it's simply

    SELECT ... 
    FROM ...
    WHERE @StartDate>= DATEFROMPARTS(Yearnum,Monthnum,Daynum) and DATEFROMPARTS(Yearnum,Monthnum,Daynum) < @EndDate

    Of course, it would be far better if your source data contained a real date column, rather than INTs, because this would

    a) Make your query easier to read and maintain, and

    b) Open up the possibility of using indexes to make your query run faster.

    • This reply was modified 3 months, 1 week ago by  Phil Parkin. Reason: Fix error in query

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • drew.allen

    SSC Guru

    Points: 76642

    I used a slightly different approach.  First, I totally agree with Phil.  It is much, MUCH easier to work with dates.  Days and Months are cyclical and it's very hard to work with cyclical data.  Dates are linear and are much easier to work with.

    I set up a calendar table to indicate that my code would handle the change in month.  The final formula can use any date expression where I have used the date field.  You'll probably want to use GETDATE().

    CREATE TABLE #calendar (dt DATE);

    INSERT #calendar (dt)
    VALUES
    ('20190801')
    , ('20190802')
    , ('20190803')
    , ('20190804')
    , ('20190805')
    , ('20190806')
    , ('20190807')
    , ('20190808')
    , ('20190809')
    , ('20190810')
    , ('20190811')
    , ('20190812')
    , ('20190813')
    , ('20190814')
    , ('20190815')
    , ('20190816')
    , ('20190817')
    , ('20190818')
    , ('20190819')
    , ('20190820')
    , ('20190821')
    , ('20190822')
    , ('20190823')
    , ('20190824')
    , ('20190825')
    , ('20190826')
    , ('20190827')
    , ('20190828')
    , ('20190829')
    , ('20190830')
    , ('20190831')
    ;

    SELECT
    dt
    , DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, -15, c.dt)) + 1, 0) AS start_dt
    , DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, -15, c.dt)) + 5, 0) AS end_dt
    FROM #calendar AS c
    ;

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 4 posts - 1 through 4 (of 4 total)

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