Query a dynamic date range based off current month

  • sean.r.mason

    SSC Enthusiast

    Points: 105

    Iā€™m trying to determine which month Iā€™m in so I can setup aSSIS package to auto run and drop off a file into a folder monthly. The queryneeds to be run on the first of each month but since not all months have thesame # of days the date between logic needs to account for this. I have thelogic figured out thanks to the search function on this forum but my sql skillsare lacking implementing it properly.

    Error:

    Msg 137, Level 15, State 2, Line 31

    Must declare the scalar variable"@BeginMonth".

    [/code]


    --use datediff from 0, ie default 1900 date, to calculate current months as int
    declare @ThisMonth int = datediff(month,0,cast(getdate() as date))
    --add 1 to the current month to get the next month
    declare @NextBom date = dateadd(month,@ThisMonth+1,0)
    -- subtract a day from the beginning of next month to get the current end of month, without worrying about 28, 30, or 31 days.
    declare @EndMonth date = dateadd(day,-1,@NextBom)
    -- Get the first day of the current month
    declare @BeginMonth date = getdate() + (1 - DAY(getdate()))
    Go
    With FacCode as(
     Select
     PRACT_ID
     ,FacCode
     ,Current_status
     ,Status_category
     ,ROW_NUMBER() OVER
       (PARTITION BY pract_id
        ORDER BY
        CASE
         WHEN faccode = 'KFH' THEN 1
         WHEN faccode = 'HON' THEN 2
         WHEN faccode = 'WAI' THEN 3
         WHEN faccode = 'AMB' THEN 4
        END ) fac_order 
     From [provider].[tbl_msow_practitioner_facilities]
     Where Status_category Not IN ('KPIC AMB', 'Added Choice', 'Added Choice-AHP',
              'Medical Resident', 'KPIC - Added Choice')
          And Current_Status In('Active', 'Provisional', 'Inactive')
    )
    ,IDNum as(
     Select
     PRACT_ID
     ,DocumentName
     ,ID_Number
     ,Expiration_date
     ,Historical
     from [provider].[tbl_msow_practitioner_id_numbers]
     Where --Expiration_date >= CURRENT_TIMESTAMP 
     --AND Expiration_date <= DATEADD(DAY, 30, CURRENT_TIMESTAMP)
     Expiration_date Between @BeginMonth and @EndMonth
     And DocumentName Not In('Out of State License', 'Other State License')
     And Historical Not In ('1')
    )
    Select
    prac.PRACT_ID
    ,Last_Name
    ,First_Name
    ,Middle_Initial as MI
    ,Degree
    ,FacCode.Current_status
    ,FacCode.Status_category
    ,FacCode.FacCode
    ,IDNum.DocumentName
    ,IDNum.ID_Number
    ,CONVERT(VARCHAR(10), IDNum.Expiration_date, 101) as Expiration_date
    from [provider].[tbl_msow_practitioner] prac
    Inner Join FacCode on prac.PRACT_ID = FacCode.PRACT_ID
    Inner Join IDNum on IDNum.PRACT_ID = prac.PRACT_ID
    And FacCode.fac_order = 1
    Where Degree Not In('MDR')
    Order by Last_Name;

  • Sue_H

    SSC Guru

    Points: 90000

    Remove the GO command as it ends the batch so the variables are no longer recognized in the next, different batch.

    Sue

  • sean.r.mason

    SSC Enthusiast

    Points: 105

    I removed the Go but I still get an error.

  • Jonathan AC Roberts

    SSCoach

    Points: 16785

    sean.r.mason - Friday, March 8, 2019 2:12 PM

    I removed the Go but I still get an error.

    The error message says: "Incorrect syntax near the keyword 'with'.  the previous statement must be terminated with a semicolon" which means you should have:
    declare @BeginMonth date = getdate() + (1 - DAY(getdate()))  ;
    With FacCode as(

  • Clocker

    SSC Enthusiast

    Points: 117

    I would probably opt for a set based approach where you create a list of dates and then join them against your target dataset to pick out what you need.
    Since your already using a CTE, take advantage of recursion to generate a list of integers and then apply them to a dateadd() function to generate a list of dates based of "todays" date. If you want an offset start the recursion with a negative number as per the example.
    To figure out the  last day of month use  the EOMonth() function (T-SQL)

    DECLARE @MONTHS INT = 10;
    WITH Factors AS (
        SELECT -10 AS Factor
        UNION ALL
        SELECT    Factor + 1
        FROM    Factors
        WHERE    factor+1 <= @MONTHS
    )
    SELECT    Dates = EOMONTH(DATEADD(month, FACTOR, cast(getdatE() as date)))
    FROM    Factors

  • sean.r.mason

    SSC Enthusiast

    Points: 105

    LOL, That's just embarrassing... Thanks that worked perfectly much appreciated.

  • Jeff Moden

    SSC Guru

    Points: 994269

    Clocker - Friday, March 8, 2019 3:42 PM

    I would probably opt for a set based approach where you create a list of dates and then join them against your target dataset to pick out what you need.
    Since your already using a CTE, take advantage of recursion to generate a list of integers and then apply them to a dateadd() function to generate a list of dates based of "todays" date. If you want an offset start the recursion with a negative number as per the example.
    To figure out the  last day of month use  the EOMonth() function (T-SQL)

    DECLARE @MONTHS INT = 10;
    WITH Factors AS (
        SELECT -10 AS Factor
        UNION ALL
        SELECT    Factor + 1
        FROM    Factors
        WHERE    factor+1 <= @MONTHS
    )
    SELECT    Dates = EOMONTH(DATEADD(month, FACTOR, cast(getdatE() as date)))
    FROM    Factors

    Consider NOT using even "small" recursive CTEs for such thing.

    Clocker - Friday, March 8, 2019 3:42 PM

    I would probably opt for a set based approach where you create a list of dates and then join them against your target dataset to pick out what you need.
    Since your already using a CTE, take advantage of recursion to generate a list of integers and then apply them to a dateadd() function to generate a list of dates based of "todays" date. If you want an offset start the recursion with a negative number as per the example.
    To figure out the  last day of month use  the EOMonth() function (T-SQL)

    DECLARE @MONTHS INT = 10;
    WITH Factors AS (
        SELECT -10 AS Factor
        UNION ALL
        SELECT    Factor + 1
        FROM    Factors
        WHERE    factor+1 <= @MONTHS
    )
    SELECT    Dates = EOMONTH(DATEADD(month, FACTOR, cast(getdatE() as date)))
    FROM    Factors

    Consider NOT using recursive CTEs for such a thing.  See the following article as to why...
    http://www.sqlservercentral.com/articles/T-SQL/74118/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. šŸ˜‰

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff.tobin 71274

    Newbie

    Points: 3

    Perhaps you could utilize this:
    Select * from [provider].[tbl_msow_practitioner_id_numbers] where Month(Expiration_date) = Month(DateAdd(m,-1,GetDate())) and Year(Expiration_date ) = Year(DateAdd(m,-1,GetDate())

  • Lynn Pettis

    SSC Guru

    Points: 442118

    Jeff.tobin 71274 - Monday, March 11, 2019 8:26 AM

    Perhaps you could utilize this:
    Select * from [provider].[tbl_msow_practitioner_id_numbers] where Month(Expiration_date) = Month(DateAdd(m,-1,GetDate())) and Year(Expiration_date ) = Year(DateAdd(m,-1,GetDate())

    And now you can't use the index that may exist on the Expiration_date column.

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

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