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

    Unanswered: SQL Query to calculate variance?

    Hi,

    I have the following query to calculate orders grouped by month and year:

    Code:
    SELECT DATEPART(yy, orderdate) as year,
           DATEPART(mm, orderdate) as month,
           COUNT(*) as total_orders
    FROm tbl_orders
    WHERE (DATEPART(yy, orderdate) = @year and DATEPART(mm, orderdate) <= @month) or 
          (DATEPART(yy, orderdate) < @year - 1)
    GROUP BY DATEPART(yy, orderdate), DATEPART(mm, orderdate)
    How can I also calculate in the same query the variance on the prior month?

    Initially i tried to calculate this in ssrs using the Previous Function. However the Previous function is not supported in a matrix report for ssrs 2005.
    So am trying to build it into the query instead.

    Any ideas would really appreciate it

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I'm not really sure what you mean by
    the variance on the prior month
    A variance is calculated by comparing with the average value. In this case that would mean the average total_orders of the previous month??

    This code will calculate the difference with the previous month
    Code:
    WITH CTE( year_, month_, total_orders) AS
    (SELECT DATEPART(yy, orderdate) as year_,
           DATEPART(mm, orderdate) as month_,
           COUNT(*) as total_orders
    FROm tbl_orders
    WHERE (DATEPART(yy, orderdate) = @year and DATEPART(mm, orderdate) <= @month) or 
          (DATEPART(yy, orderdate) < @year - 1)
    GROUP BY DATEPART(yy, orderdate), DATEPART(mm, orderdate)
    )
    
    SELECT Present.year_, 
    	Present.month_, 
    	Present.total_orders, 
    	Present.total_orders - Previous.total_orders as Difference
    FROM CTE as Present
    	INNER JOIN CTE as Previous ON
    		Present.year_ = Previous.year_ AND
    		Present.month_ = Previous.month_ + 1
    " Present.year_ = Previous.year_ AND
    Present.month_ = Previous.month_ + 1" is of coarse not totally correct to find the previous month. You will have to tweak that a bit further in the case of January.
    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

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    if by variance you mean stdev^2, then you can easily do it because sql server does have the stdev aggregate function built in.

    STDEV (Transact-SQL)

    Variance - Wikipedia, the free encyclopedia

  4. #4
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by jezemine View Post
    if by variance you mean stdev^2, then you can easily do it because sql server does have the stdev aggregate function built in.

    STDEV (Transact-SQL)

    Variance - Wikipedia, the free encyclopedia
    By Variance I mean the difference between the previous month. any ideas as Wims solution does not seem to work?

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Wims solution does not seem to work
    It gives errors? wrong result? ....?
    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

  6. #6
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by Wim View Post
    It gives errors? wrong result? ....?
    wrong results.

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    ozzii, This is pretty much what Wim had. I just converted the Year and Month to Dates so I don't have to worry about Year differences.
    Code:
    WITH TEST_DATA (ORDERDATE)
      AS (
          SELECT GETDATE() UNION ALL
          SELECT GETDATE() UNION ALL
          SELECT DATEADD(M,-1, GETDATE()) UNION ALL
          SELECT DATEADD(M,-1, GETDATE()) UNION ALL
          SELECT DATEADD(M,-1, GETDATE()) UNION ALL
          SELECT DATEADD(M,-2, GETDATE()) UNION ALL
          SELECT DATEADD(M,-2, GETDATE()) UNION ALL
          SELECT DATEADD(M,-2, GETDATE()) UNION ALL
          SELECT DATEADD(M,-2, GETDATE()) UNION ALL
          SELECT DATEADD(M,-3, GETDATE()) UNION ALL
          SELECT DATEADD(M,-3, GETDATE()) UNION ALL
          SELECT DATEADD(M,-4, GETDATE())
         )
       , TEST_TAB (YY, MM, CNT)
      AS (
          SELECT DATEPART(YY, ORDERDATE) AS YY
               , DATEPART(MM, ORDERDATE) AS MM
               , COUNT(*)                AS CNT
          FROM TEST_DATA
          GROUP BY DATEPART(YY, ORDERDATE)
                 , DATEPART(MM, ORDERDATE)
         )
     
    SELECT CURR.YY
         , CURR.MM
         , CURR.CNT                   AS CURR_TOTAL_ORDERS
         , PREV.CNT                   AS PREV_TOTAL_ORDERS
         , CURR.CNT - PREV.CNT        AS DIFF
    FROM TEST_TAB CURR
           LEFT OUTER JOIN
           TEST_TAB PREV
             ON CAST(  CAST(CURR.YY AS CHAR(4)) 
                     + '-'
                     + CAST(CURR.MM AS CHAR(2)) 
                     + '-01' AS DATETIME)
                 =
                 DATEADD(M,1,CAST(  CAST(PREV.YY AS CHAR(4)) 
                                  + '-'
                                  + CAST(PREV.MM AS CHAR(2)) 
                                  + '-01' AS DATETIME))
    ORDER BY CURR.YY, CURR.MM
     
     
             YY          MM CURR_TOTAL_ORDERS PREV_TOTAL_ORDERS        DIFF
    ----------- ----------- ----------------- ----------------- -----------
           2010          10                 1              NULL        NULL
           2010          11                 2                 1           1
           2010          12                 4                 2           2
           2011           1                 3                 4          -1
           2011           2                 2                 3          -1
     
    (5 row(s) affected)
    The first Common table expression (CTE) just generates some data.
    The second CTE is basically your query without the Where clause.
    The query joins the table to itself by date with the one date offset by 1 month.

    I used a Left Outer Join to show the first month doesn't have have previous. Wim's Inner Join would eliminate this row.

  8. #8
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by Stealth_DBA View Post
    The first Common table expression (CTE) just generates some data.
    The second CTE is basically your query without the Where clause.
    The query joins the table to itself by date with the one date offset by 1 month.

    I used a Left Outer Join to show the first month doesn't have have previous. Wim's Inner Join would eliminate this row.
    Stealth_DBA this solution work like a charm!

Posting Permissions

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