none
GROUP BY Week, Month, or Quarter

    Question

  • Hi Everyone,

    I am creating a report query where the user will select the desired interval from the report and the query will aggregate the data either by Week, Month, or Quarter. 

    My query includes a "Time" table, here are the fields:

    CREATE TABLE [dbo].[TIME](
    	[PK_Date] [datetime] NOT NULL,
    	[Date_Name] [nvarchar](50) NULL,
    	[Year] [datetime] NULL,
    	[Year_Name] [nvarchar](50) NULL,
    	[Half_Year] [datetime] NULL,
    	[Half_Year_Name] [nvarchar](50) NULL,
    	[Quarter] [datetime] NULL,
    	[Quarter_Name] [nvarchar](50) NULL,
    	[Month] [datetime] NULL,
    	[Month_Name] [nvarchar](50) NULL,
    	[Week] [datetime] NULL,
    	[Week_Name] [nvarchar](50) NULL,
    
     CONSTRAINT [PK_Time] PRIMARY KEY CLUSTERED 
    (
    	[PK_Date] ASC
    )

    How can I write this query so it will dynamically return the rows based on a @Parameter?

    Is this efficient:

    SELECT
      CASE @Interval
        WHEN 'Quarter' THEN Quarter
        WHEN 'Month' THEN Month
        WHEN 'Week' THEN Week
      END As ROW_DATE,

    Wednesday, September 02, 2009 4:40 AM

Answers

  • IF @Interval = 'Quarter'
    	SELECT		...
    	FROM		...
    	WHERE		...
    	GROUP BY	Year,
    			Quarter
    ELSE IF @Interval = 'Month'
    	SELECT		...
    	FROM		...
    	WHERE		...
    	GROUP BY	Year,
    			Month
    ELSE IF @Interval = 'Week'
    	SELECT		...
    	FROM		...
    	WHERE		...
    	GROUP BY	Year,
    			Week
    
    • Marked as answer by SamCosta Wednesday, September 02, 2009 5:42 PM
    Wednesday, September 02, 2009 5:52 AM

All replies

  • IF @Interval = 'Quarter'
    	SELECT		...
    	FROM		...
    	WHERE		...
    	GROUP BY	Year,
    			Quarter
    ELSE IF @Interval = 'Month'
    	SELECT		...
    	FROM		...
    	WHERE		...
    	GROUP BY	Year,
    			Month
    ELSE IF @Interval = 'Week'
    	SELECT		...
    	FROM		...
    	WHERE		...
    	GROUP BY	Year,
    			Week
    
    • Marked as answer by SamCosta Wednesday, September 02, 2009 5:42 PM
    Wednesday, September 02, 2009 5:52 AM
  • Hello,

    Try this...

    CREATE TABLE [dbo].[TIME](
    	[PK_Date] [datetime] NOT NULL,
    	--[Date_Name] [nvarchar](50) NULL,
    	[Year] [datetime] NULL,
    	--[Year_Name] [nvarchar](50) NULL,
    	--[Half_Year] [datetime] NULL,
    	--[Half_Year_Name] [nvarchar](50) NULL,
    	[Quarter] [datetime] NULL,
    	--[Quarter_Name] [nvarchar](50) NULL,
    	[Month] [datetime] NULL,
    	--[Month_Name] [nvarchar](50) NULL,
    	[Week] [datetime] NULL,
    	--[Week_Name] [nvarchar](50) NULL,
    
     CONSTRAINT [PK_Time] PRIMARY KEY CLUSTERED 
    (
    	[PK_Date] ASC
    )
    )
    GO
    CREATE TABLE [dbo].[Test]
    (
    	[Date] DATETIME,
    	[ID] INT
    )
    
    INSERT INTO [dbo].[TIME] VALUES ('2009-09-02',2009,03,09,36)
    
    INSERT INTO [dbo].[Test] VALUES ('2009-09-02',1)
    INSERT INTO [dbo].[Test] VALUES ('2009-09-02',2)
    INSERT INTO [dbo].[Test] VALUES ('2009-09-02',3)
    
    DECLARE @Interval NVARCHAR(50)
    
    SET @Interval = 'Month'
    
    SELECT CASE @Interval
    	WHEN 'Quarter'  THEN [Quarter]
    	WHEN 'Month'  THEN [Month]
    	WHEN 'Week'  THEN [Week]
    	   END AS ROW_DATE,
    	   @Interval AS Par,
    	   SUM(ID)
    FROM [dbo].[TIME] Ti
    INNER JOIN [dbo].[Test] Te
    ON Ti.PK_Date = Te.[Date]
    GROUP BY CASE @Interval
    	WHEN 'Quarter'  THEN [Quarter]
    	WHEN 'Month'  THEN [Month]
    	WHEN 'Week'  THEN [Week]
    	   END
    	

    Hope helpful...


     


    Pavan http://www.ggktech.com
    Wednesday, September 02, 2009 5:55 AM
  • Are there INDEXes on Quarter, Month, and Week?
    Wednesday, September 02, 2009 1:04 PM
  • This is a disastrous design flaw. You have invalid data elements, random naming conventions instead of ISO standards, attribute splitting.


    SELECT * FROM reports AS a INNER JOIN report_calendar AS b ON a.created_date BETWEEN b.start_date AND b.end_date;
    Wednesday, September 02, 2009 1:07 PM
  • Let's see. He has TABLE that works, and meets the customer's requirements.

    Your example does not work well with SQL Server.
    Wednesday, September 02, 2009 1:20 PM
  • Brian, 
    Yes - PK_Date, Week, Quarter, Half Year, and Year are all indexed.  This is a standard Time dimension table generated by SSIS. 

    Sqlguruu, 
    Can you suggest how I can aggregiate this data WITHOUT duplicating the query three times for each interval?
    Wednesday, September 02, 2009 2:54 PM
  • The reason i asked about INDEXing, is because if it is not INDEXed, IIUC, a better query plan is useless, as all would require visiting the TABLE itself.

    Peso has a good answer. Though the query would have to be repeated. A similar solution would be to use dynamic SQL, building the GROUP BY clause basedon the input.
    Wednesday, September 02, 2009 3:00 PM
  • Brian, 
    Yes - PK_Date, Week, Quarter, Half Year, and Year are all indexed.  This is a standard Time dimension table generated by SSIS. 

    Sqlguruu, 
    Can you suggest how I can aggregiate this data WITHOUT duplicating the query three times for each interval?

    Get rid of the kabbalah "time" table and put in a proper calendar table. Your time table has attribute splitting, row splitting, invalid data elements, non iso standards, absolutely no understanding of temporal dimensions etc.

    SELECT * FROM reports AS a INNER JOIN report_calendar AS b ON a.created_date BETWEEN b.start_date AND b.end_date;
    Wednesday, September 02, 2009 3:12 PM
  • And as we have established before, your suggestion" sqlguruu" performs about 20 times slower than the other proposed.
    Wednesday, September 02, 2009 3:19 PM
  • >has attribute splitting, row splitting,

    Which, even if true, is perfectly supported by SQL Server.

    >invalid data elements,

    That is not true. The TABLE was CREATEd successfully.

    >non iso standards,

    Does Microsoft claim that SQL Server is ISO compliant?


    Wednesday, September 02, 2009 3:25 PM