Sales Per Hour Report

SQL is the only way to create that report in v4. It is a good report. I will put something together.

1 Like

Thanks @QMcKay
I will be waiting

Need to get learning some sql, can’t believe you get a report like that from such a short bit of code. :slight_smile:

This won’t be “short” in SQL.

I need to step out until tomorrow, but I started some code - saving it here until later…

declare @Start datetime = '2015-01-10T00:00:00.000'
declare @End datetime = '2015-01-10T23:59:59.000'
declare @Hour int = 0

declare @tbl_Sales table
    (
    [ID]  INT IDENTITY(1,1) NOT NULL 
   ,[Hour] varchar(5)
   ,[Amount] money
    )

WHILE @Hour < 24
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]
    FROM [dbo].[Tickets]
    WHERE [Date]>=@Start AND [Date]<=@End
SET @Hour = @Hour + 1
END

SELECT [Hour], [Amount] FROM @tbl_Sales
3 Likes

@QMcKay I think GROUP BY does something similar?

Select Datepart(hour,Date) as Hour, Sum(TotalAmount) from Tickets
Group by Datepart(hour,Date)
Order by Datepart(hour,Date)

PS: Original date report sample used case method to display pre-defined time range (eg 9-12) totals as table columns.

1 Like

Yes, thank you @emre - that works really well for a quick look. It is nice and short, and does the job.

The thing I personally don’t like about it though is that hours are skipped (not displayed) if there are no sales within the hour, so it leaves holes in your data chart (missing rows). I would rather it display all requested hours within a time-frame, even if the sale amount within the hour is 0.

Here is a much shorter version using a WHILE loop and a @temp Table …

declare @Start datetime = '2015-01-10T00:00:00.000'
declare @End datetime = '2015-01-10T23:59:59.000'
declare @Hour int = 0

declare @tbl_Sales table
    (
    [ID]  INT IDENTITY(1,1) NOT NULL 
   ,[Hour] varchar(5)
   ,[Amount] money
    )

WHILE @Hour < 24
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]
    FROM [dbo].[Tickets]
    WHERE [Date]>=@Start AND [Date]<=@End
SET @Hour = @Hour + 1
END

SELECT [Hour], [Amount] FROM @tbl_Sales

Output:

2 Likes

Ok, I missed something obviously, excuse my ignorance.
If that’s the same report in sql what code was @emre post in?

Presumably this is hard coded samba report based code rather than sql then?

There are two ways to generate reports. You can use Report Tags which is what Emre showed or you can use SQL and call it with a handler like @@sql

More info on the report tags:

http://sambapos.com/wiki/doku.php?id=custom_reporting_tags

Report tags is easier if you do not know SQL. SQL technically can do more because its not bound by tags that need to be implemented. Report tags may be faster for some situations as well but typically there is no difference.

@QMcKay is seasoned with SQL so he can do some impressive reports. I prefer the Report Tags and Emre is very good at implementing tags as we need them. The tags implementation is slowly getting very advanced you can almost do anything you need with the tags without needing to know SQL.

As you can see the tags also allow you to simplify the report syntax. Emre basically did a report like what Q was doing with a few lines of code vs pages of code SQL might require.

BTW when I say SQL what i really mean is a SQL query

3 Likes

Thanks, well explained.
It obviously wouldn’t heart to learn a bit of sql but report tags will do for now :slight_smile:

Here we go … this will work in v4 or v5.

Set the first 2 declarations to use ‘{Start}’ and ‘{End}’ respectively if you want to use this SQL in a Report.

Set the next 2 declarations for the First and Last hour that you want results for. These could also be parameterized as @1 and @2 if you are going to feed the values into the SQL via a Report.

declare @StartDate datetime = '2015-01-02T00:00:00.000' -- use '{Start}' here
declare @EndDate datetime = '2015-01-02T23:59:59.000'   -- use '{End}' here
declare @StartHour int = 6 -- set the First Hour you want to track or use @1 parameter
declare @EndHour int = 22  -- set the Last Hour you want to track or use @2 parameter

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

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

Output:


##Making a Report##

Report Template:

[Sales Per Hour: 15, 25, 15, 20, 20]
>Hour|Sales|Tx|AvgTx|%Sales
@@SalesPerHour:6,22


@@SalesPerHour (SQL Script in Automation > Scripts)

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

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

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 [Hour], [Amount], [Tickets], [AvgTx], [Percent] FROM @tbl_Sales


Output:

1 Like

Uh lol… I never thought that. Great point.

Another difference is SQL runs server side, Custom Reports fetches and process data on client side. Learning SQL will open new opportunities as you won’t limit yourself with custom reports language but for most common cases Custom Reports will try to offer simple solutions.

2 Likes

Could I get some help on showing void reports? V4 actually showed the user that voided however V5 only shows really simple void report.

Id really love it if I could get user that voided, time of void, item, quantity, and total.

@eddhasaj that report should be fairly easy to do with Report Tags. Did you not look over the Custom Report Tags post I linked to you in your other post about this?

I looked over it briefly but have been kinda busy lately and haven’t had the time to figure out how custom reports work. I figured alot of it out but was confused with the expressions. I tried a few things a little while back and it was’t working so I never gave it another try :confused:

Could you please put something together for me if you find the time?

@kendash

Let me see what I can do. Others might find it useful.

PS. I was hoping you would dig in deeper it will help you out later :stuck_out_tongue:

Try this:

[Voids Detail:1,1,1,1,1]

>User|Time|Item|Quantity|Total

{REPORT ORDER DETAILS:O.User,O.Time,O.MenuItemName,O.Quantity,O.TotalPrice:(OS.GStatus=Void)}

Is that what your looking for?

1 Like

Haha thats what I was planning, and will soon just very busy atm. Im in college still and currently taking 6 spring courses simultaneously… so yeah I wish I could get a degree in sambapos instead lol.

Thanks your the best! @kendash

Thats exactly what I wanted. I will learn from this too, alot easier then I thought!

The report tags are very easy to use once you understand the structure.

PS. you could easily add that to the Work Period report.

Thanks guys this is great
It’s exactly what i neeb. :laughing:

Sales per Day of Week

@@SalesPerDay (SQL in Automation > Scripts)

declare @StartDate datetime = convert(varchar(25),'{Start}',126)
declare @EndDate datetime = convert(varchar(25),'{End}',126)
declare @StartDay varchar(10) = @1 -- set start day of week or use @1 parameter

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

declare @Day varchar(10) = ''
declare @TotalSales money = 0.00
declare @DayCount int = 1

declare @tbl_Days table
(
[ID]  INT IDENTITY(1,1) NOT NULL 
,[Day] varchar(5)
)

declare @tbl_Sales table
(
[ID]  INT IDENTITY(1,1) NOT NULL 
,[Day] varchar(5)
,[Amount] decimal(9,2)
,[Tickets] int
,[AvgTx] decimal(5,2)
,[Percent] decimal(5,2)
)

IF @StartDay='Sun' INSERT INTO @tbl_Days ([Day]) VALUES ('Sun'),('Mon'),('Tue'),('Wed'),('Thu'),('Fri'),('Sat')
IF @StartDay='Mon' INSERT INTO @tbl_Days ([Day]) VALUES ('Mon'),('Tue'),('Wed'),('Thu'),('Fri'),('Sat'),('Sun')
IF @StartDay='Tue' INSERT INTO @tbl_Days ([Day]) VALUES ('Tue'),('Wed'),('Thu'),('Fri'),('Sat'),('Sun'),('Mon')
IF @StartDay='Wed' INSERT INTO @tbl_Days ([Day]) VALUES ('Wed'),('Thu'),('Fri'),('Sat'),('Sun'),('Mon'),('Tue')
IF @StartDay='Thu' INSERT INTO @tbl_Days ([Day]) VALUES ('Thu'),('Fri'),('Sat'),('Sun'),('Mon'),('Tue'),('Wed')
IF @StartDay='Fri' INSERT INTO @tbl_Days ([Day]) VALUES ('Fri'),('Sat'),('Sun'),('Mon'),('Tue'),('Wed'),('Thu')
IF @StartDay='Sat' INSERT INTO @tbl_Days ([Day]) VALUES ('Sat'),('Sun'),('Mon'),('Tue'),('Wed'),('Thu'),('Fri')

WHILE @DayCount < 8
BEGIN
    SELECT @Day = [Day] FROM @tbl_Days WHERE [ID]=@DayCount
    INSERT INTO @tbl_Sales
    SELECT
      @Day as [Day]
    , SUM(CASE WHEN (left(datename(dw,[Date]),3)=@Day)
               THEN [TotalAmount]
               ELSE 0
               END) as [Amount]
    , COUNT(CASE WHEN (left(datename(dw,[Date]),3)=@Day)
                 THEN 1
                 ELSE null
                 END) as [Tickets]
    ,0,0
    FROM [dbo].[Tickets]
    WHERE [Date]>=@StartDate AND [Date]<=@EndDate

    SET @DayCount = @DayCount + 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 [Day]='TTL')

UPDATE @tbl_Sales SET
 [AvgTx] = (CASE WHEN [Tickets]=0 THEN 0 ELSE [Amount]/[Tickets] END)
,[Percent] = [Amount]/@TotalSales * 100

SELECT [Day], [Amount], [Tickets], [AvgTx], [Percent] FROM @tbl_Sales

Report:

[Sales Per Day: 15, 25, 15, 20, 20]
>Day|Sales|Tx|AvgTx|%Sales
@@SalesPerDay:'Sun'

Result:

3 Likes

@QMcKay how can we make the sales to 2 decimal places in your sales by hour and sales by day reports you have made instead of .0000?