How to aggregate event dates based on aggregated table?

February 3, 2023

I have a table that aggregates disables/reenables events in the following format:

Disabled Date	Enabled Date	Count
01/01	01/01	5
01/01	02/01	2
03/01	05/01	1
04/01	05/01	5

and want to build a report that aggregates the number of disables and reenables per day:

Date	Enables	Disables
01/01	5	7
02/01	2	0
03/01	0	1
04/01	0	5
05/01	6	0

I was able to build the following query that works for days that have at least one disable and one enable:

SELECT
  disables.disable_date AS disable_date, 
  disables.disable_count disable_count,
  enables.enable_count enable_count
FROM 
  (SELECT
    disable_date, 
    sum(disable_count) disable_count
    FROM table
  GROUP BY 1) AS disables,
    (SELECT
    enable_date, 
    sum(disable_count) enable_count
    FROM table
  GROUP BY 1) AS enables
WHERE enables.enable_date = disables.disable_date;

Any suggestions how to build the complete output? I’m not sure this is the right strategy, so a JOIN could also be considered.

Thanks!

>Solution :

;WITH cte
     AS (SELECT disabled_date AS Date,
                Sum(count)    AS Disables
         FROM   table1
         GROUP  BY disabled_date
         UNION ALL
         SELECT enabled_date AS Date,
                Sum(-count)  AS Disables
         FROM   table1
         GROUP  BY enabled_date)
SELECT cte.date,
       COALESCE(Sum(CASE
                      WHEN cte.disables > 0 THEN cte.disables
                    END), 0) AS Enables,
       COALESCE(Sum(CASE
                      WHEN cte.disables < 0 THEN -cte.disables
                    END), 0) AS Disables
FROM   cte
GROUP  BY cte.date
ORDER  BY cte.date;