Tydzień7_zad2

--CREATE TABLE [dbo].[SalesAggregated_Materialized](
--  [ProductName] [varchar](20) NULL,
--  [QtySum] [decimal](18, 6) NULL,
--  [QtyAvg] [decimal](18, 6) NULL,
--  [TotalPriceSum] [decimal](18, 6) NULL,
--  [TotalPriceAvg] [decimal](18, 6) NULL,
--  [Ct] [int] NULL,
-- CONSTRAINT [IX_SalesAggregated_Materialized_Product_Uniqe] UNIQUE CLUSTERED
--(
--  [ProductName] ASC
--)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
--) ON [PRIMARY]
--GO


SELECT * FROM SalesAggregated_Materialized



-- TRUNCATE TABLE dbo.SalesAggregated_Materialized

DECLARE
    @TmpValue INT =0,
    @ProductName NVARCHAR(100) = N''

MERGE INTO SalesAggregated_Materialized as [t]
USING
(
    SELECT
        P.ProductName,
        SUM(Qty) + @TmpValue as qty_sum,
        AVG(Qty) as qty_avg,
        SUM(TotalPrice) as totalprice_sum,
        AVG(TotalPrice) as totalprice_avg,
        COUNT(*) as Ct
    FROM
        [dbo].[SalesOrderDetail] SO
        JOIN [dbo].[product] P ON SO.ProductId = P.ProductId
    WHERE
        @ProductName = N''  OR P.ProductName <> @ProductName
    GROUP BY
        P.ProductName
) AS [s]
ON [t].ProductName = [s].ProductName
WHEN MATCHED
    AND
    (
        t.QtySum <> s.qty_sum
        OR t.QtyAvg <> s.qty_avg
        OR t.TotalPriceSum <> s.totalprice_sum
        OR t.TotalPriceAvg <> s.totalprice_avg
        OR t.Ct <> s.Ct
    )
THEN
   UPDATE SET
     t.QtySum = s.qty_sum
     ,t.QtyAvg = s.qty_avg
     ,t.TotalPriceSum = s.totalprice_sum
     ,t.TotalPriceAvg = s.totalprice_avg
     ,t.Ct = s.Ct
WHEN NOT MATCHED THEN
    INSERT (ProductName, QtySum, QtyAvg, TotalPriceSum, TotalPriceAvg, Ct)
    VALUES ([s].ProductName, [s].qty_sum, [s].qty_avg, [s].totalprice_sum, [s].totalprice_avg, [s].Ct)
WHEN NOT MATCHED BY SOURCE THEN --DELETE
    UPDATE SET
        t.QtySum = 0
        ,t.QtyAvg = 0
        ,t.TotalPriceSum = 0
        ,t.TotalPriceAvg = 0
        ,t.Ct = 0
OUTPUT $action,
DELETED.QtySum AS TargetQtySum,
DELETED.QtyAvg AS TargetQtyAvg,
DELETED.TotalPriceSum AS TargetTotalPriceSum,
DELETED.TotalPriceAvg AS TargetTotalPriceAvg,
DELETED.Ct AS TargetCt,
INSERTED.QtySum AS SourceQtySum,
INSERTED.QtyAvg AS SourceQtyAvg,
INSERTED.TotalPriceSum AS SourceTotalPriceSum,
INSERTED.TotalPriceAvg AS SourceTotalPriceAvg,
INSERTED.Ct AS SourceCt,
DELETED.ProductName AS TargetProductName,
INSERTED.ProductName AS SourceProductName;