Sorry to be bringing this thread back up, however I was trying to implement the Sales Per hour Script.
But ended up with
In the script I got
declare @StartDate datetime = convert(varchar(25),'{Start}',126)
declare @EndDate datetime = convert(varchar(25),'{End}',126)
declare @StartHour int = @1 -- set the First Hour you want to track, or supply via report parm
declare @EndHour int = @2 -- set the Last Hour you want to track, or supply via report parm
/************************************/
declare @Hour int = 0
declare @TotalSales money = 0.00
declare @tbl_Sales table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[Hour] varchar(5)
,[Amount] decimal(9,2)
,[Tickets] int
,[AvgTx] decimal(5,2)
,[Percent] decimal(5,2)
)
set @Hour = @StartHour
WHILE @Hour < @EndHour+1
BEGIN
INSERT INTO @tbl_Sales
SELECT
@Hour as [Hour]
, SUM(CASE WHEN (DATEPART(hour,[Date])>=@Hour AND DATEPART(hour,[Date])<@Hour+1)
THEN [TotalAmount]
ELSE 0 END) as [Amount]
, COUNT(CASE WHEN (DATEPART(hour,[Date])>=@Hour AND DATEPART(hour,[Date])<@Hour+1)
THEN 1
ELSE null END) as [Tickets]
,0,0
FROM [dbo].[Tickets]
WHERE [Date]>=@StartDate AND [Date]<=@EndDate
SET @Hour = @Hour + 1
END
INSERT INTO @tbl_Sales
SELECT 'TTL', sum([Amount]), sum([Tickets]), 0, 0 FROM @tbl_Sales
set @TotalSales = (SELECT [Amount] FROM @tbl_Sales WHERE [Hour]='TTL')
UPDATE @tbl_Sales SET
[AvgTx] = (CASE WHEN [Tickets]=0 THEN 0 ELSE [Amount]/[Tickets] END)
,[Percent] = [Amount]/@TotalSales * 100
SELECT CASE
WHEN isnumeric([Hour])=1
THEN (CASE WHEN [Hour]<10 THEN '0'+[Hour] ELSE [Hour] END)+':00'
ELSE [Hour]
END as [Hour]
, [Amount], [Tickets], [AvgTx], [Percent]
FROM @tbl_Sales
any idea where im going wrong?
