SQL and Standard Deviation

cotton31

cotton31

cotton31

Associate
Joined
25 Feb 2007
Posts
905
Location
Midlands
Hi,

I need to calculate the standard deviation for the number of tasks completed each week.

I have a table consisting of task id and date completed, so the following gives me the number of taskscompleted each week:

Code: 
SELECT DATEPART(wk,DateCompleted) AS WeekNum
, COUNT(TaskID) AS NumTasks

FROM Tasks

GROUP BY DATEPART(wk,DateCompleted)

I then need to get the standard deviation for each week. I've tried using STDEV() but I get null for every row.

I've replicated this in Excel and I get the correct figure.

Can this be done?

Thanks,
 
Hi,

Yes that's right - when I mocked this up in Excel, I was calculating the standard deviation of the number of tasks for the current week and any previous weeks. Is this possible using STDEV?

I might end up not using STDEV and calculating it manually.

Cheers,
 
Thanks for your reply.

A user has requested this for a report I'm writing in reporting services.

I currently have a graph showing a trend in the number of tasks completed each month. The user want's a rolling average to also be plotted on the graph and also lines to show this +/- the SD - is this what you were suggesting I needed?

From speaking to the user, it also sounds like the SD should be 'rolling', but I'm not sure if this is correct?

As mentioned above, this is in Reporting Services, so needs to be entirely in T-SQL

Thanks,
 
You must log in or register to reply here.
Back
Top Bottom