Sales Per Hour Report

In the Script, within the declaration of @tbl_Sales, change this line:

[Amount] money

to this:

[Amount] decimal(9,2)

I have updated the scripts above to contain this change.

1 Like

also can we display hours like this? 02:00,14:00
Or 2am 3pm?
can we display 1am,2am after midnight?

@QMcKay
im having this error when i try Sales per Day of Week

Check your Report - I had a typo in there. It should be:

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

NOT:

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

There should be a colon before ‘Sun’ instead of a comma.

1 Like

Thanks it’s working now.

@QMcKay
is there a way can do it?

i get this error on the Sales by Day report

For 24-hour format such as

06:00
07:00
...
13:00
14:00

Change the last SELECT statement from this:

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

to this:

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
2 Likes

Thanks it’s looking great

instead of declaring specific time for any custom report…why cant we enter time ie a long date where we are defining date period ie short period in reports

if short date —> report retrieved day wise
if long date --> report retrieved by hour defined

easy for end user else person who have sql knowledge only will prefer the scripts

is there a possibility for entering custom time along with custom date ?

how can we amend @QMcKay query to set time frame instead of getting hourly report.
like if i want to see sale from
11 3
3 5
5 7
7 9

Replace the last SELECT statement with this:

SELECT
'11-3' as [Hour]
, sum([Amount]) as [Amount], sum([Tickets]) as [Tickets], sum([AvgTx]) as [AvgTx], sum([Percent]) as [Percent]
FROM @tbl_Sales
WHERE isnumeric([Hour])=1 and [Hour]>=11 and [Hour]<15
UNION
SELECT
'3-5' as [Hour]
, sum([Amount]) as [Amount], sum([Tickets]) as [Tickets], sum([AvgTx]) as [AvgTx], sum([Percent]) as [Percent]
FROM @tbl_Sales
WHERE isnumeric([Hour])=1 and [Hour]>=15 and [Hour]<17
UNION
SELECT
'5-7' as [Hour]
, sum([Amount]) as [Amount], sum([Tickets]) as [Tickets], sum([AvgTx]) as [AvgTx], sum([Percent]) as [Percent]
FROM @tbl_Sales
WHERE isnumeric([Hour])=1 and [Hour]>=17 and [Hour]<19
UNION
SELECT
'7-9' as [Hour]
, sum([Amount]) as [Amount], sum([Tickets]) as [Tickets], sum([AvgTx]) as [AvgTx], sum([Percent]) as [Percent]
FROM @tbl_Sales
WHERE isnumeric([Hour])=1 and [Hour]>=19 and [Hour]<21

Output:

1 Like

Hi @QMcKay sorry for the bump but how do you format the output of Report Scripts i.e put "$’ signs in front of the values?

I also be interested if we could use the Export Features as well?

Hehe still some advantages to the good old £ GBP - even if it is at a crazy low value atm.

Id imagine you would want to add the ‘prefix’ in the report rather than the sql itself but guess depends on you point of view, and have to escape it or wrap it some how.

If in SQL quick search showed a posible solition;

SELECT PayerDate, '$' + CAST(PaymentAmount AS VARCHAR(15)) PaymentAmount
FROM   Payments
1 Like

Oh Hey @JTRTech - how you doing, thanks I will give it a try.

Doing well thanks, been busy with hotel refub stuff.
Good to see your system launch is going well, good to hear after all the time you put in to it.

Think bit to remember is this will be ‘strigifying’ the value so you wont be able to do any calculations with the values but dont think you will need to looking at what your doing.

The other way presumably would be in the report itself when formatting the columns.

Thanks JTRTech - yea launch was amazing, had several staff so “over the moon” with POS. I think at one stage I “may of” had a tear in my eye? Naaa was dust…

I am cool on the report formatting but the results come back from a @@REPORT SCRIPT so there is no line formatting? Where do you stick the :{0}|${1} when there is no line?

Not sure LOL, I kind of trial and errored it when doing the group separated item sales report looking at other examples.

Yea cannot find any on the forum, most examples nobody has cared about the format :unamused: <- I really like that face “unamused”, pretty much summarises my mornings after a hard night in the Samba PJ’s… hahaha

That goes in the {REPORT SQL DETAILS: tag, could you put your SQL into that?

Here is my price list report;

[Price List:5 ,3,3, 1, 2]
>Product | Portion | Group | Band | Price
{REPORT SQL DETAILS:
	SELECT
		product.[GroupCode] AS [Group],
		product.[Name] AS [Product],
		portion.[Name] AS [Portion],
		price.[PriceTag] AS [Price Band],
		price.[Price] AS [Price]
	FROM [MenuItems] product 
	LEFT JOIN [MenuItemPortions] portion 
	ON product.[Id] = portion.[MenuItemId] 
	LEFT JOIN [MenuItemPrices] price
	ON price.[MenuItemPortionId] = portion.[Id]
	ORDER BY product.[GroupCode]
	:F.Product,F.Portion,F.Group,F.Price Band,F.Price::{0} |{1}| {2}| {3}| £{4}
}

Just checked and works fine just like;

:F.Product,F.Portion,F.Group,F.Price Band,F.Price::{0} |{1}| {2}| {3}| ${4}