how to divide by number of days in the month

  • 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)

  • 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?

  • 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)

  • 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?

  • 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)

  • 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)

  • 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?

  • 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)

  • 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