MYOB Integration: Extracting total sales data
Here’s a couple of examples you might find helpful if integrating with MYOB.
The following query extracts the total sales from July 2011 to June 2012.
SELECT SUM(TotalTax + TotalLines – TotalDiscounts) As TotalSales from Sales WHERE InvoiceDate BETWEEN ‘2011-07-01’ AND ‘2012-06-30’
+——————+
| TotalSales |
+——————+
| 29223.5 |
+——————+
You can also group them by Sales Person.
SELECT Cards.Name As SalesPerson, SUM(TaxExclusiveFreight + TotalTax + TotalLines – TotalDiscounts) As TotalSales from Sales INNER JOIN cards on Sales.SalespersonID = Cards.CardRecordID WHERE InvoiceDate BETWEEN ‘2011-07-01’ AND ‘2012-06-30’ GROUP BY Cards.Name
+———————–+————————+
| SalesPerson | TotalSales |
+———————–+————————+
| Deborah Brook | 14520 |
+———————–+————————+
| Donal English | 4503.5 |
+———————–+————————+
| Terry Ooi | 10200 |
+———————–+————————+