AVG # of cases created per DAY
-
Hi gang!
I have created a table and stored the case_id, created_time and case_type in it.
CREATE TABLE DQCaseStartTime (
[case_id] [int] NOT NULL ,
[startTime] [datetime] NOT NULL ,
[case_type] [int] NOT NULL
) ON [PRIMARY]
GO
What I need to do is to generate a report of the Average Number of Cases Created Per Day in a Month.
The first question is, can I do this in one step? I was trying but failed.
Next, I thought I'd create a view first, which grabs the COUNT of cases created each day, starting from January 2002 and loop through each month until the current month. However when I run the CREATE VIEW script, it failed (at DECLARE). Is this because I can't create aggregates in a view? (Please take a look at my script below.) What went wrong? Is there any other way to do this so I can "SELECT AVG(NumberOfCases) FROM **Something** GROUP BY MonthName, YearName"?
Thanks in advance!
Nick
/******************* Script Starts ************************/
CREATE VIEW uv_totalDqCasesPerDay
AS
/*
-- #version0.1 4-9-2003
-- #descuv_totalDqCasesPerDay
-- #descI created the table "DQCaseStartTime"
-- #descthis view returns the total number of cases logged each day,
-- #descfrom January 2002 til the current month
-- #param
*/
/************** Declare variables @year, @month and @err ******************
*************** Set variables to year=2002, January and error=0 ************/
DECLARE @year int
DECLARE @month tinyint
DECLARE @err tinyint
SET @year = 2002
SET @month = 1
SET @err = 0
WHILE @year <= YEAR(getdate())
BEGIN
/******If @year is < this year, then do a WHILE @month <= 12 ****************************
******* else (@year = this year), then do a WHILE @month <= MONTH(getdate()) *********/
WHILE @month <= (CASE WHEN @year < YEAR(getdate()) THEN 12ELSE MONTH(getdate()) END)
BEGIN
/************ Get a count of all rows in table ***************
************* DQCaseStartTime. Grouped by days in a SET month (@month) *******
************* in a SET year (@year) **************************************************/
SELECT COUNT(*) AS 'NumberOfCases',
DAY(startTime) AS 'DayName',
MONTH(startTime) AS 'MonthName',
YEAR(startTime) AS 'YearName'
FROM DQCaseStartTime
GROUP BY DAY(startTime), MONTH(startTime), YEAR(startTime)
HAVING YEAR(startTime) = @year
AND MONTH(startTime) = @month
/*********** If no error, then @month ++ 1 to next month ***********************/
SET @err = @@error
IF @err = 0
SET @month = @month + 1
ELSEBREAK
END
/*********** If no error, then @year ++ 1 to next year ************************
************* and reset @month back to 1 ************************************/
SET @err = @@error
IF @err = 0
BEGIN
SET @year = @year + 1
SET @month = 1
END
ELSEBREAK
END
-
Do you need to show days with 0 cases? If not...
SELECT Year(startTime),
Month(startTime),
Day(startTime),
Count(*)
FROM DQCaseStartTime
GROUP BY Year(startTime),
Month(startTime),
Day(startTime)BTW, I believe the code you have for creating your view would serve better as a stored procedure.
Regards,
SJTerrill
-
Ah, yes. To get average you were looking for...
SELECTYear(startTime),
Month(startTime),
Day(startTime),
Count(*)
FROMDQCaseStartTime WITH (NOLOCK)
GROUP BYYear(startTime),
Month(startTime),
Day(startTime)
COMPUTEAvg(Count(*))SJTerrill
-
Thanks SJTerrill! If I may, I would like to pick your brain a little more... I was trying to do this w/ ROLLUP but I couldn't. I STILL CAN'T!! It's giving me Err Msg 130:
"Cannot perform an aggregate function on an expression containing an aggregate or a subquery."
SELECT YEAR(startTime) AS 'Year',
MONTH(startTime) AS 'Month',
AVG(Count(*))
FROM DQCaseStartTime WITH (NOLOCK)
GROUP BYYear(startTime),
Month(startTime),
Day(startTime)
WITH ROLLUP
Why's that happening? If I take away the AVG, I'm getting a count of the day's total cases. So why can't get put an AVG in it and get one row returned per month?
I'm doing this mainly because BOL says COMPUTE and COMPUTE BY clauses are provided for backward compatibility. Instead, we should use the ROLLUP operator.
Also, why are you using the "NOLOCK" locking hint in the second query?
Thanks again.
-
Presume it must be a restriction and that sql cannot do implicit aggregate within an aggregate and therefore you will have to do it explicitly (or is that the other way round, so confusing this time of the morning)
Try this
SELECT a.Year,a.Month,AVG(a.CT)
FROM (
SELECT YEAR(startTime) AS 'Year',
MONTH(startTime) AS 'Month',
Day(startTime) AS 'Day',
Count(*) AS 'CT'
FROM DQCaseStartTime
GROUP BY Year(startTime),
Month(startTime),
Day(startTime)
) a
GROUP BY a.Year,a.Month
WITH ROLLUP
Far away is close at hand in the images of elsewhere.
Anon. -
Nickel, to answer your second question, I cut and pasted the code for the second query from an existing proc on one of our databases. We use the WITH (NOLOCK) clause in some cases to reduce the likelihood of processes getting blocked on tables where up-to-the-second results aren't necessary. It's the equivalent of a dirty read. If we designed our indexing structures better, such things may not be needed.
But, hey, it's a datamart database anyway.To answer your first question, your SELECT clause contains Year(startTime) and Month(startTime). Your GROUP BY has those two plus Day(startTime). I guess this confuses T-SQL in that it can't figure out the relevance of a GROUP BY component that isn't included in the SELECT. That's what's generating your error. In order to get the average DAILY new case count by MONTH... I gotta think about it for a bit.
Regards,
SJTerrill
-
Nickel, David's got the goods on this one! The only thing I'd add is consider an index on your startTime column. Also, if you're concerned with fractional results, maybe use SELECT a.Year,a.Month,AVG(Cast(a.CT AS float)) for the outer SELECT.
Regards,
SJTerrill
-
Thanks David and SJTerrill!
Yes, that implicit/explicit aggregate is quite confusing. I did some similar thing yesterday as I said in my question: "SELECT AVG(NumberOfCases) FROM **Something** GROUP BY MonthName, YearName"? It's like David's query, except I tried to name the derived table with ) AS 'Total' and it failed! Today I tried David's query and changed "a" into "AS 'Total'" and it also fails! Really weird! Doesn't matter though, I am using just "a" now and the SPROC is running fine! Thanks guys!
But, hey, it's a datamart database anyway.Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply