This article covers how to sum a quantity relating to a specific end date within a date range.
My database data looks like below:
WEEKENDDATE DATE PRODUCTNAME QTY
2011-07-03 2011-07-01 PRODUCT A 221
2011-07-03 2011-07-01 PRODUCT B 116
2011-07-03 2011-07-02 PRODUCT A 221
2011-07-03 2011-07-02 PRODUCT B 116
2011-07-10 2011-07-08 PRODUCT A 265
2011-07-10 2011-07-08 PRODUCT B 111
2011-07-17 2011-07-15 PRODUCT A 221
2011-07-17 2011-07-15 PRODUCT B 116
My requirement is to generate a Cross tab report with produces the following output:
Sum Qty 03-07-2011 10-07-2011 17-07-2011-WEEK
PRODUCTNAME 02-07-2011 08-07-2011 15-07-2011-DATE
PRODUCT A 221 265 221
PRODUCT B 116 111 116
However, the output that I get is following:
Sum Qty 03-07-2011 10-07-2011 17-07-2011
PRODUCTNAME 02-07-2011 08-07-2011 15-07-2011
PRODUCT A 442 265 221
PRODUCT B 232 111 116
Background of the issue: I am trying to generate a report that shows summation of quantity for a week end date. I have a date field in my database and I only want to show data for latest date of the week therefore I have done MAX(DATE). Though am using sum of Quantity on Max of date for that particular week, the output in the report still shows sum of quantity for all the dates for the same week.
On my Cross tab report
1)Row is :PRODUCTNAME,
2)Columns are: a) WEEKENDDATE b) MAX(DATE)
3)METRIC is Sum(Qty)
Following query is getting generated:
INNER JOIN TIME
INVENTORY.TIMEID = TIME.TIMEID
We were able to accomplish this by adding an extra table to your database containing just the 2 columns weekenddate and date:
(by the way, if you're not sure about Virtual Tables please look here )
Then join your original table to the new "time" table with an inner join on the date field.
And from there the rest is as you did it, keeping in mind to use the MaxDate field you created in your virtual table rather than the date field from your original table.