Running Total for Current & Previous Year on weekly basis

Question

This query provides Year to date numbers for both Price and Square Feet of the current year and the previous year to date. This is more like the Running Total of the current Year and the Previous year with respect to the weeks in this case from 1 through 7 and so on..... (week 7th of 2017 ended on 02/19/2017) of the current year and the previous year(week 7th of 2016 ended on 02/22/2016). The reason why I am using subqueries is because this is the only way I know to get around this situation. And of course if you think there is a shorter, viable alternative of executing this query, please advice.

Actual_Sale_Date holds data on all of the seven days of the week but we cut off on Sunday that is why 2/22/2016 (Sunday ending 7th week of 2016) and 2/19/2017 (Sunday ending 7th week of 2017).

I tried "Actual_Sale_Date" = date_trunc('week', now())::date - 1 this function only returns the previous week data ending on the passed Sunday. I took a look at interval since dateadd does not exist in postgresql but could not get my ways around with it.

My query:

select (money(Sum("Price") / COUNT("Price"))) as "Avg_Value YTD",
Round(Avg("Price"/"Sq_Ft"),+2) as "Avg_PPSF YTD",

(select 
(money(Sum("Price") / COUNT("Price")))  from allsalesdata
where "Actual_Sale_Date" >=  '01/01/2016' AND "Actual_Sale_Date" < '02/22/2016'
and "Work_ID" = 'SO') AS "Last Year at this time Avg_Value", 


(select Round(Avg("Price"/"Sq_Ft"),+2)
from allsalesdata
where "Actual_Sale_Date" >=  '01/01/2016' AND "Actual_Sale_Date" <  '02/22/2016' 
and "Work_ID" = 'SO') AS "Last Year at this time Avg_PPSF"


from allsalesdata
where "Actual_Sale_Date" >=  '01/01/2017' AND "Actual_Sale_Date" <'02/20/2017'
and "Work_ID" = 'SO'

Sample Data:

Price       Sq_Ft        Actual_Sale_Date        Work_ID

45871       3583           01/15/2016             SO
55874       4457           02/05/2016             SO
88745       4788           02/20/2016             SO
58745       1459           01/10/2016             SO
88749       2145           01/25/2017             SO
74856       1478           01/25/2017             SO
74586       4587           01/31/2017             ABC
74745       1142           02/10/2017             SO
74589       2214           02/19/2017             SO

The query has a weeks series requirements. Running total of the columns as the year progresses. — Jake Wagner
I'm sorry but it is not clear to me, a cumulative total by week? — McNets
Yes you can say that. E.g. "Actual_Sale_Date" = date_trunc('week', now())::date - 1 this function only returns the previous week data ending on the passed Sunday. I took a look at interval since dateadd does not exist in postgresql but could not get my ways around with it. For example, Total 1st week 2016 Total 1st week 2017? Yes exactly but running total as we enter March, April, etc... — Jake Wagner

Patrick Patrick · Accepted Answer · 2017-02-24T08:57:08

This should be what you need (assuming you have a recent version of PG):

SELECT DISTINCT wk AS "Week",
       sum("Price")::money FILTER (WHERE yr = 2017) OVER w / 
       count("Price") FILTER (WHERE yr = 2017) OVER w AS "Avg_Value YTD",
       sum("Price")::money FILTER (WHERE yr = 2017) OVER w / 
       sum("Sq_Ft") FILTER (WHERE yr = 2017) OVER w AS "Avg_PPSF YTD",
       sum("Price")::money FILTER (WHERE yr = 2016) OVER w / 
       count("Price") FILTER (WHERE yr = 2016) OVER w AS "Last Year this time Avg_Value",
       sum("Price")::money FILTER (WHERE yr = 2016) OVER w / 
       sum("Sq_Ft") FILTER (WHERE yr = 2016) OVER w AS "Last Year this time Avg_PPSF",
FROM (
    SELECT extract(isoyear from "Actual_Sale_Date")::integer AS yr,
           extract(week from "Actual_Sale_Date")::integer AS wk,
           "Price", "Sq_Ft"
    FROM allsalesdata
    WHERE "Work_ID" = 'SO') sub
-- optional, show only completed weeks in this year:
WHERE wk <= extract(week from CURRENT_DATE)::integer - 1 
WINDOW w AS (ORDER BY wk)
ORDER BY wk;

In the inner query the year and week of the sale date are extracted for every sale. The week starts on Monday, as per your requirement.

In the main query these rows are processed as a single partition frame, i.e. from the start of the partition (= first row) to the last peer of the current row. Since the window definition orders the rows by wk, all rows from the start (week = 1) to the current week are included in the summarization. This will give you the running total. The sum() and count() functions filter by the year in question and the DISTINCT clause ensures that you get only a single row per week.

Running Total for Current & Previous Year on weekly basis

1 Answers