Can someone help me in creating a dummy query for following use case-CodePudding

In below picture one is the problem statement and on other side is the result I need . Can someone help me with the sql query.

CodePudding user response：

Sorry I had completed the first answer for MS SQL. 2nd response for Postgres:


Select tbl.Month, MAX(tbl.ClosedCount) as closedCount, MAX(tbl.RepliedCount) as RepliedCount
From
((select closedDate as Month, COUNT(*) ClosedCount,0 as RepliedCount
 from Dummy
 group by ClosedDate) 
 UNION
  (select repliedDate as Month, 0 as ClosedCount, COUNT(*) RepliedCount 
 from Dummy
 group by repliedDate)) as tbl
 group by Month
 order by Month

I created the test using:

CREATE TABLE dummy(
   ID int PRIMARY KEY,
   ClosedDate INT,
  RepliedDate INT
);

INSERT INTO Dummy
    (ID, ClosedDate, RepliedDate)
VALUES
    (1, 10, 11),
    (2,12,11),
    (3,10,12),
    (4,11,12)
;

and the output shows as

You can see my example at

You can see my example at http://sqlfiddle.com/#!18/c8ae79/3