My table ('Actions') structure & records are as below:
Action_ID Open_date Close_date
ACT1001 2015-06-18 03:58:08.000 NULL
ACT1002 2015-06-28 06:01:10.000 2015-06-30 10:11:21.000
ACT1003 2015-07-08 01:40:19.000 2015-07-09 00:29:53.000
ACT1004 2015-07-09 04:32:00.000 NULL
ACT1005 2015-07-08 21:56:20.000 2015-07-09 01:12:37.000
ACT1006 2015-07-16 17:45:56.000 2015-08-15 23:24:35.000
ACT1007 2015-08-05 17:54:25.000 NULL
ACT1008 2015-08-30 08:29:57.000 2015-08-30 22:11:45.000
ACT1009 2015-08-28 04:17:42.000 2015-09-11 21:39:11.000
ACT1010 2015-09-16 07:00:01.000 2015-09-16 19:11:29.000
Based on the above 'Actions' table, I developed a matrix (counts) report as below:
YEAR MONTH BACKLOG OPENED_ACTIONS CLOSED_ACTIONS STILL_OPEN
2015 6 0 2 1 1
2015 7 1 4 2 3
2015 8 3 3 2 4
2015 9 4 1 2 3
Now, I am trying to find BACKLOG records for each & every month.
My results should be as below:
YEAR MONTH BACKLOG_ACTIONS
2015 6 NULL
2015 7 ACT1002
2015 8 ACT1002
2015 8 ACT1004
2015 8 ACT1006
2015 9 ACT1002
2015 9 ACT1004
2015 9 ACT1007
2015 9 ACT1009
Can someone help me in writting a SQL for the above result using MS SQL Server.
Thanks in advance.
.