hi
i want a report that display item sale report in a define time frame?
need help with the sql query
thanks
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…
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.
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