Export 2015 Sales Data for tax reporting

Version 4
1

I need to export the data in V4 so that clients accountant can do some analytics, using MS SQL Express

The data needed is
Date
Sale
Sale Tax
Total Amount
Payment type (Card, Cash)

I tried to export Account Transactions, solves the purpose partially, there are no dates in this to filter the data for month, or year.
I will like to take the necessary tables in Excle or MS Access

2

You might struggle to get that in a single table.
Straight off the bat any tickets with multiple payments will cause duplicated rows…

SELECT t.[Id] AS [Ticket ID], 
t.[Date] AS [Ticket Date], 
t.[TotalAmount] AS [Ticket Total],
p.[Id] AS [Payment ID], 
p.[Date] AS [Payment Date],
p.[Name] AS [Payment Type],
p.[Amount] AS [Payment Amount]
FROM [Tickets] t JOIN [Payments] p ON t.[Id] = p.[TicketId]

In SQL Express will give a table close to what you’ve asked for.

Pasted image732×238 8.59 KB

But as I said you will get duplicate rows for multiple payments to one ticket, see ticket ID 1367 was ticket for 3.00 with 3x 1.00 payments but the ticket total shows 3.00 x3 times.

Sorry, forgot date filter;

SELECT t.[Id] AS [Ticket ID], 
t.[Date] AS [Ticket Date], 
t.[TotalAmount] AS [Ticket Total],
p.[Id] AS [Payment ID], 
p.[Date] AS [Payment Date],
p.[Name] AS [Payment Type],
p.[Amount] AS [Payment Amount]
FROM [Tickets] t JOIN [Payments] p ON t.[Id] = p.[TicketId]
WHERE t.[Date] >= '2015-01-01' AND t.[Date] <= '2015-12-31'

As for Tax, sorry am not too familiar with the way samba handles tax as not setup on my system

3

OK, found an old database with VAT setup.

Will have same issue as multiple payments as multiple Transaction Documents per Ticket

SELECT t.[Id] AS [Ticket ID], 
t.[Date] AS [Ticket Date], 
t.[TotalAmount] AS [Ticket Total],
p.[Id] AS [Payment ID], 
p.[Date] AS [Payment Date],
p.[Name] AS [Payment Type],
p.[Amount] AS [Payment Amount],
a.[Amount] AS [Transaction Amount],
a.[Name] AS [Transaction Desc]
FROM [Tickets] t JOIN [Payments] p ON t.[Id] = p.[TicketId] JOIN [AccountTransactions] a ON t.[TransactionDocument_Id] = a.[AccountTransactionDocumentId]
WHERE t.[Date] >= '2015-01-01' AND t.[Date] <= '2015-12-31'

You might be best to provide 3 separate tables;
Ticket Details

SELECT t.[Id] AS [Ticket ID], 
t.[Date] AS [Ticket Date], 
t.[TotalAmount] AS [Ticket Total]
FROM [Tickets] t 
WHERE t.[Date] >= '2015-01-01' AND t.[Date] <= '2015-12-31'

Payment Details

SELECT t.[Id] AS [Ticket ID], 
p.[Id] AS [Payment ID], 
p.[Date] AS [Payment Date],
p.[Name] AS [Payment Type],
p.[Amount] AS [Payment Amount]
FROM [Tickets] t JOIN [Payments] p ON t.[Id] = p.[TicketId]
WHERE t.[Date] >= '2015-01-01' AND t.[Date] <= '2015-12-31'

Account Transactions

SELECT t.[Id] AS [Ticket ID], 
a.[Amount] AS [Transaction Amount],
a.[Name] AS [Transaction Desc]
FROM [Tickets] t JOIN [AccountTransactions] a ON t.[TransactionDocument_Id] = a.[AccountTransactionDocumentId]
WHERE t.[Date] >= '2015-01-01' AND t.[Date] <= '2015-12-31

QMcKay might have some better ideas but the duplicate rows I believe is unavoidable given the structure of what your asking.

EDIT:
If you drop the payment breakdown you could show;
Ticket ID, Ticket Date, Tax and Total

SELECT t.[Id] AS [Ticket ID], 
t.[Date] AS [Ticket Date], 
a.[Amount] AS [Tax],
t.[TotalAmount] AS [Ticket Total]
FROM [Tickets] t JOIN [AccountTransactions] a ON t.[TransactionDocument_Id] = a.[AccountTransactionDocumentId]
WHERE t.[Date] >= '2015-01-01' AND t.[Date] <= '2015-12-31' AND a.[Name] = 'VAT Transaction'

You would need to change ‘VAT Transaction’ to your Transaction name for your tax…

1 Like