Post reply

Create parameter from getdate() to extract all previous years

  • December 14, 2011 at 2:40 am

    #146269

    Hi,

    I have a requirement to display all previous years starting from 2010 and above as parameters. The only way I can think of doing it currently is through this code (assuming it would be applicable from 2012):

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

    Select datepart(yy,getdate())-2 As Year

    union

    Select datepart(yy,getdate())-1 As Year

    union

    Select datepart(yy,getdate()) As Year

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

    Does anyone know of automating this code so that no amendments are necessary from next year onwards ? I am new to SQL so apologies if this might seem basic or stupid.

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

    Issue resolved: used a table column for this:

    select distinct left(SAISAN_MONTH,4) as year from DHL_TEMP order by left(SAISAN_MONTH,4)

    Thanks !

    Thanks,

    Paul

  • December 14, 2011 at 1:38 pm

    #1421589

    whitout table, you can use

    DECLARE @FEC_INI DATETIME, @FEC_FIN DATETIME

    SELECT @FEC_INI = '01/01/1900', @FEC_FIN = '31/12/2010';

    WITH DIAS AS

    (

    SELECT @FEC_INI AS FEC

    UNION ALL

    SELECT FEC + 366 FROM DIAS WHERE FEC + 366 <= @FEC_FIN

    )

    SELECT YEAR(FEC) FROM DIAS OPTION (MAXRECURSION 365)

  • December 14, 2011 at 1:47 pm

    #1421596

    TallyTable?

    something like this:

    SELECT SomeStuff

    FROM BusinessTable

    WHERE FiscalYear IN(SELECT YearNumber --An integer like 2010

    FROM TallyCalendar

    WHERE YearNumber BETWEEN 2010 AND YEAR(GETDATE()))

    If you do not have a Tally Calendar of your own, here'a link to a copy of mine:

    TallyCalendar_Complete_With_DST.txt

    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!

  • December 14, 2011 at 9:13 pm

    #1421717

    pwalter83 (12/14/2011)

    Hi,

    I have a requirement to display all previous years starting from 2010 and above as parameters. The only way I can think of doing it currently is through this code (assuming it would be applicable from 2012):

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

    Select datepart(yy,getdate())-2 As Year

    union

    Select datepart(yy,getdate())-1 As Year

    union

    Select datepart(yy,getdate()) As Year

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

    Does anyone know of automating this code so that no amendments are necessary from next year onwards ? I am new to SQL so apologies if this might seem basic or stupid.

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

    Issue resolved: used a table column for this:

    select distinct left(SAISAN_MONTH,4) as year from DHL_TEMP order by left(SAISAN_MONTH,4)

    Thanks !

    Thanks,

    Paul

    The following is good for 2048 years... we should all live so long. 😛 It uses SQL Server's built in "Tally" table.

    SELECT Year = DATEPART(yy,DATEADD(yy,Number,'2010'))

    FROM master.dbo.spt_Values

    WHERE Type = 'P'

    AND Number BETWEEN 0 AND DATEDIFF(yy,'2010',GETDATE())

    {EDIT}... just saw that you already solved it.

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

  • December 14, 2011 at 9:15 pm

    #1421718

    raultr (12/14/2011)

    whitout table, you can use

    DECLARE @FEC_INI DATETIME, @FEC_FIN DATETIME

    SELECT @FEC_INI = '01/01/1900', @FEC_FIN = '31/12/2010';

    WITH DIAS AS

    (

    SELECT @FEC_INI AS FEC

    UNION ALL

    SELECT FEC + 366 FROM DIAS WHERE FEC + 366 <= @FEC_FIN

    )

    SELECT YEAR(FEC) FROM DIAS OPTION (MAXRECURSION 365)

    That uses a "Counting rCTE". Please see the following article for why that's a bad thing.

    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.

    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)

  • December 15, 2011 at 4:39 am

    #1421837

    Jeff Moden (12/14/2011)

    pwalter83 (12/14/2011)

    Hi,

    I have a requirement to display all previous years starting from 2010 and above as parameters. The only way I can think of doing it currently is through this code (assuming it would be applicable from 2012):

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

    Select datepart(yy,getdate())-2 As Year

    union

    Select datepart(yy,getdate())-1 As Year

    union

    Select datepart(yy,getdate()) As Year

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

    Does anyone know of automating this code so that no amendments are necessary from next year onwards ? I am new to SQL so apologies if this might seem basic or stupid.

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

    Issue resolved: used a table column for this:

    select distinct left(SAISAN_MONTH,4) as year from DHL_TEMP order by left(SAISAN_MONTH,4)

    Thanks !

    Thanks,

    Paul

    The following is good for 2048 years... we should all live so long. 😛 It uses SQL Server's built in "Tally" table.

    SELECT Year = DATEPART(yy,DATEADD(yy,Number,'2010'))

    FROM master.dbo.spt_Values

    WHERE Type = 'P'

    AND Number BETWEEN 0 AND DATEDIFF(yy,'2010',GETDATE())

    {EDIT}... just saw that you already solved it.

    Thanks a lot Jeff, that's really helpful !!! I am thinking of replacing my code with your one...:-)

  • December 15, 2011 at 5:17 am

    #1421850

    pwalter83 (12/15/2011)

    Thanks a lot Jeff, that's really helpful !!! I am thinking of replacing my code with your one...:-)

    You bet and thank you for the feedback. Shifting gears, please consider building a real Tally Table instead of using the one that MS provided (which could change with any cummulative update, hot fix, or SP). Here's the link for how to build one and how it works...

    http://www.sqlservercentral.com/articles/T-SQL/62867/

    --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 7 posts - 1 through 7 (of 7 total)

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