If possible need a more elegant solution to a current brute force view

  • so I have a third party database I do an ETL to another third party system.  One of the fields in the extract database table is a PeriodID field.  unfortunately this third party application uses a "name"  i.e. January, February, March etc etc.  the system I am loading to needs an EOM date. i.e. 1/31/2019 2/28/2019 3/31/2019 etc. etc.

    I did this brute force code that works just fine, but would require annual maintenance.  Can anyone help me with a better solution?

    Case

    When PeriodID = 'January' THEN '1/31/2019'

    When PeriodID = 'February' THEN '2/28/2019'

    When PeriodID = 'March' THEN '3/31/2019'

    When PeriodID = 'April' THEN '4/30/2019'

    When PeriodID = 'May' THEN '5/31/2019'

    When PeriodID = 'June'  THEN '6/30/2019'

    When PeriodID = 'July'  THEN '7/31/2019'

    When PeriodID = 'August'  THEN '8/31/2019'

    When PeriodID = 'September'  THEN '9/30/2019'

    When PeriodID = 'October'  THEN '10/31/2019'

    When PeriodID = 'November'  THEN '11/30/2019'

    Else '12/31/2019' END

    AS PeriodEndDate,

     

  • Something along these lines:

    Declare @jan01 date
    Set @jan01 = Dateadd(Year, Datediff(Year, 0, GETDATE()), 0)

    select
    Case Left(PeriodID, 3)

    When 'Jan' THEN Dateadd(Day, -1, Dateadd(Month, 1, @jan01), 0)

    When 'Feb' THEN Dateadd(Day, -1, Dateadd(Month, 2, @jan01), 0)

    When 'Mar' THEN Dateadd(Day, -1, Dateadd(Month, 3, @jan01), 0)

    When ...

    END

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Can't you just concatenate Year?

    When PeriodID = 'January' 
    THEN '1/31/' + CAST(YEAR(CURRENT_TIMESTAMP) AS CHAR(4))

    --Vadim R.

  • So I tried the date function

    Dateadd(Day, -1, Dateadd(Month, 1, @jan01), 0)

    and I get an error message

    The DateAdd function requires 3 arguments.

  • yes as you were posting I was searching the internet and found the year function.  I did not do the CHAR(4) as you did so your solution worked.

  • however if I just concatenate the year, then every 4 years I need to update for the leap year affect.

  • I think the first solution is the best for me in the instance.  As it would account for year and leap year changes.  but I keep getting an error  message

    The DateAdd function requires 3 arguments.

    I have not figured out how to modify it to work.  still working on it.

     

  • never mind got it. thanks

    When 'Jan' THEN Dateadd(Day, -1, Dateadd(Month, 1, @jan01))

  • Oops, yep, sorry.  A copy/paste where I accidentally left the ", 0" at the end.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Checking for leap year is fairly easy:

    DECLARE @Date AS DATE = '20120101'
    IF ISDATE(CAST(YEAR(@Date) AS CHAR(4)) + '0229') = 1
    PRINT 'Leap'
    ELSE
    PRINT 'Norm'

    --Vadim R.

  • You could also do this:

    Declare @firstOfYear date = dateadd(year, datediff(year, 0, getdate()), 0);

    Select Case PeriodID
    When 'January' Then eomonth(@firstOfYear, 00)
    When 'February' Then eomonth(@firstOfYear, 01)
    When 'March' Then eomonth(@firstOfYear, 02)
    When 'April' Then eomonth(@firstOfYear, 03)
    When 'May' Then eomonth(@firstOfYear, 04)
    When 'June' Then eomonth(@firstOfYear, 05)
    When 'July' Then eomonth(@firstOfYear, 06)
    When 'August' Then eomonth(@firstOfYear, 07)
    When 'September' Then eomonth(@firstOfYear, 08)
    When 'October' Then eomonth(@firstOfYear, 09)
    When 'November' Then eomonth(@firstOfYear, 10)
    When 'December' Then eomonth(@firstOfYear, 11)
    End;

    You can also generate the first of the year using: datefromparts(year(getdate()), 1, 1)

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • thanks for all the posts.  I have it now, and it works great.  I really appreciate all of you posting these options.  As you can  tell I am not a proficient SQL person, but I am learning.  thank you.

  • randyetheridge wrote:

    thanks for all the posts.  I have it now, and it works great.  I really appreciate all of you posting these options.  As you can  tell I am not a proficient SQL person, but I am learning.  thank you.

    Excellent.  Please post the code your using now.  Thanks.

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

    Change is inevitable... Change for the better is not.


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

  • You can also use the following, which will take care of teh leap year

    DECLARE @Year int = 2019;

    SELECT PeriodID, EOMONTH(PeriodID + ' 01 ' + CONVERT(VARCHAR(4), @Year)) AS PeriodEndDate
  • DesNorton wrote:

    You can also use the following, which will take care of teh leap year

    DECLARE @Year int = 2019;

    SELECT PeriodID, EOMONTH(PeriodID + ' 01 ' + CONVERT(VARCHAR(4), @Year)) AS PeriodEndDate

    True enough but this is a 2008 forum.

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

    Change is inevitable... Change for the better is not.


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

Viewing 15 posts - 1 through 15 (of 24 total)

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