Skip to main content

Sales Report (SQL Procedure, Query) by Hourly, using while loop in SQL, Get Today Date from SQL, Temporary Table in SQL  

CREATE PROCEDURE [GetSaleByHours]
(
  @Date datetime
)
AS
BEGIN
Create Table #Day  -- Create primary Table to store hourly based sales

(
   hour bigint
)
Declare @Count int = 0;
WHILE @Count <= 23    -- Adding values to temp table using while loop
 BEGIN  
    INSERT INTO #Day VALUES (@Count)
    SET @Count = @Count + 1;
END
SELECT  @Date AS DAYDATE, dh.hour AS DAYHOUR ,ISNULL(SUM(ST.sales),0) AS Sales FROM #Day dh LEFT OUTER JOIN SalesTable ST  ON dh.hour = DATEPART(hh,ST.TimeStamp)
GROUP BY dh.hour
DROP TABLE #Day   -- Droping temp table
END

Comments