CODEWITHSUNDEEP
sqlsql-servert-sqlreportvariance
shat
shat

Reputation: 212

Calculate Variance T_SQL across Products, Price, and Dates

I have read several posts across stackoverflow trying to determine the best route to calculate the variance of a price across a time span for a table of items. My ITEM_NO, Month and Year are all within the same table as well as the Unit cost. Basically, I have some sample data here:

DECLARE    @Inventory TABLE (Month NUMERIC, Year NUMERIC, ItemNumber NUMERIC, Cost DECIMAL)

INSERT     @Inventory
SELECT     '01', '12', '0001', '1.05' 
SELECT     '02', '12', '0001', '1.05' 
SELECT     '03', '12', '0001', '1.02' 
SELECT     '02', '12', '0002', '1.25' 
SELECT     '03', '12', '0003', '1.55'
SELECT     '03', '12', '0003', '1.57'
SELECT     '03', '12', '0003', '1.61'
SELECT     '03', '12', '0002', '1.29'

Essentially, the output will be

Item Number |  Current Cost  |  % Change  |  Month  | Year
-----------------------------------------------------------
       0001 |           1.02 |       0.01 |      02 |    12

etc

If that is a good way of going about it. I am just looking for a way to calculate the variance in price. As this table has about 600,000 rows, and the item_number is repeated multiple times with different prices over time (different every month or every few months), it would be nice to generate a report on it. I am not entirely new to SQL, but a bit new to T_SQL and trying to wrap my head around this idea.

I've tried:

USE BWDW; -- database
GO
SELECT ITEM_NO AS 'Item Number', TXN_YR AS 'Year', TXN_MON AS 'Month', UNIT_COST AS 'Item Price', STDEV(UNIT_COST) AS 'Variance Price'
FROM DS_tblFactInventoryTxnHistory
GROUP BY ITEM_NO, TXN_YR, TXN_MON, UNIT_COST
ORDER BY ITEM_NO;
GO

Which gives output as:

Item Number Year    Month   Item Price  Variance Price
02009739        11  11  6180.0000   0
02009779        11  11  5459.0000   0
104415          12  8   0.0618  0
104415          12  9   0.0618  NULL
104650          11  5   1.0000  0
104650          11  7   1.0000  0
104650          11  8   0.2575  NULL
104650          11  10  0.2575  0
104650          11  12  0.0319  NULL
104650          11  12  0.1071  0
104650          11  12  0.1823  0
104650          11  12  0.2575  0
104650          12  8   0.0319  1.07539867831324E-09
104650          12  9   0.0319  1.57825377906919E-09

Which, seems like a lot of decimals for something that is ultimately dollars. I know I could round the final column up. I've been reading about STDEV on MSDN, various sources here on the site and the DATEDIFF explanation on sqlteam's site.

This is probably a completely newb-ish question and I appreciate any insight anyone can give to aid in my objective.

Upvotes: 0

Views: 4724

Answers (1)

Jodrell
Jodrell

Reputation: 35716

Don't you actually want something like this:

SELECT
            ITEM_NO ItemNumber,
            TXN_YR Year,
            TXN_MON Month,
            AVG(UNIT_COST) AveragePrice,
            VAR(UNIT_COST) PriceVarianceInMonth
    FROM
            DS_tblFactInventoryTxnHistory
    GROUP BY
            ITEM_NO,
            TXN_YR,
            TXN_MON
    ORDER BY
            ITEM_NO,
            TXN_YR,
            TXN_MON

It would provide much more meaningful data.

Essentially, the group clause is used to break the data into groups. In my query, I'm grouping the data by ITEM_NO, TXN_YR and TXN_MON. The result should have a group for every item for every month. The UNIT_COST could vary for every member of the group.

In the results I can apply aggregate functions to the group to allow me to express some property of the group. In my example, I return the average unit cost for the group, and the statistical variance of the group.

So, each row in the result set will include a row for each group including:

  • ITEM_NO
  • TXN_YR
  • TXN_MON
  • the average UNIT_COST for that month
  • the variance of the UNIT_COST for that month

Upvotes: 1

Related Questions