Hourly Item Sale Report

hi

i want a report that display item sale report in a define time frame?

need help with the sql query

thanks

Search found this…

@QMcKay

using the above query as my guideline i have product this query:

declare @StartDate datetime = '7/1/2015' -- use '{Start}' here
declare @EndDate datetime = '7/1/2015'   -- use '{End}' here
declare @StartHour int = '11' -- set the First Hour you want to track
declare @EndHour int = '17'  -- set the Last Hour you want to track

/************************************/

declare @Hour int = 0

declare @tbl_Sales table
(
[ID]  INT IDENTITY(1,1) NOT NULL 
,[Hour] varchar(5)
,[Item] varchar(25)
,[Qty] int
,[Amount] decimal(5,2)
,[Totalamount] decimal(5,2)
)

set @Hour = @StartHour

WHILE @Hour < @EndHour+1
BEGIN
    INSERT INTO @tbl_Sales
    SELECT
      @Hour as [Hour]
    , CASE WHEN (DATEPART(hour,[CreatedDateTime])>=@Hour AND DATEPART(hour,[CreatedDateTime])<@Hour+1)
               THEN [MenuItemName]
               ELSE null END as [Item]
    , COUNT(CASE WHEN (DATEPART(hour,[CreatedDateTime])>=@Hour AND DATEPART(hour,[CreatedDateTime])<@Hour+1)
               THEN 1
               ELSE 0 END) as [Qty]
    ,0,0
    FROM [dbo].[Orders]
    WHERE [CreatedDateTime]>=@StartDate AND [CreatedDateTime]<=@EndDate
	group by [MenuItemName],[CreatedDateTime]
SET @Hour = @Hour + 1
END

SELECT * FROM @tbl_Sales

but i am doing something wrong as no record shown

what i want to achieve is to know which item has been ordered with their qty and total amount within a time frame

You changed

declare @StartDate datetime = convert(varchar(25),'{Start}',126)

to

declare @StartDate datetime = '7/1/2015'

That might be the reason.

1 Like

something is wrong with the query

i want to achieve

hour item amount qty total
11 kebab 2.50 2 5
11 rice 1 5 5

something is wrong with my query …can help to correct it

i want to achieve

hour item amount qty total
11 kebab 2.50 2 5
11 rice 1 5 5

if i set date as:
declare StartDate datetime = ‘7/1/2015 10:37:31’ – use ‘{Start}’ here
declare @EndDate datetime = ‘7/1/2015 15:37:31’ – use ‘{End}’ here

result all empty