September 23, 2009 at 8:56 am
Sometimes the problem with being self taught in TSQL is knowing where to begin. If my search missed a thread discussing this please point me to it and I will start there.
I am working on a report which looks at the average amount of time it takes to dispatch a request for service compared to the average number of calls received in an hour. While it was easy to work out for the entire year by dividing the entire number of calls by the datediff of the report start end dates I want to take it down to a month level. What I can't figure out is how to insert the number of days in the month in the division statement.
This is what I have so far.
DECLARE @RptStart varchar(30);
DECLARE @RptEnd varchar(30);
SET @RptStart = '2008-09-01 00:00:00'
SET @RptEnd = '2009-08-31 23:59:59'
select
Dispatch_Hour_Sortable
,Dispatch_Month_Sortable
,Dispatch_Month
,(Count(v_incident_times.Incident_pk))/ --Number of days in month Need magic here!)as 'Call Avg Hour'
,avg(Transmit_to_Dispatch_numeric_seconds) 'Disp Time Avg'
from
dbo.v_Incident_Times
Join V_Incident
on v_incident.incident_pk=v_incident_times.incident_pk
Thank you for any and all help
Thom Woolverton
--
"The weakness of thinking machines is that they actually believe all the information they receive, and react accordingly." Vorian Atreides
(Brian Herbert and Kevin J. Anderson)
September 23, 2009 at 9:06 am
Assuming dispatch_month is an actual datetime you're grouping on, you can use something like
datediff(day,dispatch_month,dateadd(month,1,dispatch_month))
to get the days in a given month.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 23, 2009 at 10:12 am
Matt
Thank you for the quick reply. I should of defined my fields a little better. It's not a timestamp. This is how the Dispatch_Month field is built in the view:
...
datename(month,IIncident.I_tTimeDispatch) as Dispatch_Month,
...
But your answer led me to research (dateadd was new to me) and build this into the select statement:
,(Count(v_incident_times.Incident_pk))/Datediff(DD,(dispatch_time-day(dispatch_time)+1),DATEADD(MM,1,(dispatch_time-day(dispatch_time)+1)))as 'Call Avg Hour'
Which while I found it interesting, returns a row for every timestamp, but does give me the right number of days for the month. So you think it would be worth trying to convert the dispatch_month field back to a date type and plug it into the above?
Thom
--
"The weakness of thinking machines is that they actually believe all the information they receive, and react accordingly." Vorian Atreides
(Brian Herbert and Kevin J. Anderson)
September 23, 2009 at 10:39 am
Whether it's worth it is up to you. It looks like you need to use it something like that in several places, so I'd think it might be.
Given what you're doing (i.e. aggegating by month), I'd look at create a datetime per detail representing midnight on the first of the month (e.g. 9/1/2009 00:00:00.000), which would then allow you to use in the GROUP BY clause, AND in this calculation (it's also very convenient for sorting the results, etc...). If you can pre-calculate this, your summary calculation will be a LOT faster.
FYI - if you ned to take a specific datetime ("timestamp" in your terminology) and convert it down to the first like shown above, you can use something like:
dateadd(month,datediff(month,0,MyDateTimeValue),0)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 23, 2009 at 10:46 am
I've now built a case stm which works standing alone, but fails when part of the query. Either I'm missing something simple, or it doesn't work like this. Could someone tell me the painfully obvious thing I'm missing here? Thank you.
select
Dispatch_Hour_Sortable
,Dispatch_Month_Sortable
,Dispatch_Month
,Datediff(DD,(dispatch_time-day(dispatch_time)+1),DATEADD(MM,1,(dispatch_time-day(dispatch_time)+1)))as 'days in month'
,Count(v_incident_times.Incident_pk)AS COLUMN_COUNT
,Count(v_incident_times.Incident_pk) / 'days' =
CASE
WHEN Dispatch_Month_Sortable IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN Dispatch_Month_Sortable IN (4, 6, 9, 11) THEN 30
WHEN Dispatch_Month_Sortable IN (2) THEN 28
ELSE 31
END
,avg(Transmit_to_Dispatch_numeric_seconds) 'Disp Time Avg'
--,DATEDIFF(dd,@rptstart,@rptend)
from
dbo.v_Incident_Times
Join V_Incident
on v_incident.incident_pk=v_incident_times.incident_pk/code]
and here is the error:
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '='.
--
"The weakness of thinking machines is that they actually believe all the information they receive, and react accordingly." Vorian Atreides
(Brian Herbert and Kevin J. Anderson)
September 23, 2009 at 10:49 am
Matt,
I'll take what you suggest and give it a try. Again thank you for your time and patience with my basic questions.
Thom
--
"The weakness of thinking machines is that they actually believe all the information they receive, and react accordingly." Vorian Atreides
(Brian Herbert and Kevin J. Anderson)
September 23, 2009 at 11:04 am
Your syntax error is cause by trying to alias part of a calculation.
select
Dispatch_Hour_Sortable
,Dispatch_Month_Sortable
,Dispatch_Month
,Datediff(DD,(dispatch_time-day(dispatch_time)+1),DATEADD(MM,1,(dispatch_time-day(dispatch_time)+1)))as 'days in month'
,Count(v_incident_times.Incident_pk)AS COLUMN_COUNT
,Count(v_incident_times.Incident_pk) / /*'days' = --This is causing the error*/
CASE
WHEN Dispatch_Month_Sortable IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN Dispatch_Month_Sortable IN (4, 6, 9, 11) THEN 30
WHEN Dispatch_Month_Sortable IN (2) THEN 28
ELSE 31
END
,avg(Transmit_to_Dispatch_numeric_seconds) 'Disp Time Avg'
--,DATEDIFF(dd,@rptstart,@rptend)
from
dbo.v_Incident_Times
Join V_Incident
on v_incident.incident_pk=v_incident_times.incident_pk
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 23, 2009 at 11:10 am
Thanks Matt, corrected the error and all is good. After I get this report out of the way I'm going to work on your previous suggestion. It looks like a better long term solution.
Thom
--
"The weakness of thinking machines is that they actually believe all the information they receive, and react accordingly." Vorian Atreides
(Brian Herbert and Kevin J. Anderson)
September 23, 2009 at 11:40 am
Thomw (9/23/2009)
I've now built a case stm which works standing alone, but fails when part of the query. Either I'm missing something simple, or it doesn't work like this. Could someone tell me the painfully obvious thing I'm missing here? Thank you.
select
Dispatch_Hour_Sortable
,Dispatch_Month_Sortable
,Dispatch_Month
,Datediff(DD,(dispatch_time-day(dispatch_time)+1),DATEADD(MM,1,(dispatch_time-day(dispatch_time)+1)))as 'days in month'
,Count(v_incident_times.Incident_pk)AS COLUMN_COUNT
,Count(v_incident_times.Incident_pk) / 'days' =
CASE
WHEN Dispatch_Month_Sortable IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN Dispatch_Month_Sortable IN (4, 6, 9, 11) THEN 30
WHEN Dispatch_Month_Sortable IN (2) THEN 28
ELSE 31
END
,avg(Transmit_to_Dispatch_numeric_seconds) 'Disp Time Avg'
--,DATEDIFF(dd,@rptstart,@rptend)
from
dbo.v_Incident_Times
Join V_Incident
on v_incident.incident_pk=v_incident_times.incident_pk
and here is the error:
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '='.
Try this:
select
Dispatch_Hour_Sortable
,Dispatch_Month_Sortable
,Dispatch_Month
,Datediff(DD,(dispatch_time-day(dispatch_time)+1),DATEADD(MM,1,(dispatch_time-day(dispatch_time)+1)))as 'days in month'
,Count(v_incident_times.Incident_pk)AS COLUMN_COUNT
,Count(v_incident_times.Incident_pk) /
CASE
WHEN Dispatch_Month_Sortable IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN Dispatch_Month_Sortable IN (4, 6, 9, 11) THEN 30
WHEN Dispatch_Month_Sortable IN (2) THEN 28
ELSE 31
END
,avg(Transmit_to_Dispatch_numeric_seconds) 'Disp Time Avg'
--,DATEDIFF(dd,@rptstart,@rptend)
from
dbo.v_Incident_Times
Join V_Incident
on v_incident.incident_pk=v_incident_times.incident_pk
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply