I have a table that looks like the one below. I will like to find the sum of the cumulative difference between the target value daily actual value.

ID  | Date    | Target_value | Daily_Value
1   |01/10/20 |   200        |   5
2   |01/10/20 |   500        |   2
3   |05/10/20 |   600        |   10
1   |04/11/20 |   200        |   50
2   |05/11/20 |   500        |   80
3   |05/11/20 |   600        |   40
1   |06/12/20 |   200        |   50
4   |06/12/20 |   400        |   30
5   |07/12/20 |   300        |   20

Expected output

Date     | Target_value - monthly_cummulative daily_value          |
   10/20 | (200   500   600) - (5   2   10) =   1283               |
   11/20 | (200   500   600) - (17   50   80   40) = 1113          |
   12/20 | (200   500   600   400   300) - (17   170   100) = 1713 |

This is similar to this

Demo

CodePudding user response：

Just for completeness, I wanted to mention that on AWS Redshift (which is similar to, but not exactly Postgres) the above SQLs will not work as the "over (order by ...)" construct requires a frame clause. For example, the foll modification of LukStorms' answer is needed on Redshift:

SELECT 
  TO_CHAR(DATE_TRUNC('month', date), 'MM/YY') AS MonthYear
, SUM(Target_value) 
  - SUM(SUM(Daily_Value)) OVER (ORDER BY MonthYear ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS monthly_cumulative
FROM yourtable
GROUP BY MonthYear
ORDER BY MonthYear;

The frame clause added here is "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW". For conciseness, I have also replaced all but the first use of DATE_TRUNC by the alias MonthYear.

CodePudding user response：

Group by the truncated dates.

Then Sum over the daily sum.

But the targets need to be handled seperatly.

WITH CTE_TARGETS AS (
  SELECT ID
  , MAX(Target_Value) AS Target_Value
  , MIN(DATE_TRUNC('month', Date)) as month_first
  FROM your_table
  GROUP BY ID
), CTE_MONTHLY AS
(
  SELECT 
    DATE_TRUNC('month', Date) AS month_first
  , SUM(SUM(Daily_Value)) OVER (ORDER BY DATE_TRUNC('month', Date)) AS month_daily
  FROM your_table t
  GROUP BY DATE_TRUNC('month', Date)
) 
SELECT 
  TO_CHAR(mon.month_first, 'MM/YY') AS Month
, SUM(Target_Value) - month_daily AS monthly_cummulative
FROM CTE_MONTHLY mon
JOIN CTE_TARGETS tgt ON tgt.month_first <= mon.month_first
GROUP BY mon.month_first, month_daily
ORDER BY mon.month_first

db<>fiddle here