June 1, 2017 at 9:43 pm
Ugh, I know that topic was ugly...Ill try to explain this as best I can...I am writing a report for a POS system
I have a query here that works perfectly:SELECT DISTINCT
Ticket.i_ticket_id,
TicketItem.s_item as Menu_Item,
TicketItem.s_qty,
ChoiceItem.i_ticket_item_id,
ChoiceItem.s_choice_name as Choice_Added,
Customers.cust_fullname as Customer,
Employees.s_name as Coach
FROM Ticket
INNER JOIN TicketItem on Ticket.i_ticket_id = TicketItem.i_ticket_id
INNER JOIN Customers on Ticket.i_customer_id = Customers.cust_id
INNER JOIN Employees on Employees.i_employee_id = Ticket.i_user_id
INNER JOIN ChoiceItem on ChoiceItem.i_ticket_item_id = TicketItem.i_ticket_item_id
WHERE Ticket.i_void_ticket_id is NULL and Ticket.b_closed = 1
ORDER BY Employees.s_name ASC
The only issue is, the menu Items are displaying multiple times if they have more than one Choice riding with them.
I wish I could display the output here, but I have added a screenshot to Add file...output.png
If if you look at the attached pic...the first two lines..(highlighted in Yellow)...see that is only one Menu item, but has 2 Choices riding with it...BUT the output is showing each as a different line item only becuase they have more than one Choice with it...
I would like the output, if the i_ticket_item_id are EQUAL to only show the Menu_item name only once...
So it would list:
Menu_Item:
Tea & Aloe With Shake
Choice_Added:
Mango Aloe shot
Pomegranate Green Tea Cold
NOT showing the Menu_Item twice, this will make the user believe they sold two (the i_ticket_item_id is the unique factor here) Ticket ID can be the same as you can have more than one menu item on a Ticket, correct?
Thanks...
Chris
June 1, 2017 at 10:27 pm
Use the standard ROW_NUMBER() OVER (PARTITION BY <grouping columns> ORDER BY <columns>) AS rn
to assign each duplicate a row_number and then filter for rn = 1.
June 1, 2017 at 11:14 pm
pietlinden - Thursday, June 1, 2017 10:27 PMUse the standard ROW_NUMBER() OVER (PARTITION BY <grouping columns> ORDER BY <columns>) AS rn
to assign each duplicate a row_number and then filter for rn = 1.
Thanks Ill give that a go...
Chris
June 1, 2017 at 11:22 pm
You won't need the DISTINCT if you use a windowing function to remove the duplicates too.
June 5, 2017 at 2:38 pm
pietlinden - Thursday, June 1, 2017 11:22 PMYou won't need the DISTINCT if you use a windowing function to remove the duplicates too.
Thanks. I have attached the query from this code:SELECT
Ticket.i_ticket_id as Ticket#,
TicketItem.s_item as Menu_Item,
TicketItem.s_qty as Quanity_Sold,
ChoiceItem.i_ticket_item_id as Ticket_Item_ID,
ChoiceItem.s_choice_name as Choice_Added,
Customers.cust_fullname as Customer,
Employees.s_name as Employee
FROM Ticket
INNER JOIN TicketItem on Ticket.i_ticket_id = TicketItem.i_ticket_id
INNER JOIN Customers on Ticket.i_customer_id = Customers.cust_id
INNER JOIN Employees on Employees.i_employee_id = Ticket.i_user_id
INNER JOIN ChoiceItem on ChoiceItem.i_ticket_item_id = TicketItem.i_ticket_item_id
WHERE Ticket.i_void_ticket_id is NULL and Ticket.b_closed = 1 and dt_close_time between '2017-06-01 04:11:10.780' and '2017-06-05 23:11:10.780'
ORDER BY Employees.s_name ASC
Again, this works, but I need only one entry listed if Ticket.i_ticket_id as Ticket#,
TicketItem.s_item as Menu_Item,
TicketItem.s_qty as Quanity_Sold,
ChoiceItem.i_ticket_item_id as Ticket_Item_ID,
Customers.cust_fullname as Customer,
Employees.s_name as Employee
These fields are all the same...if ChoiceItem.i_ticket_item_id is different, then display as a separate menu item...
I am having trouble getting this up and running. I have attached the output of the original code.
Any nudge is appreciated.
June 5, 2017 at 3:53 pm
pietlinden - Thursday, June 1, 2017 11:22 PMYou won't need the DISTINCT if you use a windowing function to remove the duplicates too.
SELECT
Ticket.i_ticket_id as Ticket#,
TicketItem.s_item as Menu_Item,
ChoiceItem.s_choice_name as Choice_Added,
TicketItem.s_qty as Quanity_Sold,
ChoiceItem.i_ticket_item_id as Ticket_Item_ID,
Customers.cust_fullname as Customer,
Employees.s_name as Employee,
ROW_NUMBER() OVER (PARTITION BY Ticket.i_ticket_id , TicketItem.s_item , TicketItem.s_qty , ChoiceItem.i_ticket_item_id , Customers.cust_fullname ORDER BY Employees.s_name) AS RowNumber
FROM Ticket
INNER JOIN TicketItem on Ticket.i_ticket_id = TicketItem.i_ticket_id
INNER JOIN Customers on Ticket.i_customer_id = Customers.cust_id
INNER JOIN Employees on Employees.i_employee_id = Ticket.i_user_id
INNER JOIN ChoiceItem on ChoiceItem.i_ticket_item_id = TicketItem.i_ticket_item_id
WHERE Ticket.i_void_ticket_id is NULL and Ticket.b_closed = 1 and dt_close_time between '2017-06-01 04:11:10.780' and '2017-06-05 23:11:10.780'
ORDER BY Employees.s_name
This gets me very close to what I need for this report to read correctly. The only part is omitting, from RowNumber => 2, all the data EXCEPT 'Choice_Added'
See the screenshot attached. So for Ticket# 128808 I ONLY want to see the first row (RowNumber 1) and then for (RowNumber => 2) only display the 'Choice_Added' name
What am I missing?? 🙁
Chris
June 5, 2017 at 5:48 pm
Use an IF statement to determine if the value is 'Choice_Added', and overwrite the RowNumber value. Then filter on the very outside to eliminate the values you don't want.
Any chance you could post some consumable data (CREATE TABLE, INSERT scripts) so that we can all test this stuff out?
Better data = better answers!
June 5, 2017 at 8:36 pm
pietlinden - Monday, June 5, 2017 5:48 PMUse an IF statement to determine if the value is 'Choice_Added', and overwrite the RowNumber value. Then filter on the very outside to eliminate the values you don't want.Any chance you could post some consumable data (CREATE TABLE, INSERT scripts) so that we can all test this stuff out?
Better data = better answers!
I am not sure how to do that, elegantly....(any tips are welcome) I know how to give you each tables metadata and the columns etc, I just do not know how to place them here so they format correctly and look usable.
Just give you CREATE TABLE with the datatypes?
June 5, 2017 at 8:42 pm
I was thinking in my head, an IF statement as well..something like..
IF RowNumber > 1 and Ticket.i_ticket_id are =, and TicketItem.s_item are =, and ChoiceItem.i_ticket_item_id are = then only display ChoiceItem.s_choice_name
June 6, 2017 at 12:03 am
Can't run a SQL statement against a picture. If you want help, provide consumable data.
June 6, 2017 at 11:17 am
pietlinden - Tuesday, June 6, 2017 12:03 AMCan't run a SQL statement against a picture. If you want help, provide consumable data.
I check the form guidelines on how to post these tables and data correctly...thank you for checking back in.
Cant I just Post the DB itsself? I can purge most of the data except a weeks worth, that would give everyone something to work with...
June 6, 2017 at 11:55 am
Why not just the table stuff you posted? Simple create table script, maybe a dozen inserts. Done.
June 6, 2017 at 12:20 pm
pietlinden - Tuesday, June 6, 2017 11:55 AMWhy not just the table stuff you posted? Simple create table script, maybe a dozen inserts. Done.
SELECT
Ticket.i_ticket_id as Ticket#,
TicketItem.s_item as Menu_Item,
ChoiceItem.s_choice_name as Choice_Added,
TicketItem.s_qty as Quanity_Sold,
ChoiceItem.i_ticket_item_id as Ticket_Item_ID,
Customers.cust_fullname as Customer,
Employees.s_name as Employee,
ROW_NUMBER() OVER (PARTITION BY Ticket.i_ticket_id , TicketItem.s_item , TicketItem.s_qty , ChoiceItem.i_ticket_item_id , Customers.cust_fullname ORDER BY Employees.s_name) AS Row#
FROM Ticket
INNER JOIN TicketItem on Ticket.i_ticket_id = TicketItem.i_ticket_id
INNER JOIN Customers on Ticket.i_customer_id = Customers.cust_id
INNER JOIN Employees on Employees.i_employee_id = Ticket.i_user_id
INNER JOIN ChoiceItem on ChoiceItem.i_ticket_item_id = TicketItem.i_ticket_item_id
WHERE Ticket.i_void_ticket_id is NULL and Ticket.b_closed = 1 and dt_close_time between '2017-06-05 04:11:10.780' and '2017-06-06 23:11:10.780'
ORDER BY Employees.s_name ASC
INSERT INTO
(Ticket.i_ticket_id, TicketItem.s_item, ChoiceItem.s_choice_name, TicketItem.s_qty, ChoiceItem.i_ticket_item_id, Customers.cust_fullname, Employees.s_name)
VALUES
(129128, Cookies n Cream, Peach Tea Cold, 1, 577213, Brennan Hannah, Aubrianna Majewski, 1
129128, Cookies n Cream, Mango Aloe Shot, 1, 577213, Brennan Hannah, Aubrianna Majewski, 2
129128, Cookies n Cream, Peach Tea Cold, 1, 577214, Brennan Hannah, Aubrianna Majewski, 1
129128, Cookies n Cream, Mango Aloe Shot, 1, 577214, Brennan Hannah, Aubrianna Majewski, 2
129129, Cookies n Cream, Lemon Tea Cold, 1, 577215, Turcotte Riley, Aubrianna Majewski, 1
129129, Cookies n Cream, Mango Aloe Shot, 1, 577215, Turcotte Riley, Aubrianna Majewski, 2
129133, Liftoff, Orange Liftoff, 1, 577220, LATTUFE LARISSA, Aubrianna Majewski, 1
129134, PB Cheesecake, Chai Tea Hot, 1, 577221, Goncalo Alycia, Aubrianna Majewski, 1
129143, Dark Ali, Lemon Tea Cold, 1, 577232, Simmons Jesse, Aubrianna Majewski, 1
129143, Dark Ali, Cranberry Aloe Shot, 1, 577232, Simmons Jesse, Aubrianna Majewski, 2
129143, Liftoff, Pomegrante Liftoff, 1, 577233, Simmons Jesse, Aubrianna Majewski, 1
129144, Coconut Cream Pie, Lemon Tea Cold, 1, 577234, schuster cindy, Aubrianna Majewski, 1
129144, Coconut Cream Pie, Mango Aloe Shot, 1, 577234, schuster cindy, Aubrianna Majewski, 2)
If
Ticket.i_ticket_id = Ticket.i_ticket_id and TicketItem.s_item = TicketItem.s_item and ChoiceItem.i_ticket_item_id = ChoiceItem.i_ticket_item_id
then
only display ChoiceItem.s_choice_name and Row#
June 6, 2017 at 12:41 pm
I'm turning into Joe Celko... time for a break.
Please help us to help you and read this article.
June 6, 2017 at 1:13 pm
pietlinden - Tuesday, June 6, 2017 12:41 PMI'm turning into Joe Celko... time for a break.
Please help us to help you and read this article.Forum Etiquette: How to post data/code on a forum to get the best help
That's what i was needing...thanks. Ill submit it correctly after reading...
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply