The example directly below provides automated reporting and visualization data for a major grocery client
DECLARE ProjectID INT;
SET ProjectID = 1266;
DECLARE MyDate VARCHAR(50);
SELECT MyDate = 7;
DECLARE MyDateTwo VARCHAR(50);
SELECT MyDateTwo = 7;
DROP TABLE [TestMattDB].[dbo].[TestWeeklyDataSet];
DROP TABLE #EXCELDATA;
--==============================
--BILLABLE HOURS: 14 hours
--==============================
--============================================
--- NOTE! 1265 Starts on Jan 21. There is no expected SKU List for 1265
--======================
/*
Jan-19 Feb-19 Mar-19 Apr-19
Project # 1264 1265 1266 1267
Dec 31-Jan 18 Jan 21 - Mar 1 Mar 4 - 29 Apr 1 - 26
*/
--update columns 60 and 95
--update columns 596 + 598/ 893 + 926 for each new project number
--make sure sku list is for current project number => line 681
--Code For NullIF Div 0 errror
--NULLIF(COALESCE((SELECT COUNT(ers.[sample_brand]) FROM
--[NorwalkMySQLDiageoData].[dbo].[event_recap_samples] ers
--WHERE ers.event_recap_id = EVENT_RECAP_SAMPLES.event_recap_id), 1),0)
SELECT *
INTO #EXCELDATA FROM
(
SELECT
[Visit ID]
,[REASON FOR YOUR VISIT]
--,[OOS ON ENTRY] There is no OOS ON ENTRY
,[GENERIC YOGURT OOS: STATUS: # OOS ON ENTRY]
,[GENERIC YOGURT OOS: STATUS: # OOS ON EXIT]
,[GENERIC YOGURT OOS: STATUS: #SKUS]
,[DISPLAY: DISPLAY: NUMBER OF Matt POINTS OF INTERRUPTION (DISPL] 'NUMBER OF Matt POINTS OF INTERRUPTION (DISPLAY)'
,[DISPLAY: DISPLAY: IS THE CORPORATE DISPLAY IN PLACE? _EST-CE QU] 'IS THE CORPORATE DISPLAY IN PLACE?'
,[DISPLAY: DISPLAY: IS THERE SUPPOSED TO BE A CORPORATE DISPLAY AS] 'IS THERE SUPPOSED TO BE A CORPORATE DISPLAY AS PER THE RAP'
,[DISPLAY: DISPLAY: IS THERE A Matt DISPLAY TO SUPPORT THE FRONT] 'IS THERE A Matt DISPLAY TO SUPPORT THE FRONT/BACK?'
,[DISPLAY: DISPLAY: IS THERE A Matt FRONT/BACK PAGE AD (refer t] 'IS THERE A Matt FRONT/BACK PAGE AD'
,[DISPLAY: DISPLAY: IS THERE DISPLAY SPACE AVAILABLE FOR REFRIGERA] 'IS THERE DISPLAY SPACE AVAILABLE FOR REFRIGERATED'
,[Do you have a product superiority issue in this store?] 'Product Superiority'
,[PB REG PRICING CONSISTENCY]
,[ADDITIONAL PRODUCT SUP]
,[YOGURT REG PRICING CONSISTENCY]
,[Other comments] 'Comments POS'
,[1# Parent Level 1]
,[2# Parent Level 2]
,[3# Parent Level 3]
,[Address]
,[City]
,[State/ Province]
,[Location ID]
,[Response Date]
,[Timekeeping Start Date]
,[Timekeeping Start Time]
,[Timekeeping End Date]
,[Timekeeping End Time]
,[Total Work]
,[Staff Manager 1: Last Name]
,[Staff Manager 1: First Name]
,[Staff Number]
,[First Name]
,[Last Name]
--New Columns For 1266
,[SILK DISPLAY: DISPLAY: IS THERE A SILK FRONT/BACK PAGE AD (refer] 'IS THERE A SILK FRONT/BACK PAGE AD?'
,[SILK DISPLAY: DISPLAY: IS THERE A SILK DISPLAY TO SUPPORT THE FR] 'IS THERE A SILK DISPLAY TO SUPPORT THE FRONT/BACK?'
,[SILK DISPLAY: DISPLAY: IS THERE SUPPOSED TO BE A SILK CORPORATE ] 'IS THERE SUPPOSED TO BE A SILK CORPORATE DISPLAY AS PER THE RAP'
,[SILK DISPLAY: DISPLAY: IS THE SILK CORPORATE DISPLAY IN PLACE? ] 'IS THE SILK CORPORATE DISPLAY IN PLACE?'
FROM [TestMattDB].[dbo].[MattMarch7]
UNION ALL
SELECT
[Visit ID]
,[REASON FOR YOUR VISIT]
--,[OOS ON ENTRY] There is no OOS ON ENTRY --CONVERGE PORTION CHANGED TO DANETTE
,[DANETTE: DANETTE: # FACINGS ENTRY]
,[DANETTE: DANETTE: # FACINGS EXIT]
,[DANETTE: DANETTE: # SKUS]
,[DISPLAY: DISPLAY: NUMBER OF Matt POINTS OF INTERRUPTION (DISPL] 'NUMBER OF Matt POINTS OF INTERRUPTION (DISPLAY)'
,[DISPLAY: DISPLAY: IS THE CORPORATE DISPLAY IN PLACE? _EST-CE QU] 'IS THE CORPORATE DISPLAY IN PLACE?'
,[DISPLAY: DISPLAY: IS THERE SUPPOSED TO BE A CORPORATE DISPLAY AS] 'IS THERE SUPPOSED TO BE A CORPORATE DISPLAY AS PER THE RAP'
,[DISPLAY: DISPLAY: IS THERE A Matt DISPLAY TO SUPPORT THE FRONT] 'IS THERE A Matt DISPLAY TO SUPPORT THE FRONT/BACK?'
,[DISPLAY: DISPLAY: IS THERE A Matt FRONT/BACK PAGE AD (refer t] 'IS THERE A Matt FRONT/BACK PAGE AD'
,[DISPLAY: DISPLAY: IS THERE DISPLAY SPACE AVAILABLE FOR REFRIGERA] 'IS THERE DISPLAY SPACE AVAILABLE FOR REFRIGERATED'
,[Do you have a product superiority issue in this store?] 'Product Superiority'
,[PB REG PRICING CONSISTENCY]
,[ADDITIONAL PRODUCT SUP]
,[YOGURT REG PRICING CONSISTENCY]
,[Other comments] 'Comments POS'
,[1# Parent Level 1]
,[2# Parent Level 2]
,[3# Parent Level 3]
,[Address]
,[City]
,[State/ Province]
,[Location ID]
,[Response Date]
,[Timekeeping Start Date]
,[Timekeeping Start Time]
,[Timekeeping End Date]
,[Timekeeping End Time]
,[Total Work]
,[Staff Manager 1: Last Name]
,[Staff Manager 1: First Name]
,[Staff Number]
,[First Name]
,[Last Name]
--New Columns For 1266
,[SILK DISPLAY: DISPLAY: IS THERE A SILK FRONT/BACK PAGE AD (refer] 'IS THERE A SILK FRONT/BACK PAGE AD?'
,[SILK DISPLAY: DISPLAY: IS THERE A SILK DISPLAY TO SUPPORT THE FR] 'IS THERE A SILK DISPLAY TO SUPPORT THE FRONT/BACK?'
,[SILK DISPLAY: DISPLAY: IS THERE SUPPOSED TO BE A SILK CORPORATE ] 'IS THERE SUPPOSED TO BE A SILK CORPORATE DISPLAY AS PER THE RAP'
,[SILK DISPLAY: DISPLAY: IS THE SILK CORPORATE DISPLAY IN PLACE? ] 'IS THE SILK CORPORATE DISPLAY IN PLACE?'
FROM [TestMattDB].[dbo].[ConvergeMarch7]
) AS Z1;
WITH CteOne AS
(
SELECT CONCAT('MMG06--', t2.VisitId) AS 'Unique ID', 'Matt' AS Region, t2.VisitId,
t11.[Start], t11.[End]
,t1.columnTitle, t1.rowTitle, ISNULL(t3.Val,'') AS SurveyAnswer, t2.ResponseDate,
t5.ProjectNumber,t5.VisitType, t9.ManagerLevel1LastName, t9.ManagerLevel2LastName,
t9.FirstName, t9.LastName, t9.StaffNumber,t5.ManagerRoleTitle, t5.LocationManagerId, t6.LocationID, t8.Banner, t7.[Address], t7.City, t7.StateProvince AS 'Province',
t7.LocationGroupTitle1, t7.LocationGroupTitle2, t7.LocationGroupTitle3
FROM [MattPortal-Matt-MMG06]..gridQuestion t1 (NOLOCK)
LEFT JOIN [MattPortal-Matt-MMG06]..SurveyResponse t2 (NOLOCK) ON (t2.SurveyId = t1.SurveyId)
LEFT JOIN [MattPortal-Matt-MMG06]..SurveyResponseData t3 (NOLOCK) ON (t3.ResponseId = t2.ResponseId AND t3.[Var] = t1.VariableName)
LEFT JOIN [MattPortal-Matt-MMG06].[dbo].[vwSurveyQuestion] t4 on t4.SurveyID = t1.SurveyId
LEFT JOIN [MattPortal-Matt-MMG06]..Visit t5 (NOLOCK) ON (t5.Id = t2.VisitId)
LEFT JOIN [MattPortal-Matt-MMG06]..tblStoreSKU t6 (NOLOCK) ON (t6.Title = REPLACE(t1.rowTitle, ' ', ' ') AND t6.LocationID = t5.LocationId)
LEFT JOIN [MattPortal-Matt-MMG06].[dbo].[Location] t7 ON t7.Id = t6.LocationID
LEFT JOIN [MattPortal-Matt-MMG06].[dbo].vwLocation t8 ON t8.[Address] = t7.[Address]
LEFT JOIN [MattPortal-Matt-MMG06].[dbo].[Staff] t9 ON t9.StaffNumber = t5.StaffNumber
LEFT JOIN [MattPortal-Matt-MMG06].[dbo].[TimekeepingResponse] t11 ON t11.VisitId = t2.VisitId
LEFT JOIN [MattPortal-Matt].[dbo].[vwMerge_Visit] t12 (NOLOCK) ON (t12.Id = t5.Id)
WHERE (t5.ProjectNumber IN (ProjectID)) --AND t11.[Start] >= '2018-07-02 00:00:00.000' AND t11.[End] < '2018-07-06 11:59:00.000'
--AND t3.Val IN ('1','2','3','4','5','6','7','8','9','10','-1','-2','-3','-4','-5','-6','-7','-8','-9','-10','Yes','No')
AND t1.rowTitle LIKE '5%'
--AND t6.SKU LIKE '5%'
AND t1.[columnTitle] IN
('FACING(S) VARIATION'
,'INCREMENTAL SKUS ( + )'
,'MISSING DISTRIBUTION ( - )'
,'OOS ON EXIT (2 or less)'
,'OOS ON ENTRY')
UNION ALL
SELECT CONCAT('MMG06--', t2.VisitId) AS 'Unique ID', 'Matt' AS Region, t2.VisitId,
t11.[Start], t11.[End]
,t1.columnTitle, t1.rowTitle, ISNULL(t3.Val,'') AS SurveyAnswer, t2.ResponseDate,
t5.ProjectNumber,t5.VisitType, t9.ManagerLevel1LastName, t9.ManagerLevel2LastName,
t9.FirstName, t9.LastName, t9.StaffNumber,t5.ManagerRoleTitle, t5.LocationManagerId, t6.LocationID, t8.Banner, t7.[Address], t7.City, t7.StateProvince AS 'Province',
t7.LocationGroupTitle1, t7.LocationGroupTitle2, t7.LocationGroupTitle3
FROM [MattPortal-Matt-MMG06]..gridQuestion t1 (NOLOCK)
LEFT JOIN [MattPortal-Matt-MMG06]..SurveyResponse t2 (NOLOCK) ON (t2.SurveyId = t1.SurveyId)
LEFT JOIN [MattPortal-Matt-MMG06]..SurveyResponseData t3 (NOLOCK) ON (t3.ResponseId = t2.ResponseId AND t3.[Var] = t1.VariableName)
LEFT JOIN [MattPortal-Matt-MMG06].[dbo].[vwSurveyQuestion] t4 on t4.SurveyID = t1.SurveyId
LEFT JOIN [MattPortal-Matt-MMG06]..Visit t5 (NOLOCK) ON (t5.Id = t2.VisitId)
LEFT JOIN [MattPortal-Matt-MMG06]..tblStoreSKU t6 (NOLOCK) ON (t6.Title = REPLACE(t1.rowTitle, ' ', ' ') AND t6.LocationID = t5.LocationId)
LEFT JOIN [MattPortal-Matt-MMG06].[dbo].[Location] t7 ON t7.Id = t6.LocationID
LEFT JOIN [MattPortal-Matt-MMG06].[dbo].vwLocation t8 ON t8.[Address] = t7.[Address]
LEFT JOIN [MattPortal-Matt-MMG06].[dbo].[Staff] t9 ON t9.StaffNumber = t5.StaffNumber
LEFT JOIN [MattPortal-Matt-MMG06].[dbo].[TimekeepingResponse] t11 ON t11.VisitId = t2.VisitId
LEFT JOIN [MattPortal-Matt].[dbo].[vwMerge_Visit] t12 (NOLOCK) ON (t12.Id = t5.Id)
WHERE (t12.ProjectNumber IN (ProjectID)) --AND t11.[Start] >= '2018-07-02 00:00:00.000' AND t11.[End] < '2018-07-06 11:59:00.000'
--AND t3.Val IN ('1','2','3','4','5','6','7','8','9','10','-1','-2','-3','-4','-5','-6','-7','-8','-9','-10','Yes','No')
AND t1.rowTitle LIKE '5%'
--AND t6.SKU LIKE '5%'
AND t1.[columnTitle] IN
('FACING(S) VARIATION'
,'INCREMENTAL SKUS ( + )'
,'MISSING DISTRIBUTION ( - )'
,'OOS ON EXIT (2 or less)'
,'OOS ON ENTRY')
UNION ALL
SELECT CONCAT('Matt--', t2.VisitId) AS 'Unique ID', 'Matt' AS Region, t2.VisitId,
t11.[Start], t11.[End]
,t1.columnTitle, t1.rowTitle, ISNULL(t3.Val,'') AS SurveyAnswer, t2.ResponseDate,
t5.ProjectNumber,t5.VisitType, t9.ManagerLevel1LastName, t9.ManagerLevel2LastName,
t9.FirstName, t9.LastName, t9.StaffNumber,t5.ManagerRoleTitle, t5.LocationManagerId, t6.LocationID, t8.Banner, t7.[Address], t7.City, t7.StateProvince AS 'Province',
t7.LocationGroupTitle1, t7.LocationGroupTitle2, t7.LocationGroupTitle3
FROM [MattPortal-Matt]..gridQuestion t1 (NOLOCK)
LEFT JOIN [MattPortal-Matt]..SurveyResponse t2 (NOLOCK) ON (t2.SurveyId = t1.SurveyId)
LEFT JOIN [MattPortal-Matt]..SurveyResponseData t3 (NOLOCK) ON (t3.ResponseId = t2.ResponseId AND t3.[Var] = t1.VariableName)
LEFT JOIN [MattPortal-Matt].[dbo].[vwSurveyQuestion] t4 on t4.SurveyID = t1.SurveyId
LEFT JOIN [MattPortal-Matt]..Visit t5 (NOLOCK) ON (t5.Id = t2.VisitId)
LEFT JOIN [MattPortal-Matt-MMG06]..tblStoreSKU t6 (NOLOCK) ON (t6.Title = REPLACE(t1.rowTitle, ' ', ' ') AND t6.LocationID = t5.LocationId)
LEFT JOIN [MattPortal-Matt].[dbo].[Location] t7 ON t7.Id = t6.LocationID
LEFT JOIN [MattPortal-Matt].[dbo].vwLocation t8 ON t8.[Address] = t7.[Address]
LEFT JOIN [MattPortal-Matt].[dbo].[Staff] t9 ON t9.StaffNumber = t5.StaffNumber
LEFT JOIN [MattPortal-Matt].[dbo].[TimekeepingResponse] t11 ON t11.VisitId = t2.VisitId
LEFT JOIN [MattPortal-Matt].[dbo].[vwMerge_Visit] t12 (NOLOCK) ON (t12.Id = t5.Id)
WHERE (t5.ProjectNumber IN (ProjectID))--AND t11.[Start] >= '2018-07-02 00:00:00.000' AND t11.[End] < '2018-07-06 11:59:00.000'
--AND t3.Val IN ('1','2','3','4','5','6','7','8','9','10','-1','-2','-3','-4','-5','-6','-7','-8','-9','-10','Yes','No')
AND t1.rowTitle LIKE '5%'
--AND t6.SKU LIKE '5%'
AND t1.[columnTitle] IN
('FACING(S) VARIATION'
,'INCREMENTAL SKUS ( + )'
,'MISSING DISTRIBUTION ( - )'
,'OOS ON EXIT (2 or less)'
,'OOS ON ENTRY')
UNION ALL
SELECT CONCAT('Matt--', t2.VisitId) AS 'Unique ID', 'Matt' AS Region, t2.VisitId,
t11.[Start], t11.[End]
,t1.columnTitle, t1.rowTitle, ISNULL(t3.Val,'') AS SurveyAnswer, t2.ResponseDate,
t5.ProjectNumber,t5.VisitType, t9.ManagerLevel1LastName, t9.ManagerLevel2LastName,
t9.FirstName, t9.LastName, t9.StaffNumber,t5.ManagerRoleTitle, t5.LocationManagerId, t6.LocationID, t8.Banner, t7.[Address], t7.City, t7.StateProvince AS 'Province',
t7.LocationGroupTitle1, t7.LocationGroupTitle2, t7.LocationGroupTitle3
FROM [MattPortal-Matt]..gridQuestion t1 (NOLOCK)
LEFT JOIN [MattPortal-Matt]..SurveyResponse t2 (NOLOCK) ON (t2.SurveyId = t1.SurveyId)
LEFT JOIN [MattPortal-Matt]..SurveyResponseData t3 (NOLOCK) ON (t3.ResponseId = t2.ResponseId AND t3.[Var] = t1.VariableName)
LEFT JOIN [MattPortal-Matt].[dbo].[vwSurveyQuestion] t4 on t4.SurveyID = t1.SurveyId
LEFT JOIN [MattPortal-Matt]..Visit t5 (NOLOCK) ON (t5.Id = t2.VisitId)
LEFT JOIN [MattPortal-Matt-MMG06]..tblStoreSKU t6 (NOLOCK) ON (t6.Title = REPLACE(t1.rowTitle, ' ', ' ') AND t6.LocationID = t5.LocationId)
LEFT JOIN [MattPortal-Matt].[dbo].[Location] t7 ON t7.Id = t6.LocationID
LEFT JOIN [MattPortal-Matt].[dbo].vwLocation t8 ON t8.[Address] = t7.[Address]
LEFT JOIN [MattPortal-Matt].[dbo].[Staff] t9 ON t9.StaffNumber = t5.StaffNumber
LEFT JOIN [MattPortal-Matt].[dbo].[TimekeepingResponse] t11 ON t11.VisitId = t2.VisitId
LEFT JOIN [MattPortal-Matt].[dbo].[vwMerge_Visit] t12 (NOLOCK) ON (t12.Id = t5.Id)
WHERE (t12.ProjectNumber IN (ProjectID))--AND t11.[Start] >= '2018-07-02 00:00:00.000' AND t11.[End] < '2018-07-06 11:59:00.000'
--AND t3.Val IN ('1','2','3','4','5','6','7','8','9','10','-1','-2','-3','-4','-5','-6','-7','-8','-9','-10','Yes','No')
AND t1.rowTitle LIKE '5%'
--AND t6.SKU LIKE '5%'
AND t1.[columnTitle] IN
('FACING(S) VARIATION'
,'INCREMENTAL SKUS ( + )'
,'MISSING DISTRIBUTION ( - )'
,'OOS ON EXIT (2 or less)'
,'OOS ON ENTRY')
),
CteTwo AS
(
SELECT [Unique ID], [Region], [VisitId], [rowTitle], [ProjectNumber], [VisitType], [Start],
[End],[ResponseDate], ManagerLevel1LastName, ManagerLevel2LastName, FirstName,
LastName, StaffNumber, ManagerRoleTitle, LocationManagerId, LocationID,Banner, [Address], City, Province,
[LocationGroupTitle1],[LocationGroupTitle2],[LocationGroupTitle3],
MAX(CASE WHEN [columnTitle] = 'FACING(S) VARIATION' THEN [SurveyAnswer] ELSE NULL END) AS 'FACING(S) VARIATION',
MAX(CASE WHEN [columnTitle] = 'INCREMENTAL SKUS ( + )' THEN [SurveyAnswer] ELSE NULL END) AS 'INCREMENTAL SKUS ( + )',
MAX(CASE WHEN [columnTitle] = 'MISSING DISTRIBUTION ( - )' THEN [SurveyAnswer] ELSE NULL END) AS 'MISSING DISTRIBUTION ( - )',
MAX(CASE WHEN [columnTitle] = 'OOS ON EXIT (2 or less)' THEN [SurveyAnswer] ELSE NULL END) AS 'OOS ON EXIT (2 or less)'
FROM CteOne
GROUP BY [Unique ID], [Region], [VisitId], [rowTitle], [ProjectNumber], [VisitType], [Start],
[End], [ResponseDate], ManagerLevel1LastName, ManagerLevel2LastName, FirstName,
LastName, StaffNumber, ManagerRoleTitle, LocationManagerId, LocationID,Banner, [Address], City, Province,
[LocationGroupTitle1],[LocationGroupTitle2],[LocationGroupTitle3]
)
, CteThree AS
(
SELECT [Unique ID], [rowTitle], --LEFT([rowTitle], 11) AS 'Sku', RIGHT([rowTitle], 2) AS 'Number of Displays',
[Region], [VisitId], [ProjectNumber], [VisitType], [Start],
[End], DATEDIFF(MINUTE, [Start], [End]) AS 'Duration Of Visit',
[ResponseDate], ManagerLevel1LastName, ManagerLevel2LastName, FirstName,
LastName, StaffNumber, ManagerRoleTitle, LocationManagerId, LocationID,Banner, [Address], City, Province,
[LocationGroupTitle1],[LocationGroupTitle2],[LocationGroupTitle3], [FACING(S) VARIATION], [INCREMENTAL SKUS ( + )], [MISSING DISTRIBUTION ( - )], [OOS ON EXIT (2 or less)]
FROM CteTwo
)
, CteFour AS
(
SELECT [Unique ID], [rowTitle] AS 'Description', --LEFT([Number of Displays], 1) AS 'Number Of Displays', LEFT([rowTitle], 11) AS 'Sku'
[VisitId], [VisitType], [ProjectNumber] AS 'Project ID', CONVERT(VARCHAR(12), [Start], 100) AS 'Start Date' , [Start] AS 'Start Time',
CONVERT(VARCHAR(12), [End], 100) AS 'End Date', [End] AS 'End Time', DATEDIFF(MINUTE, [Start], [End]) AS 'Duration Of Visit',
[ResponseDate] AS 'Response Date/Time', [ManagerLevel1LastName], [ManagerLevel2LastName], [StaffNumber], [FirstName], [LastName],
Banner, [LocationGroupTitle1],[LocationGroupTitle2],[LocationGroupTitle3]
, [LocationID], [Address], [City], [Province], [FACING(S) VARIATION], [INCREMENTAL SKUS ( + )], [MISSING DISTRIBUTION ( - )], [OOS ON EXIT (2 or less)]
FROM CteThree
)
, CteFive AS
(
SELECT [Unique ID], --REPLACE( [Description], RIGHT([Description], 3), '' ) AS 'Description', [Number of Displays], [Sku],
[VisitId], [VisitType], [Project ID],[Start Date], [Start Time],
[End Date], [End Time], [Duration Of Visit],
[Response Date/Time], [ManagerLevel1LastName], [ManagerLevel2LastName], [StaffNumber], [FirstName], [LastName], Banner,
[LocationGroupTitle1],[LocationGroupTitle2],[LocationGroupTitle3], [LocationID], [Address], [City], [Province], [FACING(S) VARIATION],
[INCREMENTAL SKUS ( + )], [MISSING DISTRIBUTION ( - )], [OOS ON EXIT (2 or less)]
FROM CteFour
)
, CteSix AS
(
-- 6
SELECT
[Unique ID], -- [Sku], REPLACE( [Description], LEFT([Description], 12), '' ) AS 'Description', [Number of Displays],
[VisitId], [VisitType], [Project ID], [Start Date], [Start Time],
[End Date], [End Time], [Duration Of Visit],
[Response Date/Time], [ManagerLevel1LastName], [ManagerLevel2LastName], [StaffNumber], [FirstName], [LastName], Banner,
[LocationGroupTitle1],[LocationGroupTitle2],[LocationGroupTitle3], [LocationID], [Address], [City], [Province], [FACING(S) VARIATION],
[INCREMENTAL SKUS ( + )], [MISSING DISTRIBUTION ( - )], [OOS ON EXIT (2 or less)]
FROM CteFive
)
, CteSeven AS
(
SELECT
--CONVERT(BIGINT, [SKU]) 'SKU'
-- [SKU]
-- ,LTRIM([Description]) 'Description'
-- ,[Number of Displays]
[Project ID]
,[Banner]
,[VisitId] 'VisitID'
,[VisitType] 'Reason For Visit'
,CONVERT(DATE,[Start Date]) 'Start Date'
,CONVERT(DATETIME, [Start Time]) 'Start Time'
,CONVERT(DATE,[End Date]) 'End Date'
,[End Time]
,[Duration Of Visit]
,[Response Date/Time]
,[ManagerLevel1LastName] 'Manager FirstName'
,CONVERT(INT,[StaffNumber]) 'Staff Number'
,[FirstName] 'Staff FirstName'
,[LastName] 'Staff LastName'
-- ,[LocationGroupTitle1] 'Banner level 1'
-- ,[LocationGroupTitle2] 'Banner level 2'
-- ,[LocationGroupTitle3] 'Banner level 3'
,[LocationID]
,[Address]
,[City]
,[Province]
-- ,CONVERT(INT,[OOS ON ENTRY]) 'OOS On Entry'
,[OOS ON EXIT (2 or less)] 'OSS On Exit'
,[FACING(S) VARIATION] 'Facing (s) Variation'
,[INCREMENTAL SKUS ( + )] 'Incremental SKUs'
,[MISSING DISTRIBUTION ( - )] 'Missing Distribution'
,[Unique ID]
FROM CteSix
)
, CteEight AS
(
SELECT DISTINCT
t1.[Unique ID]
-- ,t1.[Sku]
-- ,t1.[Description]
-- ,t1.[Number of Displays]
,t1.[VisitId]
,t2.[REASON FOR YOUR VISIT] 'Reason For Visit'
,t1.[Project ID]
,t2.[Timekeeping Start Date] 'Start Date'
,CONVERT(DATETIME, CONCAT(t2.[Timekeeping Start Date], ' '
,t2.[Timekeeping Start Time])) 'Start Time'
,t2.[Timekeeping End Date] 'End Date'
,CONVERT(DATETIME, CONCAT(t2.[Timekeeping End Date], ' '
,t2.[Timekeeping End Time])) 'End Time'
,t2.[Total Work] 'Duration of Visit'
,CONCAT(t2.[Staff Manager 1: Last Name], ', ', t2.[Staff Manager 1: First Name]) 'Manager FirstName'
,t2.[Response Date] 'Response Date/time'
,t2.[Staff Number]
,t2.[First Name] 'Staff FirstName'
,t2.[Last Name] 'Staff LastName'
,t1.[Banner]
,t2.[Location ID] 'LocationID'
,t2.[1# Parent Level 1] 'Banner level 1'
,t2.[2# Parent Level 2] 'Banner Level 2'
,t2.[3# Parent Level 3] 'Banner Level 3'
,t2.[Address]
,t2.[City]
,t2.[State/ Province] 'Province'
,t1.[Facing (s) Variation]
,t1.[Incremental SKUs]
,t1.[Missing Distribution]
,t2.[GENERIC YOGURT OOS: STATUS: # OOS ON EXIT] 'OSS ON EXIT'
,t2.[GENERIC YOGURT OOS: STATUS: # OOS ON ENTRY] 'OOS ON ENTRY'
,t2.[REASON FOR YOUR VISIT]
,t2.[NUMBER OF Matt POINTS OF INTERRUPTION (DISPLAY)]
,t2.[IS THE CORPORATE DISPLAY IN PLACE?]
,t2.[IS THERE SUPPOSED TO BE A CORPORATE DISPLAY AS PER THE RAP]
,t2.[IS THERE A Matt DISPLAY TO SUPPORT THE FRONT/BACK?]
,t2.[IS THERE A Matt FRONT/BACK PAGE AD]
,t2.[IS THERE DISPLAY SPACE AVAILABLE FOR REFRIGERATED]
,t2.[IS THERE A SILK FRONT/BACK PAGE AD?]
,t2.[IS THERE A SILK DISPLAY TO SUPPORT THE FRONT/BACK?]
,t2.[IS THERE SUPPOSED TO BE A SILK CORPORATE DISPLAY AS PER THE RAP]
,t2.[IS THE SILK CORPORATE DISPLAY IN PLACE?]
,t2.[Product Superiority]
,t2.[PB REG PRICING CONSISTENCY]
,t2.[ADDITIONAL PRODUCT SUP]
,t2.[YOGURT REG PRICING CONSISTENCY]
,t2.[Comments POS]
FROM CteSeven t1
LEFT JOIN #EXCELDATA t2
ON t1.[VisitID] = t2.[Visit ID]
)
, CteNine AS
(
SELECT [Unique ID]
-- ,CONVERT(BIGINT, [SKU]) 'SKU'
-- ,[Description]
-- ,CONVERT(INT, [Number of Displays]) 'Number of Displays'
,[VisitId]
,[Reason For Visit]
,[Project ID]
,[Start Date]
,[Start Time]
,[End Date]
,[End Time]
,[Duration Of Visit]
,[Response Date/Time]
,[Manager FirstName]
,[Staff Number]
,[Staff FirstName]
,[Staff LastName]
,[Banner]
,[LocationID]
,[Banner level 1]
,[Banner Level 2]
,[Banner Level 3]
,[Address]
,[City]
,[Province]
,[Facing (s) Variation]
,[Incremental SKUs]
,[Missing Distribution]
,[OSS On Exit]
,[OOS ON ENTRY]
,[REASON FOR YOUR VISIT]
,[NUMBER OF Matt POINTS OF INTERRUPTION (DISPLAY)]
,[IS THE CORPORATE DISPLAY IN PLACE?]
,[IS THERE SUPPOSED TO BE A CORPORATE DISPLAY AS PER THE RAP]
,[IS THERE A Matt DISPLAY TO SUPPORT THE FRONT/BACK?]
,[IS THERE A Matt FRONT/BACK PAGE AD]
,[IS THERE DISPLAY SPACE AVAILABLE FOR REFRIGERATED]
,[IS THERE A SILK FRONT/BACK PAGE AD?]
,[IS THERE A SILK DISPLAY TO SUPPORT THE FRONT/BACK?]
,[IS THERE SUPPOSED TO BE A SILK CORPORATE DISPLAY AS PER THE RAP]
,[IS THE SILK CORPORATE DISPLAY IN PLACE?]
,[Product Superiority]
,[PB REG PRICING CONSISTENCY]
,[ADDITIONAL PRODUCT SUP]
,[YOGURT REG PRICING CONSISTENCY]
,[Comments POS]
FROM CteEight
)
SELECT * INTO [TestMattDB].[dbo].[TestWeeklyDataSet] FROM CteNine;
WITH M AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY [VisitID] ORDER BY [VisitID]) RN ,*
FROM [TestMattDB].[dbo].[TestWeeklyDataSet]
)
DELETE FROM M
WHERE RN >= 2;
DELETE t1
FROM [TestMattDB].[dbo].[TestWeeklyDataSet] t1
LEFT JOIN #EXCELDATA t2
ON t1.VisitID = t2.[Visit ID]
WHERE t2.[Visit ID] IS NULL AND t1.[Project ID] = ProjectID;
DELETE
FROM [TestMattDB].[dbo].[TestWeeklyDataSet]
WHERE ([LocationID] LIKE '%MATCH%' OR [LocationID] LIKE '%MATCH MG%' OR [LocationID] LIKE '%FIELD%');
UPDATE [TestMattDB].[dbo].[TestWeeklyDataSet]
SET [Banner level 1] = 'NO FRILLS'
WHERE [Banner level 1] LIKE '%NOFRILLS%';
UPDATE [MattPortal-Matt].[dbo].[MattFullDataSet1266]
SET [OSS On Exit] = NULL
WHERE [Banner level 2] LIKE 'SOBEY''S MARITIMES';
UPDATE [MattPortal-Matt].[dbo].[MattFullDataSet1266]
SET [Description] = 'ACTIVIA VAN 650G'
WHERE [Description] LIKE 'ACTIVIA VAN 650G (';
--start date of weekly raw dataset
--DECLARE TestWeeklyDate DATE;
--SET TestWeeklyDate = (SELECT DISTINCT MIN(CONVERT(DATE, [Start Date])) FROM [TestMattDB].[dbo].[TestWeeklyDataSet]);
--Delete from Full Data Set where weekly dataset starts
--done in order to overwrite current data
DELETE FROM [MattPortal-Matt].[dbo].[MattFullDataSet] WHERE [Start Date] >= '3/2/19';
INSERT INTO [MattPortal-Matt].[dbo].[MattFullDataSet]
(
-- [SKU]
-- ,[Description]
-- ,[Number of Displays]
[Project ID]
,[VisitID]
,[Reason For Visit]
,[Start Date]
,[Start Time]
,[End Date]
,[End Time]
,[Duration Of Visit]
,[Response Date/time]
,[Manager FirstName]
,[Staff Number]
,[Staff FirstName]
,[Staff LastName]
,[Banner level 1]
,[Banner Level 2]
,[Banner Level 3]
,[Location ID]
,[Address]
,[Location City]
,[Location Province]
,[ADDITIONAL PRODUCT SUP]
,[YOGURT REG PRICING CONSISTENCY]
,[PB REG PRICING CONSISTENCY]
,[Comments POS]
,[OOS On Entry]
,[OSS On Exit]
,[Facing (s) Variation]
,[Incremental SKUs]
,[Missing Distribution]
,[NUMBER OF Matt POINTS OF INTERRUPTION (DISPLAY)]
,[IS THE CORPORATE DISPLAY IN PLACE?]
,[IS THERE SUPPOSED TO BE A CORPORATE DISPLAY AS PER THE RAP]
,[IS THERE A Matt DISPLAY TO SUPPORT THE FRONT/BACK?]
,[IS THERE A Matt FRONT/BACK PAGE AD]
,[IS THERE DISPLAY SPACE AVAILABLE FOR REFRIGERATED]
,[Product Superiority]
,[Unique ID]
)
SELECT-- [SKU]
-- ,[Description]
-- ,[Number of Displays]
[Project ID]
,[VisitID]
,[Reason For Visit]
,[Start Date]
,[Start Time]
,[End Date]
,[End Time]
,[Duration Of Visit]
,[Response Date/time]
,[Manager FirstName]
,[Staff Number]
,[Staff FirstName]
,[Staff LastName]
,[Banner level 1]
,[Banner Level 2]
,[Banner Level 3]
,[LocationID]
,[Address]
,[City]
,[Province]
,[ADDITIONAL PRODUCT SUP]
,[YOGURT REG PRICING CONSISTENCY]
,[PB REG PRICING CONSISTENCY]
,[Comments POS]
,[OOS On Entry]
,[OSS On Exit]
,[Facing (s) Variation]
,[Incremental SKUs]
,[Missing Distribution]
,[NUMBER OF Matt POINTS OF INTERRUPTION (DISPLAY)]
,[IS THE CORPORATE DISPLAY IN PLACE?]
,[IS THERE SUPPOSED TO BE A CORPORATE DISPLAY AS PER THE RAP]
,[IS THERE A Matt DISPLAY TO SUPPORT THE FRONT/BACK?]
,[IS THERE A Matt FRONT/BACK PAGE AD]
,[IS THERE DISPLAY SPACE AVAILABLE FOR REFRIGERATED]
,[Product Superiority]
,[Unique ID]
FROM [TestMattDB].[dbo].[TestWeeklyDataSet];
------------------------------------------------
----
---- OUT OF STOCK REPORT
----
-------------------------------------------------
WAITFOR DELAY '00:00:05';
IF OBJECT_ID('tempdb..#A') IS NOT NULL
DROP TABLE #A;
IF OBJECT_ID('tempdb..#B') IS NOT NULL
DROP TABLE #B;
IF OBJECT_ID('tempdb..#C') IS NOT NULL
DROP TABLE #C;
IF OBJECT_ID('tempdb..#D') IS NOT NULL
DROP TABLE #D;
IF OBJECT_ID('tempdb..#E') IS NOT NULL
DROP TABLE #E;
IF OBJECT_ID('tempdb..#F') IS NOT NULL
DROP TABLE #F;
IF OBJECT_ID('tempdb..#RAWDATA') IS NOT NULL
DROP TABLE #RAWDATA;
IF OBJECT_ID('tempdb..#ExpectedSKUs') IS NOT NULL
DROP TABLE #ExpectedSKUs;
-------------
--1
-------------
--DECLARE CORRECT PROJECTID
--MAKE SURE BOTH RAW DATA COLUMNS MATCH DATE
CREATE TABLE #OOS
(
[Visit ID] FLOAT,
[POS Activation] NVARCHAR(MAX),
[Supply Issue] NVARCHAR(MAX),
[Other Comments] NVARCHAR(MAX)
)
DECLARE query VARCHAR(MAX);
SET query = 'INSERT INTO #OOS SELECT [Visit ID], [POS Activation],[Supply Issue] ,[Other comments] FROM [TestMattDB].[dbo].[MattMarch' + MyDate + ']
UNION ALL
SELECT [Visit ID], [POS Activation],[Supply Issue] ,[Other comments] FROM [TestMattDB].[dbo].[ConvergeMarch' + MyDate + ']
'
EXEC (query);
--SELECT * INTO #RAWDATA FROM
--(
--SELECT [Visit ID], [POS Activation],[Supply Issue] ,[Other comments] FROM [TestMattDB].[dbo].[MattNov22] UNION ALL
--SELECT [Visit ID], [POS Activation],[Supply Issue] ,[Other comments] FROM [TestMattDB].[dbo].[ConvergeNov22]
--) AS A;
SELECT ROW_NUMBER() OVER(PARTITION BY [VisitID] ORDER BY [SKU]) AS RN
,[Start Date]
,[Banner level 1]
,[Banner level 2]
,[Banner level 3]
,[Address]
,[Location City] 'Location'
-- ,[SKU] 'Expected Sku Number'
,[VisitID]
,[Location City]
,[Location ID]
,[SKU]
,CASE WHEN [OSS On Exit] = 'Yes' THEN 1 END 'OSS On Exit = Yes'
,[OOS On Entry]
,[Reason For Visit]
,[Manager FirstName]
,[Staff FirstName]
,[Staff LastName]
,[Location Province] 'Province'
,[Unique ID]
INTO #A
FROM [MattPortal-Matt].[dbo].[MattFullDataSet]
WHERE [Project ID] = ProjectID;
-------------
--2
-------------
SELECT
[Unique ID], [Start Date], [Banner level 1], [Banner level 2], [Banner level 3], [Address], [Location City],
[VisitID], SUM([OSS On Exit = Yes]) 'Count of OOS On Exit', [OOS On Entry] 'OOS On Entry', [Location ID] ,[Reason For Visit]
,[Manager FirstName]
,[Staff FirstName]
,[Staff LastName]
,[Province]
INTO #B FROM #A
GROUP BY [Unique ID], [VisitID], [OOS On Entry], [Location ID],[Start Date], [Banner level 1], [Banner level 2], [Banner level 3], [Address], [Location City]
,[Reason For Visit]
,[Manager FirstName]
,[Staff FirstName]
,[Staff LastName]
,[Province]
DROP TABLE #A;
-------------
--3
-------------
SELECT
t1.[Unique ID]
,t1.[Start Date]
,t1.[Banner level 1]
,t1.[Banner level 2]
,t1.[Banner level 3]
,t1.[Address]
,t1.[Location City]
,t1.[VisitID]
,t1.[Reason For Visit]
,t1.[Manager FirstName]
,t1.[Staff FirstName]
,t1.[Staff LastName]
,t1.[Province]
,t2.[Location ID]
,t2.[Count of SKUs] 'Expected SKU #'
,t1.[Count of OOS On Exit]
,t1.[OOS On Entry]
,CONVERT(FLOAT, t1.[Count of OOS On Exit]) / CONVERT(FLOAT, t2.[Count of SKUs]) '%OOS ON EXIT'
,CONVERT(FLOAT, t1.[OOS On Entry]) / CONVERT(FLOAT, t2.[Count of SKUs]) '%OOS ON ENTRY'
INTO #C FROM #B t1
LEFT JOIN
[MattPortal-Matt].[dbo].[MattExpectedSKUList1266] t2
ON (t1.[Location ID] = t2.[Location ID])-- OR t1.VisitID = t2.[VisitID]); --There is no visit id in the expected sku list.
DROP TABLE #B;
-------------
--4
-------------
SELECT DISTINCT
t1.[Unique ID]
,t1.[Start Date]
,t1.[Banner level 1]
,t1.[Banner level 2]
,t1.[Banner level 3]
,t1.[Address]
,t1.[Location City] 'City'
,t1.[Reason For Visit]
,t1.[Manager FirstName]
,t1.[Staff FirstName]
,t1.[Staff LastName]
,t1.[Province]
,t1.[VisitID]
,t2.[Location ID]
,t1.[Expected SKU #]
,t1.[Count of OOS On Exit]
,t1.[OOS On Entry], CAST(ROUND(t1.[%OOS ON EXIT], 2) AS NUMERIC(36,2)) '%OOS ON EXIT',
CAST(ROUND(t1.[%OOS On Entry], 2) AS NUMERIC(36,2)) '%OOS ON ENTRY'
INTO #D
FROM #C t1
LEFT JOIN [MattPortal-Matt].[dbo].[MattFullDataSet] t2
ON t1.VisitID = t2.[VisitID]
ORDER BY '%OOS On Entry' DESC;
SELECT t1.*
, t2.[POS Activation]
,t2.[Supply Issue]
,t2.[Other comments]
INTO
#F
FROM #D t1
LEFT JOIN #OOS t2
ON t1.[VisitID] = t2.[Visit ID]
--UPDATING POS Activation, Supply Issue and Other Comments column
UPDATE A1
SET A1.[POS Activation] = A2.[POS Activation],
A1.[Supply Issue] = A2.[Supply Issue],
A1.[Other comments] = A2.[Other comments]
FROM #F A1
LEFT JOIN #OOS A2
ON A1.VisitID = A2.[Visit ID];
--start date of weekly raw dataset
DECLARE TestWeeklyDate1 DATE;
SET TestWeeklyDate1 = (SELECT DISTINCT MIN(CONVERT(DATE, [Start Date])) FROM [TestMattDB].[dbo].[TestWeeklyDataSet]);
--Delete from Full Data Set where weekly dataset starts
--done in order to overwrite current data
DELETE FROM [MattPortal-Matt].[dbo].[MattMattOutOfStock] WHERE [Start Date] >= '3/2/19';
INSERT INTO [MattPortal-Matt].[dbo].[MattMattOutOfStock]
(
[Unique ID]
,[VisitID]
,[Start Date]
,[Banner level 1]
,[Banner level 2]
,[Banner level 3]
,[Address]
,[City]
,[Province]
,[Location ID]
,[Expected SKU #]
,[Count of OOS On Exit]
,[OOS On Entry]
,[%OOS ON EXIT]
,[%OOS ON ENTRY]
,[POS Activation]
,[Supply Issue]
,[Other comments]
,[Reason For Visit]
,[Manager FirstName]
,[Staff FirstName]
,[Staff LastName]
)
SELECT
[Unique ID]
,[VisitID]
,[Start Date]
,[Banner level 1]
,[Banner level 2]
,[Banner level 3]
,[Address]
,[City]
,[Province]
,[Location ID]
,[Expected SKU #]
,[Count of OOS On Exit]
,[OOS On Entry]
,[%OOS ON EXIT]
,[%OOS ON ENTRY]
,[POS Activation]
,[Supply Issue]
,[Other comments]
,[Reason For Visit]
,[Manager FirstName]
,[Staff FirstName]
,[Staff LastName]
FROM #F;
UPDATE [MattPortal-Matt].[dbo].[MattMattOutOfStock1266]
SET [Count of OOS On Exit] = NULL, [%OOS ON EXIT] = NULL
WHERE [Banner Level 2] LIKE '%SOBEY''S MARITIMES%';
------------------------------------------------
----
---- GENERIC BANNER REPORT
----
-------------------------------------------------
WAITFOR DELAY '00:00:05';
IF OBJECT_ID('tempdb..#A1') IS NOT NULL
DROP TABLE #A1;
IF OBJECT_ID('tempdb..#B1') IS NOT NULL
DROP TABLE #B1;
IF OBJECT_ID('tempdb..#C1') IS NOT NULL
DROP TABLE #C1;
IF OBJECT_ID('tempdb..#GENERICRAWDATA') IS NOT NULL
DROP TABLE #GENERICRAWDATA;
CREATE TABLE #GENERICRAWDATA
(
[Visit ID] FLOAT,
[Timekeeping Start Date] NVARCHAR(MAX),
[DANETTE: DANETTE: # SKUS] FLOAT,
[DANETTE: DANETTE: # FACINGS ENTRY] FLOAT,
[DANETTE: DANETTE: # FACINGS EXIT] FLOAT,
[DANETTE: DANETTE: OOS ON EXIT (2 or less)] FLOAT,
[SILK/SO DELICIOUS PBB: SILK / SO DELICIOUS REF BEV#: # SKUS] FLOAT,
[SILK/SO DELICIOUS PBB: SILK / SO DELICIOUS REF BEV#: # FACINGS ] FLOAT,
[SILK/SO DELICIOUS PBB: SILK / SO DELICIOUS REF BEV#: # FACINGS 1] FLOAT,
[SILK/SO DELICIOUS PBB: SILK / SO DELICIOUS REF BEV#: OOS ON EXIT] FLOAT,
[SILK CREAMER: SILK CREAMER: # SKUS] FLOAT,
[SILK CREAMER: SILK CREAMER: # FACINGS ENTRY] FLOAT,
[SILK CREAMER: SILK CREAMER: # FACINGS EXIT] FLOAT,
[SILK CREAMER: SILK CREAMER: OOS ON EXIT (2 or less)] FLOAT,
[SILK YOGURT: SILK YOGURT: # SKUS] FLOAT,
[SILK YOGURT: SILK YOGURT: # FACINGS ENTRY] FLOAT,
[SILK YOGURT: SILK YOGURT: # FACINGS EXIT/SORTIE] FLOAT,
[SILK YOGURT: SILK YOGURT: OOS ON EXIT (2 or less)] FLOAT,
[SILK/SO DELICIOUS SHELF STABLE: SILK / SO DELICIOUS SHELF STABLE] FLOAT,
[SILK/SO DELICIOUS SHELF STABLE: SILK / SO DELICIOUS SHELF STABL1] FLOAT,
[SILK/SO DELICIOUS SHELF STABLE: SILK / SO DELICIOUS SHELF STABL2] FLOAT,
[SILK/SO DELICIOUS SHELF STABLE: SILK / SO DELICIOUS SHELF STABL3] FLOAT,
[SO DELICIOUS FROZEN IN STORE: SO DELICIOUS FROZEN DESSERT: # SKU] FLOAT,
[SO DELICIOUS FROZEN IN STORE: SO DELICIOUS FROZEN DESSERT: # FAC] FLOAT,
[SO DELICIOUS FROZEN IN STORE: SO DELICIOUS FROZEN DESSERT: # FA1] FLOAT,
[SO DELICIOUS FROZEN IN STORE: SO DELICIOUS FROZEN DESSERT: OOS O] FLOAT,
[PB REG PRICING CONSISTENCY] NVARCHAR(MAX)
)
DECLARE querytwo VARCHAR(MAX);
SET querytwo = 'INSERT INTO #GENERICRAWDATA SELECT
[Visit ID]
,[Timekeeping Start Date]
,[DANETTE: DANETTE: # SKUS]
,[DANETTE: DANETTE: # FACINGS ENTRY]
,[DANETTE: DANETTE: # FACINGS EXIT]
,[DANETTE: DANETTE: OOS ON EXIT (2 or less)]
,[SILK/SO DELICIOUS PBB: SILK / SO DELICIOUS REF BEV#: # SKUS]
,[SILK/SO DELICIOUS PBB: SILK / SO DELICIOUS REF BEV#: # FACINGS ]
,[SILK/SO DELICIOUS PBB: SILK / SO DELICIOUS REF BEV#: # FACINGS 1]
,[SILK/SO DELICIOUS PBB: SILK / SO DELICIOUS REF BEV#: OOS ON EXIT]
,[SILK CREAMER: SILK CREAMER: # SKUS]
,[SILK CREAMER: SILK CREAMER: # FACINGS ENTRY]
,[SILK CREAMER: SILK CREAMER: # FACINGS EXIT]
,[SILK CREAMER: SILK CREAMER: OOS ON EXIT (2 or less)]
,[SILK YOGURT: SILK YOGURT: # SKUS]
,[SILK YOGURT: SILK YOGURT: # FACINGS ENTRY]
,[SILK YOGURT: SILK YOGURT: # FACINGS EXIT/SORTIE]
,[SILK YOGURT: SILK YOGURT: OOS ON EXIT (2 or less)]
,[SILK/SO DELICIOUS SHELF STABLE: SILK / SO DELICIOUS SHELF STABLE]
,[SILK/SO DELICIOUS SHELF STABLE: SILK / SO DELICIOUS SHELF STABL1]
,[SILK/SO DELICIOUS SHELF STABLE: SILK / SO DELICIOUS SHELF STABL2]
,[SILK/SO DELICIOUS SHELF STABLE: SILK / SO DELICIOUS SHELF STABL3]
,[SO DELICIOUS FROZEN IN STORE: SO DELICIOUS FROZEN DESSERT: # SKU]
,[SO DELICIOUS FROZEN IN STORE: SO DELICIOUS FROZEN DESSERT: # FAC]
,[SO DELICIOUS FROZEN IN STORE: SO DELICIOUS FROZEN DESSERT: # FA1]
,[SO DELICIOUS FROZEN IN STORE: SO DELICIOUS FROZEN DESSERT: OOS O]
,[PB REG PRICING CONSISTENCY]
FROM [TestMattDB].[dbo].[MattMarch' + MyDateTwo + ']
UNION ALL
SELECT [Visit ID]
,[Timekeeping Start Date]
,[DANETTE: DANETTE: # SKUS]
,[DANETTE: DANETTE: # FACINGS ENTRY]
,[DANETTE: DANETTE: # FACINGS EXIT]
,[DANETTE: DANETTE: OOS ON EXIT (2 or less)]
,[SILK/SO DELICIOUS PBB: SILK / SO DELICIOUS REF BEV#: # SKUS]
,[SILK/SO DELICIOUS PBB: SILK / SO DELICIOUS REF BEV#: # FACINGS ]
,[SILK/SO DELICIOUS PBB: SILK / SO DELICIOUS REF BEV#: # FACINGS 1]
,[SILK/SO DELICIOUS PBB: SILK / SO DELICIOUS REF BEV#: OOS ON EXIT]
,[SILK CREAMER: SILK CREAMER: # SKUS]
,[SILK CREAMER: SILK CREAMER: # FACINGS ENTRY]
,[SILK CREAMER: SILK CREAMER: # FACINGS EXIT]
,[SILK CREAMER: SILK CREAMER: OOS ON EXIT (2 or less)]
,[SILK YOGURT: SILK YOGURT: # SKUS]
,[SILK YOGURT: SILK YOGURT: # FACINGS ENTRY]
,[SILK YOGURT: SILK YOGURT: # FACINGS EXIT/SORTIE]
,[SILK YOGURT: SILK YOGURT: OOS ON EXIT (2 or less)]
,[SILK/SO DELICIOUS SHELF STABLE: SILK / SO DELICIOUS SHELF STABLE]
,[SILK/SO DELICIOUS SHELF STABLE: SILK / SO DELICIOUS SHELF STABL1]
,[SILK/SO DELICIOUS SHELF STABLE: SILK / SO DELICIOUS SHELF STABL2]
,[SILK/SO DELICIOUS SHELF STABLE: SILK / SO DELICIOUS SHELF STABL3]
,[SO DELICIOUS FROZEN IN STORE: SO DELICIOUS FROZEN DESSERT: # SKU]
,[SO DELICIOUS FROZEN IN STORE: SO DELICIOUS FROZEN DESSERT: # FAC]
,[SO DELICIOUS FROZEN IN STORE: SO DELICIOUS FROZEN DESSERT: # FA1]
,[SO DELICIOUS FROZEN IN STORE: SO DELICIOUS FROZEN DESSERT: OOS O]
,[PB REG PRICING CONSISTENCY] FROM [TestMattDB].[dbo].[ConvergeMarch' + MyDateTwo + ']'
EXEC (querytwo);
SELECT * INTO #B1 FROM (
SELECT [Visit ID] ,[Timekeeping Start Date] [Temp Table B Start Date]
,'Danette' 'Brand'
,SUM([DANETTE: DANETTE: # SKUS]) '#SKUs'
,SUM([DANETTE: DANETTE: # FACINGS ENTRY]) '# Facings on Entry'
,SUM([DANETTE: DANETTE: # FACINGS EXIT]) '# Facings on Exit'
,SUM([DANETTE: DANETTE: OOS ON EXIT (2 or less)]) 'OOS on Exit'
,[Timekeeping Start Date]
FROM #GENERICRAWDATA
GROUP BY [Visit ID] ,[Timekeeping Start Date]
UNION ALL
SELECT [Visit ID] ,[Timekeeping Start Date] b
,'Silk Ref PBB' 'Brand'
,SUM([SILK/SO DELICIOUS PBB: SILK / SO DELICIOUS REF BEV#: # SKUS]) '#SKUs'
,SUM([SILK/SO DELICIOUS PBB: SILK / SO DELICIOUS REF BEV#: # FACINGS ]) '# Facings on Entry'
,SUM([SILK/SO DELICIOUS PBB: SILK / SO DELICIOUS REF BEV#: # FACINGS 1]) '# Facings on Exit'
,SUM([SILK/SO DELICIOUS PBB: SILK / SO DELICIOUS REF BEV#: OOS ON EXIT]) 'OOS on Exit'
,[Timekeeping Start Date]
FROM #GENERICRAWDATA
GROUP BY [Visit ID] ,[Timekeeping Start Date]
UNION ALL
SELECT [Visit ID] ,[Timekeeping Start Date] c
,'Silk Creamer' 'Brand'
,SUM([SILK CREAMER: SILK CREAMER: # SKUS]) '#SKUs'
,SUM([SILK CREAMER: SILK CREAMER: # FACINGS ENTRY]) '# Facings on Entry'
,SUM([SILK CREAMER: SILK CREAMER: # FACINGS EXIT]) '# Facings on Exit'
,SUM([SILK CREAMER: SILK CREAMER: OOS ON EXIT (2 or less)]) 'OOS on Exit'
,[Timekeeping Start Date]
FROM #GENERICRAWDATA
GROUP BY [Visit ID] ,[Timekeeping Start Date]
UNION ALL
SELECT [Visit ID] ,[Timekeeping Start Date] d
,'Silk Yogurt' 'Brand'
,SUM(CONVERT(INT,[SILK YOGURT: SILK YOGURT: # SKUS])) '#SKUs'
,SUM(CONVERT(INT,[SILK YOGURT: SILK YOGURT: # FACINGS ENTRY])) '# Facings on Entry'
,SUM(CONVERT(INT,[SILK YOGURT: SILK YOGURT: # FACINGS EXIT/SORTIE])) '# Facings on Exit'
,SUM(CONVERT(INT,[SILK YOGURT: SILK YOGURT: OOS ON EXIT (2 or less)])) 'OOS on Exit'
,[Timekeeping Start Date]
FROM #GENERICRAWDATA
GROUP BY [Visit ID] ,[Timekeeping Start Date]
UNION ALL
SELECT [Visit ID] ,[Timekeeping Start Date] e
,'So Delicious' 'Brand'
,SUM(CONVERT(INT,[SO DELICIOUS FROZEN IN STORE: SO DELICIOUS FROZEN DESSERT: # SKU])) '#SKUs'
,SUM(CONVERT(INT,[SO DELICIOUS FROZEN IN STORE: SO DELICIOUS FROZEN DESSERT: # FAC])) '# Facings on Entry'
,SUM(CONVERT(INT,[SO DELICIOUS FROZEN IN STORE: SO DELICIOUS FROZEN DESSERT: # FA1])) '# Facings on Exit'
,SUM(CONVERT(INT,[SO DELICIOUS FROZEN IN STORE: SO DELICIOUS FROZEN DESSERT: OOS O])) 'OOS on Exit'
,[Timekeeping Start Date]
FROM #GENERICRAWDATA
GROUP BY [Visit ID] ,[Timekeeping Start Date]
UNION ALL
SELECT
[Visit ID] ,[Timekeeping Start Date] f
,'Silk/So Delicious Aseptic' 'Brand'
,SUM(CONVERT(INT,[SILK/SO DELICIOUS SHELF STABLE: SILK / SO DELICIOUS SHELF STABLE])) '#SKUs'
,SUM(CONVERT(INT,[SILK/SO DELICIOUS SHELF STABLE: SILK / SO DELICIOUS SHELF STABL1])) '# Facings on Entry'
,SUM(CONVERT(INT,[SILK/SO DELICIOUS SHELF STABLE: SILK / SO DELICIOUS SHELF STABL2])) '# Facings on Exit'
,SUM(CONVERT(INT,[SILK/SO DELICIOUS SHELF STABLE: SILK / SO DELICIOUS SHELF STABL3])) 'OOS on Exit'
,[Timekeeping Start Date]
FROM #GENERICRAWDATA
GROUP BY [Visit ID] ,[Timekeeping Start Date]
) AS B1;
SELECT DISTINCT
t1.[Unique ID]
,t1.[VisitID]
,t1.[Reason For Visit]
,t1.[Start Date]
,t1.[Duration Of Visit]
,t1.[Location ID] 'Location ID'
,t1.[Location City]
,t1.[Location Province]
,t1.[Banner level 1]
,t1.[Banner Level 2]
,t1.[Banner Level 3]
,t2.[Brand]
,t1.[Manager FirstName]
,t1.[Staff FirstName]
,t1.[Staff LastName]
,t3.[PB REG PRICING CONSISTENCY]
,t2.[Temp Table B Start Date]
,t2.[#SKUs]
,t2.[# Facings on Entry]
,t2.[# Facings on Exit]
,t2.[OOS on Exit]
INTO #C1
--INTO [MattPortal-Matt].[dbo].[MattGenericBannerReport]
FROM [MattPortal-Matt].[dbo].[MattFullDataSet1266] t1
LEFT JOIN #B1 t2
ON t1.[VisitID] = t2.[Visit ID]
LEFT JOIN #GENERICRAWDATA t3
ON t1.[VisitID] =t3.[Visit ID];
DECLARE TestWeeklyDateTwo DATE;
SET TestWeeklyDateTwo = (SELECT DISTINCT MIN(CONVERT(DATE, [Start Date])) FROM [TestMattDB].[dbo].[TestWeeklyDataSet]);
--Delete from Full Data Set where weekly dataset starts
--done in order to overwrite current data
DELETE FROM [MattPortal-Matt].[dbo].[MattGenericBannerReport] WHERE [Start Date] >= '3/2/19';
INSERT INTO [MattPortal-Matt].[dbo].[MattGenericBannerReport]
(
[Unique ID]
,[VisitID]
,[Reason For Visit]
,[Start Date]
,[Duration Of Visit]
,[Location ID]
,[Location City]
,[Province]
,[Banner level 1]
,[Banner Level 2]
,[Banner Level 3]
,[Brand]
,[Manager FirstName]
,[Staff FirstName]
,[Staff LastName]
,[PB REG PRICING CONSISTENCY]
,[#SKUs]
,[# Facings on Entry]
,[# Facings on Exit]
,[OOS on Exit]
)
SELECT
[Unique ID]
,[VisitID]
,[Reason For Visit]
,[Start Date]
,[Duration Of Visit]
,[Location ID]
,[Location City]
,[Location Province]
,[Banner level 1]
,[Banner Level 2]
,[Banner Level 3]
,[Brand]
,[Manager FirstName]
,[Staff FirstName]
,[Staff LastName]
,[PB REG PRICING CONSISTENCY]
,[#SKUs]
,[# Facings on Entry]
,[# Facings on Exit]
,[OOS on Exit]
FROM #C1;
UPDATE t2
SET t2.[PB REG PRICING CONSISTENCY] = t1.[PB REG PRICING CONSISTENCY]
FROM [MattPortal-Matt].[dbo].[MattFullDataSet] t1
LEFT JOIN [MattPortal-Matt].[dbo].[MattGenericBannerReport] t2
ON t1.[VisitID] = t2.[VisitID]
WHERE t1.[Start Date] > '2019-03-02';
UPDATE [MattPortal-Matt].[dbo].[MattGenericBannerReport]
SET [OOS On Exit] = NULL
WHERE [Banner Level 2] LIKE '%SOBEY''S MARITIMES%';
UPDATE [MattPortal-Matt].[dbo].[MattGenericBannerReport]
SET [PB REG PRICING CONSISTENCY] = NULL, [# Facings on Entry] = NULL, [# Facings on Exit] = NULL
WHERE [Start Date] >= '1/21/19';
------------------------------------------------
----
---- PRICE DISC TABLE
----
-------------------------------------------------
--WAITFOR DELAY '00:00:05';
IF OBJECT_ID('MattPortal-Matt.dbo.MattPriceDiscrepancy', 'U') IS NOT NULL
DROP TABLE [MattPortal-Matt].[dbo].[MattPriceDiscrepancy];
SELECT DISTINCT
FULL1.[VisitID]
,FULL1.[Start Date]
,FULL1.[Banner level 1]
,FULL1.[Banner Level 2]
,FULL1.[Banner Level 3]
,FULL1.[Address]
,FULL1.[Location City] 'City'
,FULL1.[Location ID]
,PRICEDISC.[SKU]
,PRICEDISC.[DESCRIPTION]
, PRICEDISC.Price
INTO
[MattPortal-Matt].[dbo].[MattPriceDiscrepancy]
FROM [MattPortal-Matt].[dbo].[MattFullDataSet] FULL1
LEFT JOIN [TestMattDB].[dbo].[PriceDiscFullVersion] PRICEDISC
ON FULL1.[VisitID] = PRICEDISC.[VisitID]
WHERE PRICEDISC.Price IS NOT NULL;
UPDATE [MattPortal-Matt].[dbo].[MattFullDataSet]
SET [Description] = 'ACTIVIA VAN 650G'
WHERE [Description] LIKE 'ACTIVIA VAN 650G (';
DELETE FROM [MattPortal-Matt].[dbo].[MattFullDataSet] WHERE [Start Date] < '2018-03-29';
DELETE FROM [MattPortal-Matt].[dbo].[MattMattOutOfStock] WHERE [Start Date] < '2018-03-29';
DELETE FROM [MattPortal-Matt].[dbo].[MattGenericBannerReport] WHERE [Start Date] < '2018-03-29';
DELETE FROM [MattPortal-Matt].[dbo].[MattPriceDiscrepancy] WHERE [Start Date] < '2018-03-29';
DELETE FROM [MattPortal-Matt].[dbo].[MattFullDataSet] WHERE [Start Date] > GETDATE();
DELETE FROM [MattPortal-Matt].[dbo].[MattMattOutOfStock] WHERE [Start Date] > GETDATE();
DELETE FROM [MattPortal-Matt].[dbo].[MattGenericBannerReport] WHERE [Start Date] > GETDATE();
DELETE FROM [MattPortal-Matt].[dbo].[MattPriceDiscrepancy] WHERE [Start Date] > GETDATE();
DECLARE OutOfStock INT;
SET OutOfStock = (
SELECT COUNT(DISTINCT [VisitID])
FROM [MattPortal-Matt].[dbo].[MattMattOutOfStock]
);
DECLARE GenericBannerReport INT;
SET GenericBannerReport = (
SELECT COUNT(DISTINCT [VisitID])
FROM [MattPortal-Matt].[dbo].[MattGenericBannerReport]
);
DECLARE FullDataSet INT;
SET FullDataSet = (
SELECT COUNT(DISTINCT [VisitID])
FROM [MattPortal-Matt].[dbo].[MattFullDataSet]
);
SELECT
FullDataSet 'Full Data Set' , OutOfStock 'Out Of Stock', GenericBannerReport 'Generic Banner Report';
SELECT *
FROM [MattPortal-Matt].[dbo].[MattFullDataSet]
WHERE ([Location ID] LIKE '%MATCH%' OR [Location ID] LIKE '%MATCH MG%' OR [Location ID] LIKE '%FIELD%');
SELECT * FROM [MattPortal-Matt].[dbo].[MattFullDataSet]
WHERE ([Banner level 1] LIKE '%NOFRILLS%' OR [Banner level 2] LIKE '%NOFRILLS%' OR [Banner level 3] LIKE '%NOFRILLS%');
SELECT * FROM [MattPortal-Matt].[dbo].[MattMattOutOfStock]
WHERE ([POS Activation] IS NULL OR [Supply Issue] IS NULL OR [Other comments] IS NULL)
ORDER BY [Start Date] DESC;
SELECT * FROM [MattPortal-Matt].[dbo].[MattMattOutOfStock] WHERE [Banner level 2] LIKE 'SOBEY''S MARITIMES' ORDER BY [Start Date] DESC;
SELECT DISTINCT [Start Date] 'Full Data Set Start Date' FROM [MattPortal-Matt].[dbo].[MattFullDataSet] ORDER BY [Start Date] DESC;
SELECT DISTINCT [Start Date] 'Out Of Stock' FROM [MattPortal-Matt].[dbo].[MattMattOutOfStock] ORDER BY [Start Date] DESC;
SELECT DISTINCT [Start Date] 'Generic Start Date' FROM [MattPortal-Matt].[dbo].[MattGenericBannerReport] ORDER BY [Start Date] DESC;
SELECT DISTINCT [Start Date] 'Price Discrepancy' FROM [MattPortal-Matt].[dbo].[MattPriceDiscrepancy] ORDER BY [Start Date] DESC;
UPDATE [MattPortal-Matt].[dbo].[MattFullDataSet]
SET [OSS On Exit] = NULL
WHERE [Banner level 2] LIKE 'SOBEY''S MARITIMES';
UPDATE [MattPortal-Matt].[dbo].[MattFullDataSet]
SET [Description] = 'ACTIVIA VAN 650G'
WHERE [Description] LIKE 'ACTIVIA VAN 650G (';
SELECT * FROM [MattPortal-Matt].[dbo].[MattFullDataSet]
WHERE [Description] LIKE 'ACTIVIA VAN 650G (';
SELECT * FROM [MattPortal-Matt].[dbo].[MattFullDataSet]
WHERE [Banner level 1] LIKE '%NOFRILLS%';
--FULL DATA SET VALIDATION
WITH M AS
(
SELECT
ROW_NUMBER() OVER(PARTITION BY [VisitID] ORDER BY [SKU]) FULLDATARowNumberTest,
* FROM [MattPortal-Matt].[dbo].[MattFullDataSet] WHERE [Start Date] > '2019-01-20'
)
SELECT * FROM M;
-- OUT OF STOCK VALIDATION
WITH M AS
(
SELECT
ROW_NUMBER() OVER(PARTITION BY [VisitID] ORDER BY [VisitID]) OOSRowNumberTest,
* FROM [MattPortal-Matt].[dbo].[MattMattOutOfStock] WHERE [Start Date] > '2019-01-20'
)
SELECT * FROM M WHERE M.OOSRowNumberTest >= 1;
-- GENERIC BANNER VALIDATION
WITH M AS
(
SELECT
ROW_NUMBER() OVER(PARTITION BY [VisitID] ORDER BY [VisitID]) GENERICRowNumberTest,
* FROM [MattPortal-Matt].[dbo].[MattGenericBannerReport] WHERE [Start Date] > '2019-01-20'
)
SELECT * FROM M WHERE M.GENERICRowNumberTest >= 1;
SELECT * FROM [MattPortal-Matt].[dbo].[MattFullDataSet] WHERE [Start Date] > '2019-03-01' ORDER BY [SKU] DESC;
SELECT * FROM [MattPortal-Matt].[dbo].[MattMattOutOfStock] WHERE [Start Date] > '2019-03-01' ORDER BY [Start Date] DESC;
SELECT * FROM [MattPortal-Matt].[dbo].[MattGenericBannerReport] WHERE [Start Date] > '2019-03-01' ORDER BY [Start Date] DESC;
SELECT * FROM [MattPortal-Matt].[dbo].[MattPriceDiscrepancy] ORDER BY [Start Date] DESC;