Help with optimising query

[chriscrowhurst]

I have table which contains the status of tasks over a period of time. Each task can be Not Started, In Progress, Or Complete. Each time the status of task is updated it is added as a new record to the table :

e.g.

RecordID, TaskID, Status, DateModified
1,1, Not Started, 01/01/2015
2,1, In Progress, 01/02/2015
3,1, Complete, 01/03/2015
4,2,Not Started,01/03/2015

What I would like to do is to produce a graph using SSRS to show the status of all tasks between a particular points in time, the Y-axis would should the number of tasks, and the X-axis would be the time (group by year and month).

Using the example data above if I ran a report for all of 2015, I would like to see the data for the rest of the months in the year. e.g. for months 4 onwards it should show 1 record for Not Started (RecordID=4,TaskID=2) for each month, and 1 record for Complete (RecordID=3,TaskID=1) for each month.

The problem I have is getting the data into a suitable format to be able to produce the graph. I believe I need to create a time dimension table containing the units of time to report on e.g. year and month, then somehow cross join the tasks table, to create all the data I need using SQL.

I have had a go with this and have produced the following test t-sql to show what I am trying to achieve. However it is not very quick and I was wondering what would be a better way to achieve this ?

CREATE TABLE #dates (TimeID INT, [Year] INT,[Month] INT, [Date] DATE)
INSERT INTO #dates VALUES
	(1,2015,1,'20150101'),
	(2,2015,1,'20150201'),
	(3,2015,1,'20150301'),
	(4,2015,1,'20150401'),
	(5,2015,1,'20150501'),
	(6,2015,1,'20150601'),
	(7,2015,1,'20150701'),
	(8,2015,1,'20150801'),
	(9,2015,1,'20150901'),
	(10,2015,1,'20151001'),
	(11,2015,1,'20151101'),
	(12,2015,1,'20151201')


CREATE TABLE #tasks (RecordID INT, TaskID INT, Status VARCHAR(20), DateLastModified DATETIME)
INSERT INTO #tasks VALUES
(1,1,'Not Started','20150101'),
(2,1,'In Progress','20150201'),
(3,1,'Complete','20150301'),
(4,2,'Not Started','20150301'),
(5,3,'Not Started','20150401'),
(6,4,'Not Started','20150401'),
(7,3,'In Progress','20150701'),
(8,4,'In Progress','20150801')


SELECT D.*,
	(SELECT COUNT(TaskID) FROM #tasks T WHERE DateLastModified <= D.Date AND T.Status = 'Not Started' AND NOT EXISTS(SELECT TaskID FROM #tasks WHERE DateLastModified <= D.Date AND Status <> 'Not Started' AND TaskID=T.TaskID) ) AS NotStarted,  
	(SELECT COUNT(TaskID) FROM #tasks T WHERE DateLastModified <= D.Date AND T.Status = 'In Progress'  AND NOT EXISTS(SELECT TaskID FROM #tasks WHERE DateLastModified <= D.Date AND Status = 'Complete' AND TaskID=T.TaskID) ) AS InProgress,
	(SELECT COUNT(TaskID) FROM #tasks T WHERE DateLastModified <= D.Date AND T.Status = 'Complete' AND NOT EXISTS(SELECT TaskID FROM #tasks WHERE DateLastModified > D.Date AND TaskID=T.TaskID) ) AS Complete
FROM #dates D

 

[philhege]

What happens when you run this query?

-----------
With business clients like mine, you'd be better off herding cats.

 

[chriscrowhurst]

It creates the table of results I am looking for, but I was wondering if there is a better way to do the last SELECT statement as it contains many sub-queries to get the results.