Order Voided after Payment Settled

[madiha]

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

[emre]

You don’t need SQL for this. Try

[Paid Tickets:1,2,2,2,2,2, 2]
>Ticket No|Date|Time|Table|Order State|Menu Item|Amount
{REPORT ORDER DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,OS.GStatus,O.MenuItemName,ON.TotalPrice:(TS.Status=Paid) and (OS.GStatus=Void)} 
>Total||||||{REPORT ORDER TOTAL:(TS.Status=Paid) and (OS.GStatus=Void)}

You’ll find more info here
http://sambapos.com/wiki/doku.php?id=custom_reporting

[madiha]

thankyou so much @emre you surely have made things simple and easier