How do I sum a quantity relating to a specific end date within a range of dates?

Summary

This article covers how to sum a quantity relating to a specific end date within a date range.

Our example database data looks like the below:

Problem Scenario 

We are trying to generate a report that shows summation of quantity for a week end date. There is a date field in the database and we only want to show data for latest date of the week. Therefore we have used MAX(DATE). 

While we are 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 the current Cross tab report

1. Row is: PRODUCTNAME
2. Columns are: a) WEEKENDDATE b) MAX(DATE)
3. The metric is Sum(Qty)

The requirement is to generate a Cross tab report with produces the following output:

However, the output that is generated is the following:

The following query is generated: 

SELECT DISTINCT
    I.PRODUCTNAME,
    T.WEEKENDDATE,
    MAX(T.DATE) AS MAX_DATE,
    SUM(I.QTY) AS TOTAL_QTY
FROM INVENTORY AS I
INNER JOIN TIME AS T 
    ON I.TIMEID = T.TIMEID
GROUP BY
    T.WEEKENDDATE,
    I.PRODUCTNAME;

Solution

We were able to accomplish this by adding an extra table to the database containing just the 2 columns weekenddate and date:

Then add a Virtual table ("time") to your current View that only contains the max date for each week (by using the following SQL): 

SELECT 
   WEEKENDDATE, 
   MAX(DATE) AS MAX_DATE
FROM TIME;

(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 the same, keeping in mind to use the MAX_DATE field you created in your virtual table rather than the date field from your original table.