# Thread: SQL Query to calculate variance?

1. Registered User
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. Registered User
Join Date
Nov 2004
Posts
1,428
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.

3. another indirection layer
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. Registered User
Join Date
Mar 2007
Posts
212
Originally Posted by jezemine
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. Registered User
Join Date
Nov 2004
Posts
1,428
Wims solution does not seem to work
It gives errors? wrong result? ....?

6. Registered User
Join Date
Mar 2007
Posts
212
Originally Posted by Wim
It gives errors? wrong result? ....?
wrong results.

7. Registered User
Join Date
May 2009
Posts
509
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
)
, 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)
=
+ '-'
+ 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. Registered User
Join Date
Mar 2007
Posts
212
Originally Posted by Stealth_DBA
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
•