Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: Sql with clause???

    Can any one explain to me how the SQL WITH clause works in the following query and what the UNION subqueries are meant to be doing? The results basically produce a table with total volume of enquiries for the selected week of the year and the preceding 3 weeks. It also provides the difference in volumes from the previous week as a percentage and as a number plus an average of the selected week over a 52 week period.

    I cant make head or tails of the query as I've never used a WITH clause.

    Most of you will probably be put off by the sheer size and complexity of the query though!

    Code:
    DECLARE @WEEK_OF_YEAR1 NUMERIC
    
    DECLARE @WEEK_OF_YEAR2 NUMERIC
    DECLARE @WEEK_OF_YEAR3 NUMERIC
    DECLARE @WEEK_OF_YEAR4 NUMERIC
    
    DECLARE @YEAR1 NUMERIC
    DECLARE @YEAR2 NUMERIC
    DECLARE @YEAR3 NUMERIC
    DECLARE @YEAR4 NUMERIC
    
    DECLARE @MONTH NUMERIC
    DECLARE @MAX_WEEK_OF_YEAR NUMERIC
    
    SELECT @WEEK_OF_YEAR1 = WEEK_OF_YEAR,
           @YEAR1 = YEAR
    	FROM FLODS_TIMELINE_D00
     WHERE WEEK_OF_YEAR = @CurrentWeek AND YEAR = @CurrentYear
    GROUP BY YEAR, WEEK_OF_YEAR;
    
    SELECT @MAX_WEEK_OF_YEAR = MAX(WEEK_OF_YEAR)
      FROM FLODS_TIMELINE_D00
    WHERE YEAR = @CurrentYear-1;
    
    IF @WEEK_OF_YEAR1 = 1
    	BEGIN
    		SET @WEEK_OF_YEAR2  = @MAX_WEEK_OF_YEAR - 1;
    		SET @YEAR2 = @CurrentYear-1;
    		SET @WEEK_OF_YEAR3  = @MAX_WEEK_OF_YEAR - 2;
    		SET @YEAR3 = @CurrentYear-1;
    		SET @WEEK_OF_YEAR4  = @MAX_WEEK_OF_YEAR - 3;
    		SET @YEAR4 = @CurrentYear-1;
    	END
    ELSE IF @WEEK_OF_YEAR1 = 2
    	BEGIN
    		SET @WEEK_OF_YEAR2  = 1;
    		SET @YEAR2 = @CurrentYear;
    		SET @WEEK_OF_YEAR3  = @MAX_WEEK_OF_YEAR - 1;
    		SET @YEAR3 = @CurrentYear-1;
    		SET @WEEK_OF_YEAR4  = @MAX_WEEK_OF_YEAR - 2;
    		SET @YEAR4 = @CurrentYear-1;
    	END
    ELSE IF @WEEK_OF_YEAR1 = 3
    	BEGIN
    		SET @WEEK_OF_YEAR2  = 1;
    		SET @YEAR2 = @CurrentYear;
    		SET @WEEK_OF_YEAR3  = 2;
    		SET @YEAR3 = @CurrentYear;
    		SET @WEEK_OF_YEAR4  = @MAX_WEEK_OF_YEAR - 1;
    		SET @YEAR4= @CurrentYear-1;
    	END
    ELSE
    	BEGIN
    		SET @WEEK_OF_YEAR2  = @WEEK_OF_YEAR1 - 1;
    		SET @YEAR2 = @CurrentYear;
    		SET @WEEK_OF_YEAR3  = @WEEK_OF_YEAR1 - 2;
    		SET @YEAR3 = @CurrentYear;
    		SET @WEEK_OF_YEAR4  = @WEEK_OF_YEAR1 - 3;
    		SET @YEAR4 = @CurrentYear;
    END;
    
    WITH PeriodTotalCTE AS
    (SELECT SUBJECT, 
            YEAR, 
    				WEEK_OF_YEAR AS PERIOD_ORDER_01, 
    				'Week ' + CAST(WEEK_OF_YEAR AS VARCHAR) AS PERIOD_01, 
    				count(1) AS TotalEnquiries
       FROM FLODS_ENQUIRY_F00 t1
       			INNER JOIN FLODS_ENQUIRY_D00 t2 ON t1. ENQUIRY_ID = t2.FLODS_ID AND t2.STATUS ='Enquiry Only'
       			INNER JOIN FLODS_TIMELINE_D00 t4 ON t1.TIMELINE_ID = t4.FLODS_ID
       			RIGHT OUTER JOIN FLODS_CLASSIFICATION_D00 t3 ON t1.CLASSIFICATION_ID = t3.FLODS_ID
      WHERE ((t4.YEAR = @YEAR1 AND t4.WEEK_OF_YEAR = @WEEK_OF_YEAR1) OR
    				 (t4.YEAR = @YEAR2 AND t4.WEEK_OF_YEAR = @WEEK_OF_YEAR2) OR
             (t4.YEAR = @YEAR3 AND t4.WEEK_OF_YEAR = @WEEK_OF_YEAR3) OR
             (t4.YEAR = @YEAR4 AND t4.WEEK_OF_YEAR = @WEEK_OF_YEAR4)) AND t2.STATUS='Enquiry Only'
    	GROUP BY SUBJECT,YEAR,WEEK_OF_YEAR
    	UNION 
    	SELECT il2.SUBJECT,
           	 999995 AS YEAR,
           	 999995 AS PERIOD_ORDER_01,
           	 'Variance On Prior Week' AS PERIOD_01, 
          	 sum( il2.TotalEnquiriesP1) - sum(il2.TotalEnquiriesP2) As VarianceOnPriorWeek
    		FROM (SELECT SUBJECT,
           					 WEEK_OF_YEAR AS PERIOD_ORDER_01,
           					 WEEK_OF_YEAR AS PERIOD_01,
           				 	 count(1) AS TotalEnquiriesP1,
           					 0 AS TotalEnquiriesP2
    						FROM FLODS_ENQUIRY_F00 t1
    								INNER JOIN FLODS_ENQUIRY_D00 t2 ON t1. ENQUIRY_ID = t2.FLODS_ID AND t2.STATUS ='Enquiry Only'
    								INNER JOIN FLODS_TIMELINE_D00 t4 ON t1.TIMELINE_ID = t4.FLODS_ID
    								RIGHT OUTER JOIN FLODS_CLASSIFICATION_D00 t3 ON t1.CLASSIFICATION_ID = t3.FLODS_ID
    					 WHERE (t4.YEAR = @YEAR1 AND t4.WEEK_OF_YEAR = @WEEK_OF_YEAR1) AND t2.STATUS='Enquiry Only'
    					 GROUP BY SUBJECT, WEEK_OF_YEAR
    					 UNION 
    					 SELECT SUBJECT,
           				 		WEEK_OF_YEAR AS PERIOD_ORDER_01,
           				 		WEEK_OF_YEAR AS PERIOD_01,
           				 		0 AS TotalEnquiriesP1,
           				 		count(1) AS TotalEnquiriesP2
    						 FROM FLODS_ENQUIRY_F00 t1
    									INNER JOIN FLODS_ENQUIRY_D00 t2 ON t1. ENQUIRY_ID = t2.FLODS_ID AND t2.STATUS ='Enquiry Only'
    									INNER JOIN FLODS_TIMELINE_D00 t4 ON t1.TIMELINE_ID = t4.FLODS_ID
    									RIGHT OUTER JOIN FLODS_CLASSIFICATION_D00 t3 ON t1.CLASSIFICATION_ID = t3.FLODS_ID
    						WHERE (t4.YEAR = @YEAR2 AND t4.WEEK_OF_YEAR = @WEEK_OF_YEAR2) AND t2.STATUS='Enquiry Only'
    				 		GROUP BY SUBJECT, WEEK_OF_YEAR) AS il2
    		GROUP BY  il2.SUBJECT
    		UNION  
    		SELECT il2.SUBJECT,
           		 999996 AS YEAR,
           		 999996 AS PERIOD_ORDER_01,
           		'Variance On Prior Week %' AS PERIOD_01, 
           			CASE WHEN sum(il2.TotalEnquiriesP2) = 0 THEN 0 
                 		ELSE  ((CAST (sum(il2.TotalEnquiriesP1) -  sum(il2.TotalEnquiriesP2) AS DECIMAL))  /  sum(il2.TotalEnquiriesP2))  * 100
           			END As VarianceOnPriorPeriodPerc
    			FROM (SELECT SUBJECT,
           						 WEEK_OF_YEAR AS PERIOD_ORDER_01,
           						 WEEK_OF_YEAR AS PERIOD_01,
           						 count(1) AS TotalEnquiriesP1,
           						 0 AS TotalEnquiriesP2
    							FROM FLODS_ENQUIRY_F00 t1
    										INNER JOIN FLODS_ENQUIRY_D00 t2 ON t1. ENQUIRY_ID = t2.FLODS_ID AND t2.STATUS ='Enquiry Only'
    										INNER JOIN FLODS_TIMELINE_D00 t4 ON t1.TIMELINE_ID = t4.FLODS_ID
    										RIGHT OUTER JOIN FLODS_CLASSIFICATION_D00 t3 ON t1.CLASSIFICATION_ID = t3.FLODS_ID
    							WHERE (t4.YEAR = @YEAR1 AND t4.WEEK_OF_YEAR = @WEEK_OF_YEAR1) AND t2.STATUS='Enquiry Only'
    							GROUP BY SUBJECT, WEEK_OF_YEAR
    							UNION 
    							SELECT SUBJECT,
           								WEEK_OF_YEAR AS PERIOD_ORDER_01,
           								WEEK_OF_YEAR AS PERIOD_01,
           								0 AS TotalEnquiriesP1,
           								count(1) AS TotalEnquiriesP2
    								FROM FLODS_ENQUIRY_F00 t1
    										 INNER JOIN FLODS_ENQUIRY_D00 t2 ON t1. ENQUIRY_ID = t2.FLODS_ID AND t2.STATUS ='Enquiry Only'
    										 INNER JOIN FLODS_TIMELINE_D00 t4 ON t1.TIMELINE_ID = t4.FLODS_ID
    										 RIGHT OUTER JOIN FLODS_CLASSIFICATION_D00 t3 ON t1.CLASSIFICATION_ID = t3.FLODS_ID
    								WHERE (t4.YEAR = @YEAR2 AND t4.WEEK_OF_YEAR = @WEEK_OF_YEAR2) AND t2.STATUS='Enquiry Only'
    						 GROUP BY SUBJECT, WEEK_OF_YEAR) AS il2
    			GROUP BY  il2.SUBJECT
    			UNION
    			SELECT il1.SUBJECT,
           				999998 AS YEAR,
           				999998 AS PERIOD_ORDER_01,
           				'52 Week AVG' AS PERIOD_01,
           				AVG(il1.TotalEnquiries) AS TotalEnquiries
    				FROM ( SELECT SUBJECT,
    											t4.WEEK_OF_YEAR,
    											t4.YEAR,
    											COUNT(1) AS TotalEnquiries
    								 FROM FLODS_ENQUIRY_F00 t1
    											INNER JOIN FLODS_ENQUIRY_D00 t2 ON t1. ENQUIRY_ID = t2.FLODS_ID AND t2.STATUS ='Enquiry Only'
    											INNER JOIN FLODS_TIMELINE_D00 t4 ON t1.TIMELINE_ID = t4.FLODS_ID
    											RIGHT OUTER JOIN FLODS_CLASSIFICATION_D00 t3 ON t1.CLASSIFICATION_ID = t3.FLODS_ID
    								WHERE ((t4.YEAR = @YEAR1-1 AND t4.WEEK_OF_YEAR BETWEEN @WEEK_OF_YEAR1+1 AND @MAX_WEEK_OF_YEAR)
    											OR (t4.YEAR = @YEAR1 AND t4.WEEK_OF_YEAR BETWEEN 1 AND @WEEK_OF_YEAR1)) AND t2.STATUS='Enquiry Only'
     						 GROUP BY SUBJECT, t4.YEAR, t4.WEEK_OF_YEAR ) AS il1
    			GROUP BY il1.SUBJECT
    )
    
    SELECT cte1.SUBJECT, 
           cte1.YEAR,
           cte1.PERIOD_ORDER_01,
           cte1.PERIOD_01,
           CASE WHEN cte1.TotalEnquiries IS NULL THEN 0 ELSE cte1.TotalEnquiries END AS TotalCases
    FROM  PeriodTotalCTE cte1
    ORDER BY cte1.SUBJECT, cte1.YEAR, cte1.PERIOD_ORDER_01
    any decent tutorials on this subject would be useful.

    regards

    ozzii

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Try looking up CTE (common Table Expression)
    Dave

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I cant make head or tails of the query as I've never used a WITH clause.
    In this case you can regard the WITH clause to be an alternative to using a temporary table PeriodTotalCTE, but without the hassle of defining each and every column in advance. All the WITH clause does it populating the table PeriodTotalCTE with data.

    The results basically produce a table with total volume of enquiries for the selected week of the year and the preceding 3 weeks....
    I can affirm that is what it does. Explaining to you each step in detail is a big undertake though.

    To understand what the query does, break it up in pieces and execute them separately, alter a few things here and there and see how it alters the results. Best way to understand what it does and how it works.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Another thing to note, it is better practice to use

    i.e.
    SELECT GETDATE(), DATEADD(month, -1, GETDATE())

    for any date arithmetic for adding days/weeks/years, instead of adding/subtracting straight values, if you are working with dates and date comparison.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Very true! Just look at 2008;
    Code:
    SELECT GetDate() - 1
    will fail!

    We have these functions for a reason
    George
    Home | Blog

  6. #6
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by Wim View Post
    I can affirm that is what it does. Explaining to you each step in detail is a big undertake though.

    To understand what the query does, break it up in pieces and execute them separately, alter a few things here and there and see how it alters the results. Best way to understand what it does and how it works.
    OK. But is there not an error in the following bit of code from the original query:

    Code:
    SELECT @MAX_WEEK_OF_YEAR = MAX(WEEK_OF_YEAR)
      FROM FLODS_TIMELINE_D00
    WHERE YEAR = @CurrentYear-1;
    
    IF @WEEK_OF_YEAR1 = 1
    	BEGIN
    		SET @WEEK_OF_YEAR2  = @MAX_WEEK_OF_YEAR - 1;
    		SET @YEAR2 = @CurrentYear-1;
    		SET @WEEK_OF_YEAR3  = @MAX_WEEK_OF_YEAR - 2;
    		SET @YEAR3 = @CurrentYear-1;
    		SET @WEEK_OF_YEAR4  = @MAX_WEEK_OF_YEAR - 3;
    		SET @YEAR4 = @CurrentYear-1;
    	END
    ELSE IF @WEEK_OF_YEAR1 = 2
    	BEGIN
    		SET @WEEK_OF_YEAR2  = 1;
    		SET @YEAR2 = @CurrentYear;
    		SET @WEEK_OF_YEAR3  = @MAX_WEEK_OF_YEAR - 1;
    		SET @YEAR3 = @CurrentYear-1;
    		SET @WEEK_OF_YEAR4  = @MAX_WEEK_OF_YEAR - 2;
    		SET @YEAR4 = @CurrentYear-1;
    	END
    ELSE IF @WEEK_OF_YEAR1 = 3
    	BEGIN
    		SET @WEEK_OF_YEAR2  = 1;
    		SET @YEAR2 = @CurrentYear;
    		SET @WEEK_OF_YEAR3  = 2;
    		SET @YEAR3 = @CurrentYear;
    		SET @WEEK_OF_YEAR4  = @MAX_WEEK_OF_YEAR - 1;
    		SET @YEAR4= @CurrentYear-1;
    	END
    ELSE
    	BEGIN
    		SET @WEEK_OF_YEAR2  = @WEEK_OF_YEAR1 - 1;
    		SET @YEAR2 = @CurrentYear;
    		SET @WEEK_OF_YEAR3  = @WEEK_OF_YEAR1 - 2;
    		SET @YEAR3 = @CurrentYear;
    		SET @WEEK_OF_YEAR4  = @WEEK_OF_YEAR1 - 3;
    		SET @YEAR4 = @CurrentYear;
    END;
    For example if the MAX(WEEK_OF_YEAR) from 2008 was 52 and
    if the @CurrentYear = 2009 and @WEEK_OF_YEAR1 = 1 then shouldn't @WEEK_OF_YEAR2 be set to @MAX_WEEK_OF_YEAR instead of @MAX_WEEK_OF_YEAR - 1? Otherwise in the first block of the conditional statement, @WEEK_OF_YEAR2 will be set to 51. So how can the query provide the difference in volumes from the previous week as there would be a week missing?

    The same problem with the second ELSE IF statement where @WEEK_OF_YEAR1 = 2. In this case @WEEK_OF_YEAR3 will equal 51 if @MAX_WEEK_OF_YEAR = 52. However this shouldnt pose much of a problem since we would only be comparing @WEEK_OF_YEAR1 with @WEEK_OF_YEAR2. But within the following part of the CTE wont the query be pulling data from the wrong time period if its mean to retrieve enquiry volumes from the selected week and the previous 3 weeks?

    Code:
    WITH PeriodTotalCTE AS
    (SELECT SUBJECT, 
            YEAR, 
    				WEEK_OF_YEAR AS PERIOD_ORDER_01, 
    				'Week ' + CAST(WEEK_OF_YEAR AS VARCHAR) AS PERIOD_01, 
    				count(1) AS TotalEnquiries
       FROM FLODS_ENQUIRY_F00 t1
       			INNER JOIN FLODS_ENQUIRY_D00 t2 ON t1. ENQUIRY_ID = t2.FLODS_ID AND t2.STATUS ='Enquiry Only'
       			INNER JOIN FLODS_TIMELINE_D00 t4 ON t1.TIMELINE_ID = t4.FLODS_ID
       			RIGHT OUTER JOIN FLODS_CLASSIFICATION_D00 t3 ON t1.CLASSIFICATION_ID = t3.FLODS_ID
      WHERE ((t4.YEAR = @YEAR1 AND t4.WEEK_OF_YEAR = @WEEK_OF_YEAR1) OR
    				 (t4.YEAR = @YEAR2 AND t4.WEEK_OF_YEAR = @WEEK_OF_YEAR2) OR
             (t4.YEAR = @YEAR3 AND t4.WEEK_OF_YEAR = @WEEK_OF_YEAR3) OR
             (t4.YEAR = @YEAR4 AND t4.WEEK_OF_YEAR = @WEEK_OF_YEAR4)) AND t2.STATUS='Enquiry Only'
    Finally in the ELSE IF part of the conditional statment where @WEEK_OF_YEAR1 = 3, shouldnt @WEEK_OF_YEAR2 be set to 2 and @WEEK_OF_YEAR3 set to 1?

    Have look and let me know if its me who is going loopy or if the code indeed is wrong because this query comes standard with an enterprise report!

    thanks

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    At first glance, the use of a CTE for this seems like superfluous obfuscation.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    The first time I saw the code, I noticed this:
    Code:
    SELECT @WEEK_OF_YEAR1 = WEEK_OF_YEAR,
           @YEAR1 = YEAR
    	FROM FLODS_TIMELINE_D00
     WHERE WEEK_OF_YEAR = @CurrentWeek AND YEAR = @CurrentYear
    GROUP BY YEAR, WEEK_OF_YEAR;
    Seems like redundant to me. It could easily be replaced by :
    Code:
    SET @WEEK_OF_YEAR1 = @CurrentWeek
    SET @YEAR1 = @CurrentYear
    But it is not an error.

    At first glance I think you are right about the first other flaw in the code.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Have look and let me know if its me who is going loopy or if the code indeed is wrong because this query comes standard with an enterprise report!
    You can go tell your users the reports for the first 3 weeks of the year contained an error. I came up with :
    Code:
    IF @WEEK_OF_YEAR1 = 1
    	BEGIN
    		SET @WEEK_OF_YEAR2  = @MAX_WEEK_OF_YEAR - 0;
    		SET @YEAR2 = @CurrentYear-1;
    		SET @WEEK_OF_YEAR3  = @MAX_WEEK_OF_YEAR - 1;
    		SET @YEAR3 = @CurrentYear-1;
    		SET @WEEK_OF_YEAR4  = @MAX_WEEK_OF_YEAR - 2;
    		SET @YEAR4 = @CurrentYear-1;
    	END
    ELSE IF @WEEK_OF_YEAR1 = 2
    	BEGIN
    		SET @WEEK_OF_YEAR2  = @WEEK_OF_YEAR1 - 1;
    		SET @YEAR2 = @CurrentYear;
    		SET @WEEK_OF_YEAR3  = @MAX_WEEK_OF_YEAR - 0;
    		SET @YEAR3 = @CurrentYear-1;
    		SET @WEEK_OF_YEAR4  = @MAX_WEEK_OF_YEAR - 1;
    		SET @YEAR4 = @CurrentYear-1;
    	END
    ELSE IF @WEEK_OF_YEAR1 = 3
    	BEGIN
    		SET @WEEK_OF_YEAR2  = @WEEK_OF_YEAR1 - 1;
    		SET @YEAR2 = @CurrentYear;
    		SET @WEEK_OF_YEAR3  = @WEEK_OF_YEAR1 - 2;
    		SET @YEAR3 = @CurrentYear;
    		SET @WEEK_OF_YEAR4  = @MAX_WEEK_OF_YEAR - 0;
    		SET @YEAR4= @CurrentYear-1;
    	END
    ELSE
    	BEGIN
    		SET @WEEK_OF_YEAR2  = @WEEK_OF_YEAR1 - 1;
    		SET @YEAR2 = @CurrentYear;
    		SET @WEEK_OF_YEAR3  = @WEEK_OF_YEAR1 - 2;
    		SET @YEAR3 = @CurrentYear;
    		SET @WEEK_OF_YEAR4  = @WEEK_OF_YEAR1 - 3;
    		SET @YEAR4 = @CurrentYear;
    END;
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •