Script help in JOIN of [Orders], [Payments} & [Tickets]

V5 Question
1

Have hit a bit of a snag in my PMS integration.

Am working on my last main script for transferring sales and payments for non room transfer tickets (Cash & Card over the bar)

I was using a script based on a start from @QMcKay

SELECT SUM(o.[Price] * o.[Quantity]) as TotalAmount
FROM [Orders] o JOIN [Payments] p ON p.[TicketId] = o.[TicketId] JOIN [MenuItems] m ON o.[MenuItemId] = m.[Id] JOIN [Tickets] t ON o.[TicketId] = t.[Id]
WHERE 1=1 AND m.[CustomTags] like '%'+@tagName+'%' AND p.[Name] != '@2' AND  t.[Date] > '@3'

It works fine so long as there is only one payment per ticket…
Once there is more than one payment per ticket the order rows are duplicated for each payment :frowning:

I need to know orders and payment type to be able to split out the room charge orders which will have already been transferred to the PMS…

At the minute the only way I can think to solve the problem is to set an order state to orders charged to room so can go from orders table only for sales post and payments table for payments post.

I understand why its happening but not sure how to get around it, any suggestions greatly appreciated.

As an example;

Ticket ID 7 was 2x1664 and 2xFosters with half Cash and half Card payments.

2

The query you provided above does not produce the type of output that you show.

In any case, you are going to need separate queries to do what you want, and piece the data back together.

  • get the Orders for the Ticket.
  • get the Payments for the Ticket
1 Like
3 
declare @tagName varchar(20) = 'Drink'
declare @date datetime = '2016-01-02'

SELECT 
  t.[Id]
, m.[Name] as [Item]
, o.[Quantity]
, o.[Price]
, SUM(o.[Price] * o.[Quantity]) as [TotalAmount]
FROM [Orders] o
 JOIN [MenuItems] m ON o.[MenuItemId] = m.[Id]
 JOIN [Tickets]   t ON o.[TicketId]   = t.[Id]
WHERE 1=1
 AND m.[CustomTags] like '%'+@tagName+'%'
 AND t.[Date] > @date
 GROUP BY t.[Id], m.[Name], o.[Quantity], o.[Price]


SELECT
 t.[Id]
 --, p.[Name]
 --, p.[Description]
 , pt.[Name] as [PaymentType]
 , sum(p.[Amount]) as [TotalPayment]
 FROM [Payments] p
 JOIN [PaymentTypes] pt on pt.[Id] = p.[PaymentTypeId]
 JOIN [Tickets]  t on t.[Id] = p.[TicketId]
WHERE 1=1
 AND t.[Date] > @date
 GROUP BY t.[Id], pt.[Name]
 ORDER BY t.[Id], pt.[Name]

Now you have 2 result sets that have a single thing in common: Ticket Id.

So for example, on Ticket 3002, I ordered 2 Barena and 2 Imperial. Ticket Total is 240.

Two Payments were made for 3002, Cash and Credit Card, both for the same amount of 120, for a Total of 240.

How you want to put that together for presentation is up to you.

1 Like
4

Sorry, yes the screenshot was from the second script where I was confirming that the first one was doing what I thought it was.