It came up not long ago that there seemed to be a problem with the Group Sales versus Total Sales numbers in DDMS. In short, Group Sales was showing a total just shy of double what Total Sales was. As I had just added in some new categories, I left it overnight, hoping dayend might fix it, but no such luck. I rebuilt the totals in +AJ, but the result was the same. What to do? Thinking on it, I never liked flipping between two tabs to see those numbers anyway, so maybe putting something together in PSyOPs would be the better fix.
At the same time, I thought this might be the right time to demonstrate how I develop queries for use in this framework. Unlike what I often see, I’m not here to put on the appearance that I’m a wizard-like developer. I know there are devs out there that are truly amazing and I admire them. However, I’m a bit of a caveman bashing two rocks together, so development takes some work.
With the thought in mind of putting together a Total Sales query first, I open up SQL Server Management Studio and have a look at the columns in SalesOrderJournal. I have a memory that SOJ has what I need, in that it includes a total per invoice. I also already know I’ll want it grouped by month, so I hunt down the datepart function and use that for my group by clause. I also know that I don’t want to wait to wade through the entirety of the journal, so I add a limit for order dates greater than Jan 1, 2013. Somewhat unintentionally, I’ve written the beginnings of a year to date report. Perfect, since that is one set of data DDMS shows in Group Sales and Total Sales.
I pick a steady customer to be sure there will be sales throughout the year and run it for the first time:
Click for full size
Hmm…lovely as it would be if they spent $182k in January, I know that’s quite wrong. Aside from that, it appears to be showing they didn’t spend nearly as much in March. I’m fairly sure that incorrect, as well. Not entirely sure what the issue is, I decide to dump out the entirety of the SalesOrderJournal table to see what it’s showing, while limiting it to March in hopes of maybe killing two birds with one stone:
Click for full size
And there we have it – because we’ve had to left join SalesOrderJournal with SalesOrderJournalDetail, it returns the total amount of the invoice on every line of the invoice. As you can see, there was an invoice 4 lines long with a grand total of 269.844. My initially mis-written query considers that invoice to be (269.844 * 4) 1079.536.
Recent Comments