April 19, 2016 at 7:20 am
I have a table 'Actions' like this.
CREATE TABLE Actions (
Action_Id varchar(25) NOT NULL PRIMARY KEY,
Open_Dt datetime NULL,
Close_Dt datetime NULL
);
INSERT INTO Actions (Action_Id, Open_Dt, Close_Dt) VALUES ('ACT1001', '2015-06-18 03:58:08.000', NULL);
INSERT INTO Actions (Action_Id, Open_Dt, Close_Dt) VALUES ('ACT1002', '2015-06-28 06:01:10.000', '2015-06-30 10:10:10.000');
INSERT INTO Actions (Action_Id, Open_Dt, Close_Dt) VALUES ('ACT1003', '2015-07-08 01:40:19.000', '2015-07-09 10:10:10.000');
INSERT INTO Actions (Action_Id, Open_Dt, Close_Dt) VALUES ('ACT1004', '2015-07-09 04:32:00.000', NULL);
INSERT INTO Actions (Action_Id, Open_Dt, Close_Dt) VALUES ('ACT1005', '2015-07-08 21:56:20.000', '2015-07-09 10:10:10.000');
INSERT INTO Actions (Action_Id, Open_Dt, Close_Dt) VALUES ('ACT1006', '2015-07-16 17:45:56.000', '2015-08-15 10:10:10.000');
INSERT INTO Actions (Action_Id, Open_Dt, Close_Dt) VALUES ('ACT1007', '2015-08-05 17:54:25.000', NULL);
INSERT INTO Actions (Action_Id, Open_Dt, Close_Dt) VALUES ('ACT1008', '2015-08-30 08:29:57.000', '2015-08-30 10:10:10.000');
INSERT INTO Actions (Action_Id, Open_Dt, Close_Dt) VALUES ('ACT1009', '2015-08-28 04:17:42.000', '2015-09-11 10:10:10.000');
INSERT INTO Actions (Action_Id, Open_Dt, Close_Dt) VALUES ('ACT1010', '2015-09-16 07:00:01.000', '2015-09-16 10:10:10.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
I have tried like this:
;WITH CTE AS
(
SELECT YEAR(OPEN_Dt) CCYY, MONTH(OPEN_Dt) MM, YEAR(OPEN_Dt) * 12 + MONTH(OPEN_Dt) MONTHNUM
FROM ACTIONS
GROUP BY YEAR(OPEN_Dt), MONTH(OPEN_Dt),YEAR(OPEN_Dt) * 12 + MONTH(OPEN_Dt)
)
SELECT CCYY, MM, A.Action_Id
FROM CTE
LEFT JOIN ACTIONS A ON YEAR(A.OPEN_Dt) * 12 + MONTH(A.OPEN_Dt) < MONTHNUM
WHERE A.CLOSE_Dt IS NULL
But it is not working properly. Can someone help me with writing the SQL for the above result using MS SQL Server?
Thanks in advance.
April 20, 2016 at 9:44 am
Thanks for posting CREATE TABLE and INSERT statements, expected results, and the code you have so far - an excellent way to ask a question!
I copied your code, changed it where needed to run on my case sensitive test server, and got these results:
CCYY MM Action_Id
----------- ----------- -------------------------
2015 6 NULL
2015 7 ACT1001
2015 8 ACT1001
2015 8 ACT1004
2015 9 ACT1001
2015 9 ACT1004
2015 9 ACT1007
These are not the same as you posted, but upon inspection of the test data you posted I cannot explain why these would be wrong.
Why should ACT1001 not be included for months 7 and up? Why should ACT1002 not be?
Why should ACT1006 be included for months 8? (And not for month 9)
And finally, why should ACT1009 be included for month 9?
I interpret backlog actions as actions that have no close date and your query selects on that as well, but based on the expected output that is not the correct interpretation. Can you clarify?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply