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

  • randyetheridge

    SSCommitted

    Points: 1613

    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,

     

  • ScottPletcher

    SSC Guru

    Points: 97979

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • rVadim

    Hall of Fame

    Points: 3844

    Can't you just concatenate Year?

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

    SSCommitted

    Points: 1613

    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.

  • randyetheridge

    SSCommitted

    Points: 1613

    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.

  • randyetheridge

    SSCommitted

    Points: 1613

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

  • randyetheridge

    SSCommitted

    Points: 1613

    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.

     

  • randyetheridge

    SSCommitted

    Points: 1613

    never mind got it. thanks

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

  • ScottPletcher

    SSC Guru

    Points: 97979

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

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • rVadim

    Hall of Fame

    Points: 3844

    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'
  • Jeffrey Williams 3188

    SSC Guru

    Points: 87975

    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)

     

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • randyetheridge

    SSCommitted

    Points: 1613

    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.

  • Jeff Moden

    SSC Guru

    Points: 993779

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

  • DesNorton

    SSC-Insane

    Points: 22491

    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
  • Jeff Moden

    SSC Guru

    Points: 993779

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

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

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