SET NOCOUNT ON;
--Scripts for Gallons sold Every Hour
--This is a demanding script that may take time if the date range is too wide
--Designed for optimization
Use PDI_Warehouse_2064_01
--Go
DECLARE @EntStartDate AS DATETIME, @EntEndDate AS DATETIME
DECLARE @SiteID as Varchar(15)
DECLARE @ShowValuesWithNoSales as BIT
--Set the Date range needed.
SET @EntStartDate = '03/11/2023'
SET @EntEndDate = '03/11/2023'
--SET @SiteID = '6'
SET @SiteID = '79'
--Enter the specific site ID between the single quote or 'ALL' to return all sites.
--e.g SET @SiteID = '6' for Site_ID 6
-- Set this Option to 0 if do not want to show any hours that have no Fuel Gallon Sales.
-- Set this Option to 1 If show all hours of the day.
SET @ShowValuesWithNoSales = 0
-- Get MB Headers
-------------------------------------------------------------------------
--Get all MarketBasket_Header_Key involved
IF OBJECT_ID(N'tempdb..#MBHeaderKeys', N'U') IS NOT NULL DROP TABLE #MBHeaderKeys
CREATE TABLE #MBHeaderKeys
(
MarketBasket_Header_Key decimal(15,0),
calendar_key decimal(10,0),
organization_key int,
timeofday_key int
)
CREATE INDEX MBX1 ON #MBHeaderKeys(MarketBasket_Header_Key)
--Get only outside sales MBH.Inside_Sales_Flag = 0
INSERT INTO #MBHeaderKeys
SELECT MBH.MarketBasket_Header_Key, MBH.calendar_key, MBH.organization_key, MBH.timeofday_key
FROM MarketBasket_Header MBH (NoLock)
INNER JOIN organization_view O (NoLock)
ON
rganization_key = MBH.organization_key
AND ( (@SiteID = 'All') OR (@SiteID <> 'All' AND O.Site_ID = @SiteID) )
WHERE EXISTS ( SELECT 1 FROM Calendar C (NoLock)
WHERE C.calendar_key = MBH.calendar_key
AND C.Day_Date BETWEEN @EntStartDate
AND @EntEndDate
AND MBH.Inside_Sales_Flag = 0
AND MBH.Total_Quantity <> 0 )
ORDER BY MBH.calendar_key, organization_key, MarketBasket_Header_Key
--SELECT * FROM #MBHeaderKeys
IF OBJECT_ID(N'tempdb..#Temp_Hourly_Items', N'U') IS NOT NULL DROP TABLE #Temp_Hourly_Items
CREATE TABLE #Temp_Hourly_Items
(
calendar_Key int,
organization_key int,
timeofday_key int,
timeofday_hour INT,
Product_Key int,
Item_Desc Varchar(50),
Item_Type int,
Quantity_Sold decimal(18, 2),
Extended_Retail decimal(18, 2)
)
CREATE INDEX MBX1 ON #Temp_Hourly_Items (organization_key, calendar_Key)
--Limit sales to only fuel
INSERT INTO #Temp_Hourly_Items
SELECT
MLI.calendar_key,
MLI.organization_key,
MLI.TimeOfDay_Key,
T.timeofday_hour,
MLI.Product_Key,
P.Item_Desc,
MLI.Item_Type,
MLI.Quantity_Sold,
MLI.Extended_Retail
FROM MarketBasket_Line_Items MLI (NoLock)
INNER JOIN FuelProduct P (NoLock)
ON P.Product_Key = MLI.Product_Key
INNER JOIN timeofday T (NoLock)
ON T.timeofday_key = MLI.TimeOfDay_Key
WHERE EXISTS ( SELECT 1 FROM #MBHeaderKeys MBH
WHERE MBH.MarketBasket_Header_Key = MLI.MarketBasket_Header_Key
AND MBH.Organization_Key = MLI.Organization_Key
AND MLI.Item_Type = 2) --fuel sales only
ORDER BY MLI.calendar_key, MLI.organization_key
--Create a table with all the hours
IF OBJECT_ID(N'tempdb..#All_Hour_Items ', N'U') IS NOT NULL DROP TABLE #All_Hour_Items
CREATE TABLE #All_Hour_Items
(
calendar_Key int,
organization_key int,
Site_id Varchar(50),
Site_desc Varchar(50),
[Date] Date,
Day_Of_Week Varchar(50),
timeofday_hour INT,
Item_Desc Varchar(50),
Product_Key INT,
Gallons_Sold decimal(18, 2),
Fuel_Sales decimal(18, 2),
RowNum int identity(1,1)
)
CREATE INDEX MBX1 ON #All_Hour_Items (organization_key, calendar_Key, timeofday_hour)
INSERT INTO #All_Hour_Items
SELECT distinct
A.calendar_Key,
A.organization_key,
O.Site_id,
O.Site_desc,
[Date] = CAST(C.Day_Date as Date),
Day_Of_Week,
TOD.timeofday_hour ,
Grade = A.Item_Desc,
A.Product_Key,
Gallons_Sold = NULL,
Fuel_Sales = NULL
FROM #Temp_Hourly_Items A
INNER JOIN organization_view O (NoLock)
ON
rganization_key = A.organization_key
INNER JOIN Calendar C (NoLock)
ON C.calendar_key = A.calendar_key
CROSS JOIN (SELECT DISTINCT timeofday_hour FROM timeofday) tod
ORDER BY O.Site_id, A.calendar_Key, TOD.timeofday_hour , A.Product_Key
--Update #All_Hour_Items with gallon sales
UPDATE #All_Hour_Items SET Gallons_Sold = B.Gallons_Sold, Fuel_Sales = B.Fuel_Sales
FROM #All_Hour_Items A
INNER JOIN ( SELECT DISTINCT
A.organization_key,
A.calendar_Key,
timeofday_hour,
Item_Desc,
Gallons_Sold = SUM(Quantity_Sold),
Fuel_Sales = SUM(Extended_Retail)
FROM #Temp_Hourly_Items A
INNER JOIN organization_view O (NoLock)
ON
rganization_key = A.organization_key
INNER JOIN Calendar C (NoLock)
ON C.calendar_key = A.calendar_key
GROUP BY A.organization_key, A.calendar_Key, A.timeofday_hour, A.Item_Desc
) B
ON A.calendar_Key =B.calendar_Key
AND A.organization_key = B.organization_key
AND A.timeofday_hour = B.timeofday_hour
AND A.Item_Desc = B.Item_Desc
IF @ShowValuesWithNoSales <> 1
BEGIN
DELETE FROM #All_Hour_Items WHERE (Gallons_Sold IS NULL AND Fuel_Sales IS NULL)
END
--Output what you want
SELECT Site_id
,Site_desc
,[Date]
,Day_Of_Week
,Hour_Of_Day = timeofday_hour + 1
,Fuel_Grade = Item_Desc
,Gallons_Sold
,Fuel_Sales
FROM #All_Hour_Items ORDER BY RowNum
