Sales , Item sold , Coustomer count report by hourly?

Version 4 V4 Question
1

Hi

I like to know how to create following report ?

Time Items Sales Ticket Drop
6 am 1 5.00 1 0 (Time =6 am Items=1 Sales =5.00 Ticket =1 Drop=0)
7 am 2 8.00 1 0 (Time =7 am Items=1 Sales =5.00 Ticket =1 Drop=0)
8 am 5 20.00 3 0 (Time =8 am Items =5 Sales = 20 Ticket=3 Drop=0)

11 pm 0 0 0 0 (Time=11 am Items= 0 Sales = 0 Ticket=0 Drop=0)

Time 6 am means sales bitween 5 am - 6 am
Items = any product employee sold
Sales = total hour sales
Ticket = total ticket created
Drop = money drop to the safe

2

Dear @allmixedup, I dont see how SAMBA will be able to show you the DROP field, since its not in the tables or tickets data structure.

If you look closely ate the two first reports in this post:

what you are asking for is a mix of both first reports!.
This afternoon I will b by the computer and if there is no much work will try to post it.

G.

3

It could be in the SQL if he created transactions for the drop this would be accounting and you can build reports for it.

1 Like
4

this SQL will return both count of tickets and total amount sold in between hours.
Qty of sold items is a little bit tricky… i have to do a join and count orders… will take a little bit longer

SELECT
      min(datename(dw,[Date])) as Dia,
      min(convert(nvarchar,Tickets.Date,105)) as Date,
      COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 17 AND 18 THEN 1 ELSE NULL END),
	  sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 17 AND 18 THEN TotalAmount ELSE 0 END),
      COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 19 AND 20 THEN 1 ELSE NULL END),
	  sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 19 AND 20 THEN TotalAmount ELSE 0 END),
      COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 21 AND 22 THEN 1 ELSE NULL END),
	  sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 21 AND 22 THEN TotalAmount ELSE 0 END),
      COUNT(CASE WHEN DATEPART(hour, [Date]) BETWEEN 23 AND 24 THEN 1 ELSE NULL END),
	  sum(CASE WHEN DATEPART(hour, [Date]) BETWEEN 23 AND 24 THEN TotalAmount ELSE 0 END)

    FROM Tickets

	WHERE Date > '{Start}' and Date < '{End}'

    GROUP BY Convert(nvarchar(20),Date,105)

G.

1 Like