locked
select query RRS feed

  • Question

  • hi,

    let us consider the following table.

    Quarter  Year

    Q1  2010

    Q2 2010

    Q3 2010

    Q4 2010

    Q1      2011

    Q2      2011

    Q3      2011

    Q4      2011

    In the above way, there are many quarter and Years for 2009,2008...... when i pass quarter and year as parameters , i should get all the quarter and years less than that quarter and year.

    Ex:  when i pass Q3, 2011 as parameters then i should get all Quarters less than 2011 and quarters q1,q2,q3 in 2011 .

    how to achieve this in only single query with out using union and union  all etc in the query?

    Please help me in fixing this.

    thanks.

    Tuesday, February 7, 2012 7:57 AM

Answers

  • If you want to dynamically get the values you have to use the database functions including GETDATE() and DATE functions.

    With Quarter and year, in what format do you want them. You need to do some code to get any custom formats as you need.

    The following is a function I have written which provides all the quarters between two given dates.

    There's some redundant code in it. You can remove it if you do not need it. I have added a select query on top for testing the output. The function returns values in date time values.

    SELECT *
    FROM   dbo.[ufnGetFromToDateList]('1/1/2011' ,'1/1/2012' ,'Q' ,'Y' ,NULL)
    
    CREATE FUNCTION [dbo].[ufnGetFromToDateList]
    (
    	@dtFrom           DATETIME
       ,@dtTo             DATETIME
       ,@cBreakBy         CHAR(1)
       ,@cBreakImmediate  CHAR(1)
       ,@cIncludeTime     CHAR(1)
    )
    RETURNS @retFromToDateTable TABLE 
            (dtFrom DATETIME ,dtTo DATETIME)
    AS
    BEGIN
        IF @cBreakBy IS NULL
           OR @cBreakImmediate IS NULL
           OR @dtFrom IS NULL
           OR @dtTo IS NULL
           OR @cBreakImmediate<>'Y'
        BEGIN
            INSERT @retFromToDateTable
              (
                dtFrom
               ,dtTo
              )
            SELECT @dtFrom
                  ,@dtTo
        END
        ELSE 
        IF @cBreakBy='M'
        BEGIN
            -- List all First and Last Day of Manths within the given time period inclusive of the given dates  
            ;WITH 
            cteTally AS 
            (
                --==== Returns a value of 1 to the number of months in date range  
                SELECT TOP(
                           DATEDIFF(
                               mm
                              ,DATEADD(mm ,DATEDIFF(mm ,0 ,@dtFrom) ,0)	--First of start month
                              ,DATEADD(mm ,DATEDIFF(mm ,0 ,@dtTo)+1 ,0)
                           )
                       ) --First of month after end month  
                       N = ROW_NUMBER() OVER(ORDER BY t1.Object_ID)
                FROM   MASTER.sys.All_Columns t1
                       CROSS JOIN MASTER.sys.All_Columns t2
            )  
            INSERT @retFromToDateTable
              (
                dtFrom
               ,dtTo
              )
            SELECT DATEADD(mm ,DATEDIFF(mm ,0 ,@dtFrom)+t.N-1 ,0)	-- 1st of a Month
                  ,CASE 
                        WHEN @cIncludeTime IS NULL OR @cIncludeTime<>'Y' THEN 
                             DATEADD(dd ,-1 ,DATEADD(mm ,DATEDIFF(mm ,0 ,@dtFrom)+t.N ,0)) -- Last of a Month
                        ELSE DATEADD(ms ,-3 ,DATEADD(mm ,DATEDIFF(mm ,0 ,@dtFrom)+t.N ,0))
                   END
            FROM   cteTally t
            ORDER BY
                   N DESC
        END
        ELSE 
        IF @cBreakBy='Q'
        BEGIN
            -- List all First and Last Day of Quarters within the given time period inclusive of the given dates  
            ;WITH 
            cteTally AS 
            (
                --==== Returns a value of 1 to the number of Quarters in date range  
                SELECT TOP(
                           DATEDIFF(
                               qq
                              ,DATEADD(qq ,DATEDIFF(qq ,0 ,@dtFrom) ,0)	--First of start Quarters
                              ,DATEADD(qq ,DATEDIFF(qq ,0 ,@dtTo)+1 ,0)
                           )
                       ) --First of Quarters after end Quarters  
                       N = ROW_NUMBER() OVER(ORDER BY t1.Object_ID)
                FROM   MASTER.sys.All_Columns t1
                       CROSS JOIN MASTER.sys.All_Columns t2
            )  
            INSERT @retFromToDateTable
              (
                dtFrom
               ,dtTo
              )
            SELECT DATEADD(qq ,DATEDIFF(qq ,0 ,@dtFrom)+t.N-1 ,0)	-- 1st of a Quarters
                  ,--      DATEADD(ms,-3,DATEADD(qq,DATEDIFF(qq,0,@dtFrom)+t.N,0)) -- Last of a Quarters  
                   CASE 
                        WHEN @cIncludeTime IS NULL OR @cIncludeTime<>'Y' THEN 
                             DATEADD(dd ,-1 ,DATEADD(qq ,DATEDIFF(qq ,0 ,@dtFrom)+t.N ,0)) -- Last of a Month
                        ELSE DATEADD(ms ,-3 ,DATEADD(qq ,DATEDIFF(qq ,0 ,@dtFrom)+t.N ,0))
                   END
            FROM   cteTally t
            ORDER BY
                   N DESC
        END
        ELSE 
        IF @cBreakBy='Y'
        BEGIN
            -- List all First and Last Day of Quarters within the given time period inclusive of the given dates  
            ;WITH 
            cteTally AS 
            (
                --==== Returns a value of 1 to the number of months in date range  
                SELECT TOP(
                           DATEDIFF(
                               yy
                              ,DATEADD(yy ,DATEDIFF(yy ,0 ,@dtFrom) ,0)	--First of start month
                              ,DATEADD(yy ,DATEDIFF(yy ,0 ,@dtTo)+1 ,0)
                           )
                       ) --First of month after end month  
                       N = ROW_NUMBER() OVER(ORDER BY t1.Object_ID)
                FROM   MASTER.sys.All_Columns t1
                       CROSS JOIN MASTER.sys.All_Columns t2
            )  
            INSERT @retFromToDateTable
              (
                dtFrom
               ,dtTo
              )
            SELECT DATEADD(yy ,DATEDIFF(yy ,0 ,@dtFrom)+t.N-1 ,0)	-- 1st of a Month
                  ,--     DATEADD(dd,-1,DATEADD(yy,DATEDIFF(yy,0,@dtFrom)+t.N,0)) -- Last of a Month  
                   CASE 
                        WHEN @cIncludeTime IS NULL OR @cIncludeTime<>'Y' THEN 
                             DATEADD(dd ,-1 ,DATEADD(yy ,DATEDIFF(yy ,0 ,@dtFrom)+t.N ,0)) -- Last of a Month
                        ELSE DATEADD(ms ,-3 ,DATEADD(yy ,DATEDIFF(yy ,0 ,@dtFrom)+t.N ,0))
                   END
            FROM   cteTally t
            ORDER BY
                   N DESC
        END
        ELSE 
        IF @cBreakBy='D'
        BEGIN
            -- List all First and Last Day of Quarters within the given time period inclusive of the given dates  
            ;WITH 
            cteTally AS 
            (
                --==== Returns a value of 1 to the number of days in date range  
                SELECT TOP(
                           DATEDIFF(
                               dd
                              ,DATEADD(dd ,DATEDIFF(dd ,0 ,@dtFrom) ,0)	--Whole day start of range
                              ,DATEADD(dd ,DATEDIFF(dd ,0 ,@dtTo)+1 ,0)
                           )
                       ) --Whole day end of range  
                       N = ROW_NUMBER() OVER(ORDER BY t1.Object_ID)
                FROM   MASTER.sys.All_Columns t1
                       CROSS JOIN MASTER.sys.All_Columns t2
            )  
            INSERT @retFromToDateTable
              (
                dtFrom
               ,dtTo
              )
            SELECT DATEADD(dd ,DATEDIFF(dd ,0 ,@dtFrom) ,0)+t.N-1
                  ,CASE 
                        WHEN @cIncludeTime IS NULL OR @cIncludeTime<>'Y' THEN 
                             DATEADD(dd ,DATEDIFF(dd ,0 ,@dtFrom) ,0)+t.N-1
                        ELSE DATEADD(ms ,-3 ,DATEADD(dd ,DATEDIFF(dd ,0 ,@dtFrom) ,0)+t.N)
                   END
            FROM   cteTally t
            ORDER BY
                   N DESC
        END
        ELSE
        BEGIN
            INSERT @retFromToDateTable
              (
                dtFrom
               ,dtTo
              )
            SELECT @dtFrom
                  ,@dtTo
        END 
        
        RETURN
    END  

    Hope this is useful.

    - Arun Kumar Allu



    • Edited by arun.passioniway Tuesday, February 7, 2012 2:23 PM reformatted code block.
    • Proposed as answer by arun.passioniway Friday, February 10, 2012 10:09 AM
    • Marked as answer by Iric Wen Monday, February 20, 2012 9:44 AM
    Tuesday, February 7, 2012 2:20 PM

All replies

  • create table #t (q char(2),y int)


    insert into #t values ('Q1',2011)
    insert into #t values ('Q2',2011)
    insert into #t values ('Q3',2011)
    insert into #t values ('Q4',2011)




    declare @y int,@q char(2)
    set @y=2011
    set @q='Q3'
    select * from #t where 
    y=@y and q<= @q

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Tuesday, February 7, 2012 8:10 AM
    Answerer
  • hi Uri Dimant,

    THanks for the reply.

    But the select query will not return all the qaurter and years less than 2011 . the select should also include 2010,2009....

    Thanks in advance.

    Tuesday, February 7, 2012 8:55 AM
  • Hi,

    SELECT *
    FROM TABLENAME WHERE 
    Year < 2011 AND Quart < 'Q1'

    OR

    You can Uri Dimants' query and change this part to

    declare @y int,@q char(2)
    set @y=2011
    set @q='Q3'
    select * from #t where 
    y<@y and q<= @q

    if your table contains years less than 2011 you will get it.

    I hope this is helpful.

    Elmozamil Elamir

    MyBlog


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid
    http://elmozamil.blogspot.com

    Tuesday, February 7, 2012 9:43 AM
  • Hi ,

       You can change the Uri Dimant query to get the desired result:

    -- Filter for year 2010 and Q3
    select * from #t
    where (y < 2010)
    
    union
    
    select * from #t
    where (y = 2010 and q <='Q3')
    order by Y,q


    -- Reddy Balaji C. ##Mark as answer if it solves the issue

    Tuesday, February 7, 2012 9:57 AM
  • Yes as others have already pointed small change will do the job :-)

    select * from #t where 
    y<=@y and q<= @q


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Tuesday, February 7, 2012 10:03 AM
    Answerer
  • Hi ,

         I think we have to use union with multiple queries to fetch the required results. Only one query with "y<=@y and q<= @q" condition won't fetch required result, as it will filter the required quarters for the below years.


    -- Reddy Balaji C. ##Mark as answer if it solves the issue

    Tuesday, February 7, 2012 10:21 AM
  • If you want to dynamically get the values you have to use the database functions including GETDATE() and DATE functions.

    With Quarter and year, in what format do you want them. You need to do some code to get any custom formats as you need.

    The following is a function I have written which provides all the quarters between two given dates.

    There's some redundant code in it. You can remove it if you do not need it. I have added a select query on top for testing the output. The function returns values in date time values.

    SELECT *
    FROM   dbo.[ufnGetFromToDateList]('1/1/2011' ,'1/1/2012' ,'Q' ,'Y' ,NULL)
    
    CREATE FUNCTION [dbo].[ufnGetFromToDateList]
    (
    	@dtFrom           DATETIME
       ,@dtTo             DATETIME
       ,@cBreakBy         CHAR(1)
       ,@cBreakImmediate  CHAR(1)
       ,@cIncludeTime     CHAR(1)
    )
    RETURNS @retFromToDateTable TABLE 
            (dtFrom DATETIME ,dtTo DATETIME)
    AS
    BEGIN
        IF @cBreakBy IS NULL
           OR @cBreakImmediate IS NULL
           OR @dtFrom IS NULL
           OR @dtTo IS NULL
           OR @cBreakImmediate<>'Y'
        BEGIN
            INSERT @retFromToDateTable
              (
                dtFrom
               ,dtTo
              )
            SELECT @dtFrom
                  ,@dtTo
        END
        ELSE 
        IF @cBreakBy='M'
        BEGIN
            -- List all First and Last Day of Manths within the given time period inclusive of the given dates  
            ;WITH 
            cteTally AS 
            (
                --==== Returns a value of 1 to the number of months in date range  
                SELECT TOP(
                           DATEDIFF(
                               mm
                              ,DATEADD(mm ,DATEDIFF(mm ,0 ,@dtFrom) ,0)	--First of start month
                              ,DATEADD(mm ,DATEDIFF(mm ,0 ,@dtTo)+1 ,0)
                           )
                       ) --First of month after end month  
                       N = ROW_NUMBER() OVER(ORDER BY t1.Object_ID)
                FROM   MASTER.sys.All_Columns t1
                       CROSS JOIN MASTER.sys.All_Columns t2
            )  
            INSERT @retFromToDateTable
              (
                dtFrom
               ,dtTo
              )
            SELECT DATEADD(mm ,DATEDIFF(mm ,0 ,@dtFrom)+t.N-1 ,0)	-- 1st of a Month
                  ,CASE 
                        WHEN @cIncludeTime IS NULL OR @cIncludeTime<>'Y' THEN 
                             DATEADD(dd ,-1 ,DATEADD(mm ,DATEDIFF(mm ,0 ,@dtFrom)+t.N ,0)) -- Last of a Month
                        ELSE DATEADD(ms ,-3 ,DATEADD(mm ,DATEDIFF(mm ,0 ,@dtFrom)+t.N ,0))
                   END
            FROM   cteTally t
            ORDER BY
                   N DESC
        END
        ELSE 
        IF @cBreakBy='Q'
        BEGIN
            -- List all First and Last Day of Quarters within the given time period inclusive of the given dates  
            ;WITH 
            cteTally AS 
            (
                --==== Returns a value of 1 to the number of Quarters in date range  
                SELECT TOP(
                           DATEDIFF(
                               qq
                              ,DATEADD(qq ,DATEDIFF(qq ,0 ,@dtFrom) ,0)	--First of start Quarters
                              ,DATEADD(qq ,DATEDIFF(qq ,0 ,@dtTo)+1 ,0)
                           )
                       ) --First of Quarters after end Quarters  
                       N = ROW_NUMBER() OVER(ORDER BY t1.Object_ID)
                FROM   MASTER.sys.All_Columns t1
                       CROSS JOIN MASTER.sys.All_Columns t2
            )  
            INSERT @retFromToDateTable
              (
                dtFrom
               ,dtTo
              )
            SELECT DATEADD(qq ,DATEDIFF(qq ,0 ,@dtFrom)+t.N-1 ,0)	-- 1st of a Quarters
                  ,--      DATEADD(ms,-3,DATEADD(qq,DATEDIFF(qq,0,@dtFrom)+t.N,0)) -- Last of a Quarters  
                   CASE 
                        WHEN @cIncludeTime IS NULL OR @cIncludeTime<>'Y' THEN 
                             DATEADD(dd ,-1 ,DATEADD(qq ,DATEDIFF(qq ,0 ,@dtFrom)+t.N ,0)) -- Last of a Month
                        ELSE DATEADD(ms ,-3 ,DATEADD(qq ,DATEDIFF(qq ,0 ,@dtFrom)+t.N ,0))
                   END
            FROM   cteTally t
            ORDER BY
                   N DESC
        END
        ELSE 
        IF @cBreakBy='Y'
        BEGIN
            -- List all First and Last Day of Quarters within the given time period inclusive of the given dates  
            ;WITH 
            cteTally AS 
            (
                --==== Returns a value of 1 to the number of months in date range  
                SELECT TOP(
                           DATEDIFF(
                               yy
                              ,DATEADD(yy ,DATEDIFF(yy ,0 ,@dtFrom) ,0)	--First of start month
                              ,DATEADD(yy ,DATEDIFF(yy ,0 ,@dtTo)+1 ,0)
                           )
                       ) --First of month after end month  
                       N = ROW_NUMBER() OVER(ORDER BY t1.Object_ID)
                FROM   MASTER.sys.All_Columns t1
                       CROSS JOIN MASTER.sys.All_Columns t2
            )  
            INSERT @retFromToDateTable
              (
                dtFrom
               ,dtTo
              )
            SELECT DATEADD(yy ,DATEDIFF(yy ,0 ,@dtFrom)+t.N-1 ,0)	-- 1st of a Month
                  ,--     DATEADD(dd,-1,DATEADD(yy,DATEDIFF(yy,0,@dtFrom)+t.N,0)) -- Last of a Month  
                   CASE 
                        WHEN @cIncludeTime IS NULL OR @cIncludeTime<>'Y' THEN 
                             DATEADD(dd ,-1 ,DATEADD(yy ,DATEDIFF(yy ,0 ,@dtFrom)+t.N ,0)) -- Last of a Month
                        ELSE DATEADD(ms ,-3 ,DATEADD(yy ,DATEDIFF(yy ,0 ,@dtFrom)+t.N ,0))
                   END
            FROM   cteTally t
            ORDER BY
                   N DESC
        END
        ELSE 
        IF @cBreakBy='D'
        BEGIN
            -- List all First and Last Day of Quarters within the given time period inclusive of the given dates  
            ;WITH 
            cteTally AS 
            (
                --==== Returns a value of 1 to the number of days in date range  
                SELECT TOP(
                           DATEDIFF(
                               dd
                              ,DATEADD(dd ,DATEDIFF(dd ,0 ,@dtFrom) ,0)	--Whole day start of range
                              ,DATEADD(dd ,DATEDIFF(dd ,0 ,@dtTo)+1 ,0)
                           )
                       ) --Whole day end of range  
                       N = ROW_NUMBER() OVER(ORDER BY t1.Object_ID)
                FROM   MASTER.sys.All_Columns t1
                       CROSS JOIN MASTER.sys.All_Columns t2
            )  
            INSERT @retFromToDateTable
              (
                dtFrom
               ,dtTo
              )
            SELECT DATEADD(dd ,DATEDIFF(dd ,0 ,@dtFrom) ,0)+t.N-1
                  ,CASE 
                        WHEN @cIncludeTime IS NULL OR @cIncludeTime<>'Y' THEN 
                             DATEADD(dd ,DATEDIFF(dd ,0 ,@dtFrom) ,0)+t.N-1
                        ELSE DATEADD(ms ,-3 ,DATEADD(dd ,DATEDIFF(dd ,0 ,@dtFrom) ,0)+t.N)
                   END
            FROM   cteTally t
            ORDER BY
                   N DESC
        END
        ELSE
        BEGIN
            INSERT @retFromToDateTable
              (
                dtFrom
               ,dtTo
              )
            SELECT @dtFrom
                  ,@dtTo
        END 
        
        RETURN
    END  

    Hope this is useful.

    - Arun Kumar Allu



    • Edited by arun.passioniway Tuesday, February 7, 2012 2:23 PM reformatted code block.
    • Proposed as answer by arun.passioniway Friday, February 10, 2012 10:09 AM
    • Marked as answer by Iric Wen Monday, February 20, 2012 9:44 AM
    Tuesday, February 7, 2012 2:20 PM
  • Can you add in an extra counting column so that Q1 2010 has a value 1 etc.

    Then you would select the counting column for Q1 2011 and select all the counting columns less than that

    1 q1 2010

    2 q2 2010

    3 q3 2010

    4 q4 2010

    5 q1 2011

    6 q2 2011

    7 q3 2011

    SELECT quarter, year

    from table

    where counting < 7

    Seth


    http://lqqsql.wordpress.com

    Tuesday, February 7, 2012 2:34 PM
  • Hi All,

    I think sudeep wants some dynamically generated quarters before a given date. His question is not clear if his data is static or dynamically generated.

    The data he has provided is only an example for illustrating his requirement.

    This should need a lot of logic as per his requirement. No knit picking on replies, Hope we are not misleaded by the example.


    - Arun Kumar Allu

    Tuesday, February 7, 2012 3:55 PM