Untitled

CREATE OR ALTER VIEW VW_Mat_Cost_monthly AS
SELECT
DATENAME(MONTH, CAST(CONCAT([Year],'-',[MonthOfYear], '-01') AS DATE)) AS MONTHOFYEAR,
ISNULL(SUM(CASE WHEN PROD_CODE = 5 THEN INWARDS ELSE 0 END),0) AS [5],
ISNULL(SUM(CASE WHEN PROD_CODE = 10 THEN INWARDS ELSE 0 END),0) AS [10],
ISNULL(SUM(CASE WHEN PROD_CODE = 12 THEN INWARDS ELSE 0 END),0) AS [12],
ISNULL(SUM(INWARDS),0) AS GRAND_TOTAL
FROM(
SELECT
Year,
MonthOfYear,
PROD_CODE,
StkRsPerLtr,
VolInLtr,
COALESCE(LAG(StkRsPerLtr) OVER (PARTITION BY PROD_CODE ORDER BY Year, MonthOfYear),0) AS prevmonth_stk,
(ISNULL(StkRsPerLtr,0)+ ISNULL(VolInLtr,0)) + COALESCE(LAG(StkRsPerLtr) OVER (PARTITION BY PROD_CODE ORDER BY Year, MonthOfYear),0) as INWARDS
FROM
Mat_Cost
)
AS subresult
where
PROD_CODE IN (5,10,12)
GROUP BY
Year, MonthOfYear


UNION ALL
SELECT 

'GRAND_TOTAL' AS MONTHOFYEAR,
SUM([5]),
SUM([10]),
SUM([12]),
SUM([GRAND_TOTAL])

FROM(
SELECT 

DATENAME(MONTH, CAST(CONCAT([Year],'-',[MonthOfYear], '-01') AS DATE)) AS MONTHOFYEAR,
ISNULL(SUM(CASE WHEN PROD_CODE = 5 THEN INWARDS ELSE 0 END),0) AS [5],
ISNULL(SUM(CASE WHEN PROD_CODE = 10 THEN INWARDS ELSE 0 END),0) AS [10],
ISNULL(SUM(CASE WHEN PROD_CODE = 12 THEN INWARDS ELSE 0 END),0) AS [12],
ISNULL(SUM(INWARDS),0) AS GRAND_TOTAL


FROM(
SELECT
Year,
MonthOfYear,
PROD_CODE,
StkRsPerLtr,
VolInLtr,
COALESCE(LAG(StkRsPerLtr) OVER (PARTITION BY PROD_CODE ORDER BY Year, MonthOfYear),0) AS prevmonth_stk,
(ISNULL(StkRsPerLtr,0)+ ISNULL(VolInLtr,0)) + COALESCE(LAG(StkRsPerLtr) OVER (PARTITION BY PROD_CODE ORDER BY Year, MonthOfYear),0) as INWARDS
FROM
Mat_Cost
)
AS subresult
where
PROD_CODE IN (5,10,12)
GROUP BY
Year, MonthOfYear

) AS sp;



GO