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

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

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!

CodePudding user response：

;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; 

TEST

http://sqlfiddle.com/#!18/13cace/3