Author Topic: Calculating Longest Duration with No Activity  (Read 2772 times)

HWI

  • Newbie
  • *
  • Offline Offline
  • Posts: 1
    • View Profile
Calculating Longest Duration with No Activity
« on: March 09, 2007, 06:47:45 PM »
I'm pulling together an interactive report using a Pivot Table in Excel.  In order to get the data in the format I need, and for the last six months, I'm using SQL queries.

(csavage's tip for using the ODBC drivers and Query Tool have been a great help, by the way.)

I'm currently trying to reproduce the "Longest Duration with No Activity" metric used in CAM's daily user activity report.  I would love to borrow the SQL statement used to generate this figure, since it's obviously a clever bit of programming that make's it run as fast as it does.   ;)

Any chance someone here is willing to share it?

To put my money where my mouth is, and for whatever it's worth, here are my SQL queries for pulling average call duration, max call duration, and total call count (between 9:00 AM to 5:00 PM hours and excluding a list of our voicemail ports) by user and by day:

SELECT AVG("calls3"."DURATION_DATETIME")/60 AS "AVERAGE DURATION", "USER_EXT", "USER_NAME", "YEAR", "MONTH", "DAY"
FROM "calls3"
WHERE "USER_EXT" NOT IN (141,142,143) AND "HOUR" > 8 AND "HOUR" < 18
GROUP BY "USER_NAME", "YEAR", "MONTH", "DAY" ORDER BY "YEAR", "MONTH", "DAY"
GO

SELECT MAX("calls3"."DURATION_DATETIME")/60 AS "MAX DURATION", "USER_EXT", "USER_NAME", "YEAR", "MONTH", "DAY"
FROM "calls3"
WHERE "USER_EXT" NOT IN (141,142,143) AND "HOUR" > 8 AND "HOUR" < 18
GROUP BY "USER_NAME", "YEAR", "MONTH", "DAY" ORDER BY "YEAR", "MONTH", "DAY"
GO

SELECT COUNT("calls3"."id") AS "CALLS", "USER_EXT", "USER_NAME", "YEAR", "MONTH", "DAY"
FROM "calls3"
WHERE "USER_EXT" NOT IN (141,142,143) AND "HOUR" > 8 AND "HOUR" < 18
GROUP BY "USER_NAME", "YEAR", "MONTH", "DAY" ORDER BY "YEAR", "MONTH", "DAY"
GO