Hi SQL query gurus,
Can anyone help with the following SQL query? I need to pull Total Sales per server for one Category of products called BAR. Second column should times Total Sales by 10%.
BAR Server Sales Totals:
Server name: Total BAR Sales x10%
Joe $100.00 $10.00
Susan $200.00 $20.00
Jackie $300.00 $30.00
Thank you in advance.
This question may not be related to what you are asking… I am just curious. What are you using that 10% for?
How do you store servers? Are they users?
It should be something like that.
select
O.CreatingUserName as Server,
Convert(nvarchar,Cast(Sum(O.Price*O.Quantity) as Money),1) Total,
Convert(nvarchar,Cast(Sum(O.Price*O.Quantity*0.1)as Money),1) Share
from Orders as O
inner join MenuItems as MI on MI.Id = O.MenuItemId
where MI.GroupCode = 'Bar'
group by O.CreatingUserName
I don’t have suitable data atm for testing but I’m sure you’ll be able to correct that.
Hi @emre
The query does not seem to produce the right results.
Query:
select
O.CreatingUserName as Server,
Convert(nvarchar,Cast(Sum(O.PriceO.Quantity) as Money),1) Total,
Convert(nvarchar,Cast(Sum(O.PriceO.Quantity*0.1)as Money),1) Share
from Orders as O
inner join MenuItems as MI on MI.Id = O.MenuItemId
where
MI.GroupCode = ‘Beers’
or MI.GroupCode = ‘Red Wine’
or MI.GroupCode = ‘White Wine’
or MI.GroupCode = ‘Brandy’
or MI.GroupCode = ‘Gin’
or MI.GroupCode = ‘Rum’
or MI.GroupCode = ‘Scotch’
or MI.GroupCode = ‘Tequila’
or MI.GroupCode = ‘Vodka’
or MI.GroupCode = ‘Whiskey’
or MI.GroupCode = ‘Cordials’
or MI.GroupCode = ‘Mixed Drinks’
or MI.GroupCode = ‘Open Bar’
group by O.CreatingUserName
Template:
[BarTipOut:1,1,1]
Server Name|Total Sales|Bar Tip
@@BarTipOut
Results:
Thank you again for your help.
That produces whole results. I think you’re expected to see it for a single work period so you should also include date filter in where clause.
select
O.CreatingUserName as Server,
Convert(nvarchar,Cast(Sum(O.Price*O.Quantity) as Money),1) Total,
Convert(nvarchar,Cast(Sum(O.Price*O.Quantity*0.1)as Money),1) Share
from Orders as O
inner join MenuItems as MI on MI.Id = O.MenuItemId
where MI.GroupCode = 'Bar' and CreatedDateTime > '{Start}' and CreatedDateTime < '{End}'
group by O.CreatingUserName
Also you can exclude voids
select
O.CreatingUserName as Server,
Convert(nvarchar,Cast(Sum(O.Price*O.Quantity) as Money),1) Total,
Convert(nvarchar,Cast(Sum(O.Price*O.Quantity*0.1)as Money),1) Share
from Orders as O
inner join MenuItems as MI on MI.Id = O.MenuItemId
where MI.GroupCode = 'Bar' and CreatedDateTime > '{Start}' and CreatedDateTime < '{End}' and DecreaseInventory = 1
group by O.CreatingUserNameI added ’ after CreatedDateTime < '{End}. It is still producing the same results. It does not filter based on date.
Additionally, could something else be wrong with the query. User Sales and Bar Tip Out report do not match. Thank you.
I’ve fixed a typoo on SQL scripts. It should be CreatedDateTime < '{End}' not CreatedDateTime < '{End}
… also it can’t produce same results when there is a syntax error on script. Maybe you should double check that.
User Sales is the WPR right? It is not a custom report… correct? You are selecting Just the Work Period Date in the date selecting at top right? Also User Sales from WPR is picking up ALL Menu items… are those items in the script the ONLY items those servers sold?
If they rang up food or anything not in your script… it will be reflected on User Sales from WPR but it wont show in your script because you told it exactly what to calculate for sales.
The ONLY way User Sales from WPR and your script will match exactly… is if those items in your script are the ONLY items each user sells.
I have feeling they sell more than just those drinks listed in your script 
EDIT: You wanted Tips to be calculated off Bar sales only right? If so your script is working just fine.
You would not compare it to User Sales from WPR that would not be accurate.
You could compare it and subtract the Sales from your Bar report from the WPR User Sales and do some math to get your % of Bar sales vs Total sales.
To sum it up… it looks like your script is running as you intended it… you are just reading the WPR:User Sales wrong
I am so sorry I got both of you confused. That’s what happens when I am multitasking. 
I will try to explain better why Totals do not seem to match.
I just pulled new numbers. All numbers are from current WPR started at 10:29 AM.
Image below shows Item Sales for current WPR. Bar Items are Red Wine, White Wine, Beer.
Image below is the same WPR. In this report, I would expect Servers(Users) with Bar Item Sales only.
Totals below do not seem to match WPR above.
Please let me know if something still does not make sense.
Thanks you.
OK I still believe we have something to fix in SQL Script. If you need to check multiple categories with or you should use brackets to define scope for or.
For example
Where PC = 'X' AND DATE > Y
means Product code should be x AND date should be greater than Y.
Now I want to check if PC = X or PC = Z
Where PC = 'X' OR PC = 'Y' AND DATE > Z
That will make Date Constraint useless. We should use parenthesis to define a priority. So it should be
Where (PC = 'X' OR PC = 'Y') AND DATE > Z
So we should add multiple groups in brackets.
select
O.CreatingUserName as Server,
Convert(nvarchar,Cast(Sum(O.Price*O.Quantity) as Money),1) Total,
Convert(nvarchar,Cast(Sum(O.Price*O.Quantity*0.1)as Money),1) Share
from Orders as O
inner join MenuItems as MI on MI.Id = O.MenuItemId
where
(MI.GroupCode = 'Bar' OR MI.GroupCode = 'Open Bar')
and CreatedDateTime > '{Start}' and CreatedDateTime < '{End}'
and DecreaseInventory = 1
group by O.CreatingUserName