Monthly Direct Sales Report

Pull the monthly direct order figures in MySQL for the prior month on 1st of the month (i.e. pull June sales figures on July 1st). You can download MySQL Workbench here:


http://www.google.com/url?q=http%3A%2F%2Fdev.mysql.com%2Fdownloads%2Fworkbench%2F&sa=D&sntz=1&usg=AFQjCNE1Sg93NoDkq9DmGaEYjuwbx5NVeQ


  • Scroll down, click “Download”
  • Click “No thanks, just start my download”
  • Install, then open and begin setup:
  • Name your group: RDS-PRO
  • Password (save to keychain): mec5EJukuzEh
  • Server: pro.cfgjkecfpua1.us-west-2.rds.amazonaws.com
  • Contact Bill: if you are unable to log in. He will need to allow your IP address through our server security settings.

Running a query:

1. Click the “Add Query” icon to begin a new query.



2. Paste the following query into the body:


  1. Use FundyCentral;
  2. set @startDate = "2020/07/01";
  3. set @endDate = "2020/07/31";
  4. set @vendorId = "5dac0b32-cc53-4669-952e-3ee3540b0bbc";
  5. DROP TEMPORARY TABLE IF EXISTS DirectPaymentsAndCredits;
  6. DROP TEMPORARY TABLE IF EXISTS DirectPayments;
  7. DROP TEMPORARY TABLE IF EXISTS DirectCredits;
  8. CREATE temporary table DirectPaymentsAndCredits
  9. SELECT
  10. o.VendorId,
  11. u.CompanyName,
  12. sum(OrderTotal) as 'All Transactons'
  13. FROM FundyCentral.`OrderTransaction` ot
  14. INNER JOIN FundyCentral.`Order` o
  15. ON ot.OrderId = o.Id
  16. INNER JOIN FundyCentral.`User` u
  17. ON u.id = o.VendorId
  18. WHERE
  19. (o.OrderType = 'ALBUM' OR o.OrderType = 'WALLART' OR o.OrderType = "CARDS") AND ot.Status = 'APPROVED'
  20. AND Date(ot.TransactionDate) between @startDate and @endDate
  21. group by o.vendorId;
  22. CREATE temporary table DirectPayments
  23. SELECT
  24. o.VendorId,
  25. u.CompanyName,
  26. sum(OrderTotal) as 'Payments'
  27. FROM FundyCentral.`OrderTransaction` ot
  28. INNER JOIN FundyCentral.`Order` o
  29. ON ot.OrderId = o.Id
  30. INNER JOIN FundyCentral.`User` u
  31. ON u.id = o.VendorId
  32. WHERE
  33. (o.OrderType = 'ALBUM' OR o.OrderType = 'WALLART' OR o.OrderType = "CARDS") AND ot.Status = 'APPROVED'
  34. AND Date(ot.TransactionDate) between @startDate and @endDate
  35. AND ot.TransactionType <> "CREDIT"
  36. group by o.vendorId;
  37. CREATE temporary table DirectCredits
  38. SELECT
  39. o.VendorId,
  40. u.CompanyName,
  41. sum(OrderTotal) as 'Credits'
  42. FROM FundyCentral.`OrderTransaction` ot
  43. INNER JOIN FundyCentral.`Order` o
  44. ON ot.OrderId = o.Id
  45. INNER JOIN FundyCentral.`User` u
  46. ON u.id = o.VendorId
  47. WHERE
  48. (o.OrderType = 'ALBUM' OR o.OrderType = 'WALLART' OR o.OrderType = "CARDS") AND ot.Status = 'APPROVED'
  49. AND Date(ot.TransactionDate) between @startDate and @endDate
  50. AND ot.TransactionType = "CREDIT"
  51. group by o.vendorId;
  52. select * from DirectPaymentsAndCredits;
  53. select * from DirectPayments;
  54. select * from DirectCredits;
  55. select  coalesce( (select payments from DirectPayments where VendorId=@vendorId), 0) - coalesce( (select credits from DirectCredits where vendorId=@vendorId), 0) as 'Total' from DirectPaymentsAndCredits where VendorId = @vendorId;


3. Update the dates to reflect the previous month (i.e. 2021/06/01 - 2021/06/30) and make sure you’re including all days for that month.

4. Click the lightning bolt icon to run your query.

5. Open the Direct Sales by Month report: https://docs.google.com/spreadsheets/d/1QUr9LFlnDUCNqVwSH8t8WFoKEOgGy0L4RqdRrOR12CA/edit?usp=sharing 

6. Enter the direct sales figures for each lab from the MySQL query into the report under that specific month.

7. Re-Share document with Todd ([email protected]), Jonathan Main, Lisa ([email protected]), and Andrew. Be sure to enter a brief message stating you’ve updated the document to reflect the previous month’s figures.