Calculating volume of Helpdesk call backlog for specifice date

[Monkeyboy126]

I run a helpdesk and use CA's USD call logging software sitting on SQL.

I want to calculate the backlog (calls that were in an open state for selected date or day by day for a date range). The database has the following fields:

Datetime opened
Datetime Closed
Active flag (1 or 0)

I am looking to chart the outstanding calls on a day by day basis for a given period.

Any ideas???

 

[rosemaryl]

Dear Monkeyboy126,

I am assunming that the active field indicates if a call is open or closed but that doesn't actually help you.

Question:

Do you have data for each calendar day?

The reason I ask is that what you want to show is a call was opened on 01/01/02 and it was closed on 01/04/02.

So for your chart purposes you need to show a count for this call on the 1, 2, 3, and maybe 4 depending on your needs.

Is that correct?

This is harder than it looks, because the record will only be read once in Crystal. There are formulas you can use to show missing days, but I don't think that will work for you especially with charting.

To overcome this for a report against another helpdesk software (MagicTSD) I had to write a stored procedure in which I generate the dates for the range (parameters) and set up a counter that summed all calls who in an open state on each day (Open Calls).

You will then be able to chart the numbers for each calendar day.

If looking at the stored procedure would help you, let me know.

I hope that helps,

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Monkeyboy126]

Dear Ro

I believe the stored procedure that you mention may just do the trick.

I seem to be finding it difficult just to explain what it is that I require but I think you already have a good understanding. I am trying to show a running history of calls outstanding (i.e. not in a closed state) on a daily basis.

We have always done this manually at the end of each day by a simple 'show me open calls' query. This is okay as long as you don't miss a day!

I'm sure I've seen this working while visiting another support desk but as we didn't use Crystal at the time....

Hope that gives some more insight to my problem and I look forward to hearing from you.

Many thanks


Paul

 

[synapsevampire]

I resolve the no data available for dates issue by creating a Periods table (which would have an entry for all days), this minimal investment of time resolves these types of reporting concern forevermore.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[rosemaryl]

Paul,

Yes, I understand your issue. From a metrics standpoint you need a total count of outstanding calls by day - however information is not stored this way in the database.

I am working from my home computer will post the stored procedure for you tomorrow if that is okay? I actually think I might have posted it up here before...let me do a search and see if I can't point you to the thread,

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Monkeyboy126]

Thanks Ro, standing by.....

Thanks synapsevampire

 

[rosemaryl]

Dear Paul,

Sorry for the delay. I have been swampled today.

Here is a Stored Procedure that will generate dates for an entire year using derived tables. I found this procedure on SQLTeam and modified it for my needs, here is the original link:

author: Alexander Netrebchenko
link:

http://www.sqlteam.com/item.asp?ItemID=3332

Fix the stored procedure to match your table and field names. Comment out the Grant Public after running the first time.

Go into Crystal and connect to the stored procedure, you will be prompted for a date, I always use the first day of the year, but whatever you use it will generate dates from there forward.

Now you can group your report by month and then day as the main report and link to your actual table via a subreport to show data.

/*Code Begins*/

if exists (select * from sysobjects where id = object_id('dbo.sp_DateReport_R') and sysstat & 0xf = 4)
drop procedure dbo.sp_DateReport_R
GO
CREATE PROCEDURE dbo.sp_DateReport_R
@startdate datetime
as
Begin
/*set @startdate = ('01/01/02')commented out for Crystal*/
declare @counter int
set @counter =1

declare @enddate datetime
set @enddate = DATEADD(YYYY, 1, @startdate)-1
declare @month int
set @month = datepart(M,@startdate)
declare @y int
set @y = datepart(Yy,@startdate)
declare @mydate datetime
Set @mydate = @startdate

declare
@start datetime,
@end datetime,
@counter1 int
set @start = @startdate
set @end = @enddate
set @counter1 = 1
select dates.date
from
(
-- This select produces a sequence of dates starting from @start
select DATEADD(dd, seq.id, @start) as date
from
(
-- This select produces a sequence from 0 to 399 using Cartesian product.
select (a0.id + a1.id + a2.id) as id
from
(select 0 id union select 1 union select 2 union select 3 union select 4 union select 5 union
select 6 union select 7 union select 8 union select 9) a0,

(select 0 id union select 10 union select 20 union select 30 union select 40 union select 50 union select
60 union select 70 union select 80 union select 90) a1,

(select 0 id union select 100 union select 200 union select 300) a2
-- ...
) seq
) dates

left join _SMDBA_._TELMASTE_
on dates.date between [DATE OPEN] and coalesce([Closed On], [Closed On], '12/31/9999')
where
dates.date between @start and @end
group by
dates.date
order by 1


END


go
grant execute on MagicTSD.dbo.sp_DateReport_R to Public
go

/*Code Ends - Don't forget to comment out grant after first run*/

I hope this helps, if you need something else let me know.

I also have a stored procedure that I created that is used to populate a calendar style report (thanks to ChelseaTech Newsletter for the how to). The stored procedure gathers open and closed call counts for each "possible square" in a calendar.

Once again, sorry for the delay...

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[synapsevampire]

If you create a pre-populated perdiods table, you can always join to it from any table, and you eliminate this sort of brute force coding for a requirement common to most reporting environments.

The net effect is to build a temporary Periods table.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[rosemaryl]

Synapsevampire,

Yes, I know, obviously the best thing to do is to have a periods table.

But, sometimes the client will not let you add a table or there are limitations.

For example, the product I consult on (MagicTSD - Help Deks software) has a database admin tool you use to create the tables(actually it creates the base table and view for you -adds app required fields, etc).

To use a table in the application it must be created here and not in the backend or extreme havoc will ensue. The issue is that the db must be shutdown to use that tool. While I could add the table on the backend and make sure that the owner is dbo so as not to interfere with the app - that means that client won't be able to maintain. Most of my clients don't have SQL dba's on staff. The use SQL or Oracle because this app runs on it.

The client does not want to hear when I arrive on site to do reports that we need to shutdown their (to them) mission critical database.

This is a good solution when you are allowed to write sp's but cannot add tables to the db.

My two cents...

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[synapsevampire]

I agree, and I have and likely will use similar techniques.

My wife is back in NYC on the 19th, how's your "near the Holidaze" schedule?

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[rosemaryl]

Dear Kai,

I'll be around, just got back from 10 days of vaca in FL so no more planned unless I get a booking somewhere.

Give me a call and I will be happy to meet you and your wife!

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[Monkeyboy126]

Thanks guys, I've think we've cracked it.

Really apreciated......

Paul