Preppin Data Challenge - Week 8, Sql Server

Preppin Data Challenge - Week 8

-- prepare input data tables for PreppinChallenge Week 8
CREATE TABLE W8_TheftAudit(Type NVARCHAR(20), Action NVARCHAR(20), [Date] DATE, Quantity INT, Store_ID NVARCHAR(20), 
                            Crime_Ref_Number NVARCHAR(20))

INSERT INTO W8_TheftAudit
SELECT Type, Action, [Date], Quantity, Store_ID, Crime_Ref_Number FROM 
(
 SELECT  'Bar' AS Type, 'Theft' AS Action, '3/19/2019' AS Date, '10' AS Quantity, 'OX1' AS Store_ID, 'S04P1' AS Crime_Ref_Number  UNION ALL
 SELECT  'Bar' AS Type, 'Stock Adjusted' AS Action, '3/25/2019' AS Date, '-10' AS Quantity, 'OX1' AS Store_ID, 'S04P1' AS Crime_Ref_Number  UNION ALL
 SELECT  'Bar' AS Type, 'Theft' AS Action, '3/22/2019' AS Date, '5' AS Quantity, 'OX1' AS Store_ID, 'S04P2' AS Crime_Ref_Number  UNION ALL
 SELECT  'Bar' AS Type, 'Stock Adjusted' AS Action, '3/25/2019' AS Date, '-4' AS Quantity, 'OX1' AS Store_ID, 'S04P2' AS Crime_Ref_Number  UNION ALL
 SELECT  'Liquid' AS Type, 'Theft' AS Action, '3/2/2019' AS Date, '100' AS Quantity, 'WM1' AS Store_ID, 'S04P3' AS Crime_Ref_Number  UNION ALL
 SELECT  'Liquid' AS Type, 'Stock Adjusted' AS Action, '3/2/2019' AS Date, '-100' AS Quantity, 'WM1' AS Store_ID, 'S04P3' AS Crime_Ref_Number  UNION ALL
 SELECT  'Bar' AS Type, 'Theft' AS Action, '4/3/2019' AS Date, '5' AS Quantity, 'WM2' AS Store_ID, 'S04P4' AS Crime_Ref_Number  UNION ALL
 SELECT  'Luquid' AS Type, 'Theft' AS Action, '3/22/2019' AS Date, '14' AS Quantity, 'OX1' AS Store_ID, 'S04P5' AS Crime_Ref_Number  UNION ALL
 SELECT  'Liquid' AS Type, 'Theft' AS Action, '2/2/2019' AS Date, '2' AS Quantity, 'WM2' AS Store_ID, 'S04P6' AS Crime_Ref_Number  UNION ALL
 SELECT  'Liquid' AS Type, 'Stock Adjusted' AS Action, '4/3/2019' AS Date, '-2' AS Quantity, 'WM2' AS Store_ID, 'S04P6' AS Crime_Ref_Number  UNION ALL
 SELECT  'Soap Bar' AS Type, 'Theft' AS Action, '2/28/2019' AS Date, '22' AS Quantity, 'WM1' AS Store_ID, 'S04P7' AS Crime_Ref_Number  UNION ALL
 SELECT  'Soap Bar' AS Type, 'Stock Adjusted' AS Action, '3/1/2019' AS Date, '-20' AS Quantity, 'WM1' AS Store_ID, 'S04P7' AS Crime_Ref_Number  UNION ALL
 SELECT  'Liquid' AS Type, 'Theft' AS Action, '2/27/2019' AS Date, '105' AS Quantity, 'OX1' AS Store_ID, 'S04P8' AS Crime_Ref_Number  UNION ALL
 SELECT  'Liquid' AS Type, 'Stock Adjusted' AS Action, '3/1/2019' AS Date, '-105' AS Quantity, 'OX1' AS Store_ID, 'S04P8' AS Crime_Ref_Number
) As t1

CREATE TABLE W8_BranchID(BranchID NVARCHAR(20))

INSERT INTO W8_BranchID(BranchID) VALUES ('OX1 - Oxford Street');
INSERT INTO W8_BranchID(BranchID) VALUES ('WM1 - Wimbledon 1');
INSERT INTO W8_BranchID(BranchID) VALUES ('WM2 - Wimbledon 2');
INSERT INTO W8_BranchID(BranchID) VALUES ('ST1 - Stratford');

--SELECT LEFT(BranchID,3) As B_ID, SUBSTRING(BranchID,7,LEN(BranchID)-5) As BranchName FROM W8_BranchID

SELECT m1.BranchName, l1.Crime_Ref_Number, LTRIM(REPLACE(l1.Type, 'Soap','')) As Type, l1.Store_ID, l1.Theft_Qty, l1.Adjusted_Qty, r1.Theft_Date, r1.Adjusted_Date,
       (l1.Theft_Qty + ISNULL(l1.Adjusted_Qty,0)) As [Stock_Variance], 
       DATEDIFF(day, r1.Theft_Date, r1.Adjusted_Date) AS [Days_to_complete_adjustment]
FROM
(
    SELECT Crime_Ref_Number, Type, Store_ID, [Theft] As Theft_Qty, [Stock Adjusted] AS Adjusted_Qty  FROM 
    (
       SELECT Crime_Ref_Number, Type, Action, Quantity, Store_ID FROM W8_TheftAudit
    ) As src
    PIVOT 
    ( SUM(Quantity) FOR Action IN ([Theft],[Stock Adjusted] ) ) As pvt
 ) As l1 
 INNER JOIN 
 (
    SELECT Crime_Ref_Number, Type, Store_ID, [Theft] As Theft_Date, [Stock Adjusted] AS Adjusted_Date FROM 
    (
       SELECT Crime_Ref_Number, Type, Action, Date, Store_ID FROM W8_TheftAudit
    ) As src
    PIVOT 
    ( MIN([Date]) FOR Action IN ([Theft],[Stock Adjusted] ) ) As pvt
 ) As r1 ON l1.Crime_Ref_Number = r1.Crime_Ref_Number   
 INNER JOIN 
 ( 
     SELECT LEFT(BranchID,3) As B_ID, SUBSTRING(BranchID,7,LEN(BranchID)-5) As BranchName FROM W8_BranchID 
 ) As m1 ON l1.Store_ID = m1.B_ID


run \| edit \| history \| help	0