SQL Server Performance

Difference Between Two Dates in Seconds Excluding Sunday and Out of Office Hours

Discussion in 'SQL Server 2008 General DBA Questions' started by jchmiel, Apr 21, 2011.

  1. jchmiel New Member

    I would like the Difference in Seconds between the Create Date and the Personal Response that would not include Sunday or any hours out side of 8AM to 8PM. Basically within working hours. I could do this manually outside SQL but it would be awesome if someone had a Statement that could calculate it. I know someone out there has had to do this but I cannot find the solution. Please help!
    CURRENT SIMPLE STATEMENT
    SELECT CREATE_DATE, PERSONAL_RESPONSE_SENT_DT, DATEDIFF(ss, CREATE_DATE, PERSONAL_RESPONSE_SENT_DT) AS DATEDIF, DATEPART(weekday, CREATE_DATE) AS CD_WKD, CONSUMER_ID FROM LEADS WHERE MONTH(PERSONAL_RESPONSE_SENT_DT) = 3 AND YEAR(PERSONAL_RESPONSE_SENT_DT) = 2011 ORDER BY PERSONAL_RESPONSE_SENT_DT DESC
    SOME TABLE DATA
    CREATE_DATE PERSONAL_RESPONSE_SENT_DT DATEDIF CD_WKD CONSUMER_ID
    Mar 31 2011 07:44PM Mar 31 2011 07:47PM 210 5 173409
    Mar 31 2011 06:04PM Mar 31 2011 06:21PM 1050 5 120497
    Mar 31 2011 05:49PM Mar 31 2011 05:49PM 19 5 173403
    HERE IS ODD DATA (The 27th is a SUNDAY...)
    so the first row should be 145079sec - 3hours 34min (till 8PM) - 24hours(SUNDAY) - 8hours(till 8AM)
    145079 - 12840 - 86400 - 28800 = 17039sec / 60 = 283.98min / 60 = 4.73hour response time :(
    Mar 26 2011 04:34PM Mar 28 2011 08:52AM 145079 7 172994
    Mar 27 2011 04:54PM Mar 28 2011 08:51AM 57406 1 173021
    Mar 27 2011 08:44AM Mar 28 2011 08:50AM 86785 1 173012
  2. mmarovic Active Member

Share This Page