Sales Per Hour Report

QMcKay · May 19, 2015, 6:44pm

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.

pizzaeilat4 · May 19, 2015, 6:47pm

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

pizzaeilat4 · May 19, 2015, 6:49pm

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

QMcKay · May 19, 2015, 7:05pm

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.

pizzaeilat4 · May 19, 2015, 7:09pm

Thanks it’s working now.

pizzaeilat4 · May 19, 2015, 7:10pm

@QMcKay
is there a way can do it?

RickH · May 19, 2015, 7:56pm

i get this error on the Sales by Day report

QMcKay · May 19, 2015, 7:57pm

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

pizzaeilat4 · May 19, 2015, 8:06pm

Thanks it’s looking great

madiha · July 16, 2015, 12:22am

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 ?

madiha · July 23, 2015, 12:52am

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

QMcKay · July 23, 2015, 8:06pm

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:

pauln · July 26, 2016, 7:52am

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?

JTRTech · July 26, 2016, 10:57am

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

pauln · July 26, 2016, 12:48pm

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

JTRTech · July 26, 2016, 12:52pm

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.

pauln · July 26, 2016, 12:56pm

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?

JTRTech · July 26, 2016, 12:57pm

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

pauln · July 26, 2016, 1:00pm

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

JTRTech · July 26, 2016, 1:02pm

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}