I just need some help modifying this script and adding a sum qty measure/attribute for each of the ref_type & trans_type combinations.
So for instance
Where trans_type = 'I' and ref_type = 'K' them Sum Qty AS '12mo Qty Issued to Projects'
Where trans_type = 'I' and ref_type = 'J' them Sum Qty AS '12mo Qty Issued to Jobs'
etc
Not sure the correct function syntax OR where to insert it
-- Gets all material transactions for the specified time, grouped by month.
, [Transactions]
AS (SELECT [matltran].[item]
, [matltran].[site_ref]
, SUM([matltran].[qty]) * -1 AS [Qty]
, DATEADD(m, DATEDIFF(m, 0, [matltran].[trans_date]), 0) AS [Month]
FROM [dbo].[matltran_mst] [matltran]
WHERE (
(
[matltran].[trans_type] = 'I'
AND [matltran].[ref_type] = 'K' -- Issued to Project
)
OR
(
[matltran].[trans_type] = 'I'
AND [matltran].[ref_type] = 'J' -- Issued to Job
)
OR
(
[matltran].[trans_type] = 'I'
AND [matltran].[ref_type] = 'S' -- Issued to Production Schedule
)
OR
(
[matltran].[trans_type] = 'S'
AND [matltran].[ref_type] = 'O' -- Shipped to Order
)
OR
(
[matltran].[trans_type] = 'T'
AND [matltran].[ref_type] = 'T' -- Transferred
AND [matltran].[qty] < 0
)
)
GROUP BY [matltran].[item]
, [matltran].[site_ref]
, DATEADD(m, DATEDIFF(m, 0, [matltran].[trans_date]), 0))
-- Joins list of item/dates to the material transactions
, [Summary]
AS (SELECT [AllItems].[Date]
, ISNULL([Transactions].[Qty], 0) AS [qty]
, [AllItems].[item]
, [AllItems].[site_ref]
, [AllItems].[abc_code]
FROM [AllItems]
LEFT OUTER JOIN [Transactions]
ON [AllItems].[Date] = [Transactions].[Month]
AND [Transactions].[item] = [AllItems].[item]
AND [Transactions].[site_ref] = [AllItems].[site_ref])
-- Calculates Runner / Repeater / Stranger across the last (TOP XX) months.
-- StdDev / Avg = CoV = (Buckets <1, 1-1.5, 1.5+)
, [RRS]
AS (SELECT [Summary].[site_ref]
, [Summary].[item]
, STDEV([Summary].[qty]) AS [StdDev]
, AVG([Summary].[qty]) AS [MonthlyAverage]
, SUM([Summary].[qty]) AS [YearlyTotal]
, CASE AVG([Summary].[qty]) WHEN 0 THEN 0 ELSE STDEV([Summary].[qty]) / AVG([Summary].[qty]) END AS [CoV]
, CASE AVG([Summary].[qty])
WHEN 0 THEN
'Stranger'
ELSE
CASE WHEN STDEV([Summary].[qty]) / AVG([Summary].[qty]) <= 1 THEN
'Runner'
WHEN STDEV([Summary].[qty]) / AVG([Summary].[qty]) > 1
AND STDEV([Summary].[qty]) / AVG([Summary].[qty]) <= 1.5 THEN
'Repeater'
ELSE
'Stranger'
END
END AS [RRS]
, [Summary].[abc_code]
FROM [Summary]
GROUP BY [Summary].[site_ref]
, [Summary].[item]
, [Summary].[abc_code])
SELECT *
INTO [#RRS]
FROM [RRS];
WITH [RRSSummary]
AS (SELECT 'Count' AS [Type]
, [#RRS].[site_ref]
, [#RRS].[abc_code]
, [#RRS].[RRS]
, COUNT(1) AS [Value]
FROM [#RRS]
GROUP BY [#RRS].[abc_code]
, [#RRS].[site_ref]
, [#RRS].[RRS]
UNION ALL
SELECT 'Value' AS [Type]
, [#RRS].[site_ref]
, [#RRS].[abc_code]
, [#RRS].[RRS]
, SUM([itemwhse].[qty_on_hand] * [item].[unit_cost]) AS [Value]
FROM [#RRS]
LEFT OUTER JOIN [dbo].[itemwhse_mst] [itemwhse]
ON [itemwhse].[item] = [#RRS].[item]
AND [itemwhse].[site_ref] = [#RRS].[site_ref]
LEFT OUTER JOIN [dbo].[item_mst] [item]
ON [item].[item] = [#RRS].[item]
AND [item].[site_ref] = [#RRS].[site_ref]
GROUP BY [#RRS].[abc_code]
, [#RRS].[site_ref]
, [#RRS].[RRS])
SELECT *
INTO [#RRSSummary]
FROM [RRSSummary];