locked
Need help with query RRS feed

  • Question

  • Hi,

    I have a table called Seasons with 2 fields: season_id int, season_name nvarchar (50), season_year int
    The table consists of the following

    Season_year

    Season_name

    Season_id

    2009

    Unavailable

    0

    2009

    Winter

    1

    2009

    Spring

    2

    2009

    Summer

    3

    2009

    Autumn

    4


    I have to write a query that will get the last season in the table ie Autumn 2009 and will also return in addition the next season which is Winter 2010. If the last season in the table was Summer 2009 then the query must return Summer 2009 and Autumn 2009.
    In addition the query must check if the current season is in fact in the table. Months 1-3=Winter, Months 4-6=Spring;
    Months 7-9=Summer and months 10-12=Autumn.
    So if it's November 2009 I need to check that Autumn 2009 is in the db. If not I need to return the last season in the db together with Autumn 2009 and Winter 2010.
    If the current season is in the db then i only need to return the current season and the following season Winter 2010.

    Not sure how to write the query.

    I will appreciate any help.

    Thanks
    Friday, November 13, 2009 2:36 PM

Answers

  • The following solution uses a table-valued function to map a date to year and season. To go to next season, it adds 3 months to the date. Let us know if works for you.

    -- SQL Server 2008 T-SQL
    -- Table-valued function (TVF)
    CREATE FUNCTION fnSeasons 
                   (@Date DATE) 
    RETURNS TABLE 
    AS 
      RETURN 
        (SELECT SeasonYear = YEAR(@Date), 
                SeasonName = CASE 
                               WHEN DATEPART(QQ,@Date) = 1 THEN 'Winter' 
                               WHEN DATEPART(QQ,@Date) = 2 THEN 'Spring' 
                               WHEN DATEPART(QQ,@Date) = 3 THEN 'Summer' 
                               ELSE 'Fall' 
                             END) 
    
    GO 
    DECLARE @Now date = '2009-10-23'
    
    -- Get current season info
    SELECT * FROM dbo.fnSeasons (@Now)
    /* 
    SeasonYear	SeasonName
    2009		Fall
    */
    -- Get next season info
    SELECT * FROM dbo.fnSeasons (dateadd(mm,3,@Now))
    GO
    /*
    SeasonYear	SeasonName
    2010		Winter
    */
    

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    • Proposed as answer by Abdshall Saturday, November 14, 2009 10:15 PM
    • Marked as answer by Zongqing Li Friday, November 20, 2009 8:48 AM
    Saturday, November 14, 2009 9:43 AM

All replies

  • Could you populate the table for 2010 values?
    every day is a school day
    Friday, November 13, 2009 2:56 PM
  • Hi,

    Yea if i could that would be easier but unfortunately i can't.
    Saturday, November 14, 2009 8:52 AM
  • The following solution uses a table-valued function to map a date to year and season. To go to next season, it adds 3 months to the date. Let us know if works for you.

    -- SQL Server 2008 T-SQL
    -- Table-valued function (TVF)
    CREATE FUNCTION fnSeasons 
                   (@Date DATE) 
    RETURNS TABLE 
    AS 
      RETURN 
        (SELECT SeasonYear = YEAR(@Date), 
                SeasonName = CASE 
                               WHEN DATEPART(QQ,@Date) = 1 THEN 'Winter' 
                               WHEN DATEPART(QQ,@Date) = 2 THEN 'Spring' 
                               WHEN DATEPART(QQ,@Date) = 3 THEN 'Summer' 
                               ELSE 'Fall' 
                             END) 
    
    GO 
    DECLARE @Now date = '2009-10-23'
    
    -- Get current season info
    SELECT * FROM dbo.fnSeasons (@Now)
    /* 
    SeasonYear	SeasonName
    2009		Fall
    */
    -- Get next season info
    SELECT * FROM dbo.fnSeasons (dateadd(mm,3,@Now))
    GO
    /*
    SeasonYear	SeasonName
    2010		Winter
    */
    

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    • Proposed as answer by Abdshall Saturday, November 14, 2009 10:15 PM
    • Marked as answer by Zongqing Li Friday, November 20, 2009 8:48 AM
    Saturday, November 14, 2009 9:43 AM
  • Thanks i think that is what i need. You are a life saver :-)
    Saturday, November 14, 2009 9:07 PM