i have tried to make a report using sql to check orders that are voided after payment.
but i am not sure if this is how it suppose to be done
please correct me
many thanks
Template:
[Paid Tickets:1,2,2,2,2,2,2, 2]
Ticket No|Date|Time|Table|Order State|Menu Item|Amount
@@VoidedOrder
@@VoidTotal
Script:@@VoidedOrder
SELECT
T.TicketNumber,
min(convert(nvarchar,T.Date,105)) as Date,
min(convert(nvarchar,T.Date,108)) as Time,
min(TE.EntityName) as TableName,
βVoidβ,
min([MenuItemName]) as MenuItem,
min([Price]) as TotalAmount
FROM Orders as O
join Tickets as T
on O.TicketId =T.TicketNumber
join TicketEntities as TE
on TE.Ticket_Id = T.Id
Where [OrderStates] like β%Void%β And [TicketStates] like β%Paid%β
And Date > β{Start}β and Date < β{End}β
group by T.TicketNumber
order by Date,Time
Script:@@VoidTotal
SELECT βTotalβ,ββ,ββ,ββ,ββ,ββ, sum([Price])
FROM Orders as O
join Tickets as T
on O.TicketId =T.TicketNumber
Where [OrderStates] like β%Void%β And [TicketStates] like β%Paid%β
And T.Date > β{Start}β and T.Date < β{End}β
Output:
the total seem to be wrong