number of records in different time periods

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

number of records in different time periods

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-06-01 : 04:22:03

Hi,
I have a db which records phone calls logged, (similar to a helpdesk).
I need assistance with extracting data in a particular way.
I would like to show all records closed within 5 minutes of them being added and all records closed within 20 mins of them being added. how would I do this ?
the fields are callno, logdate and closedate.
at present the dates are recorded as varchars. I can convert them like
convert(datetime,logdate,103)
but how could I display a count for each closed criteria ?

thank you Jamie

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-01 : 04:31:13

can you post your table structure, some sample data and the result that you want ?

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-06-01 : 04:39:13

callNO logdate closedate
6539 01/06/2006 06:09:04
6540 01/06/2006 06:48:54
6541 01/06/2006 07:02:56 01/06/2006 07:06:47
6542 01/06/2006 07:05:05 01/06/2006 07:18:16
6543 01/06/2006 07:13:38
6544 01/06/2006 07:26:36 01/06/2006 07:40:31
6545 01/06/2006 07:32:58
6546 01/06/2006 07:37:17
6547 01/06/2006 07:38:13
6548 01/06/2006 07:39:23 NULL
6549 01/06/2006 07:39:58 NULL
6550 01/06/2006 07:40:59
6551 01/06/2006 07:41:11 NULL
6552 01/06/2006 07:42:10 NULL
6553 01/06/2006 07:49:12

and I just need counts really for number of calls closed within certain time periods, 5g, 5, mins, 20 mins, 1 hour, etc.
like :
[calls closed < 5] [calls closed >5 and <20]
2 4

callno int
logdate varchar
closedate varchar

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-01 : 04:46:08

What youw ant is cross tab.

something like this :

select count(case when datediff(minute, logdate, closedate) <=  5 then 1 end) as [calls closed <= 5],
       count(case when datediff(minute, logdate, closedate) >   5 and
                       datediff(minute, logdate, closedate) <= 20 then 1 end) as [calls closed  > 5 and < 20]
from   table
where  closedate is not null

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-06-01 : 04:51:44

Thank you, however I get the following error :
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Warning: Null value is eliminated by an aggregate or other SET operation.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-01 : 04:58:12

you have closeddate that is not null but empty string ?

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-06-01 : 05:00:40

also, how could I use this to do within 4 hours ?
would I changethe miunte to hour ?

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-06-01 : 05:06:06

I think some records have ' ' rather than NULL..

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-06-01 : 05:15:51

If I add AND
closedate <> ' ' to the WHERE clause it still errors.
I have checked some dates and it seems to have a problem with some of the dates..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-01 : 05:22:11

add the following to the where clause to filter out the record.

where  closedate is not null
and    closedate <> ''

Have you condier using date time data type for the logdate & closedate ? it will be much neater.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-01 : 05:23:07

4 hours is 240 minutes.

select count(case when datediff(minute, logdate, closedate) <= 5 then 1 end) as [calls closed <= 5],
count(case when datediff(minute, logdate, closedate) > 5 and datediff(minute, logdate, closedate) <= 20 then 1 end) as [calls closed > 5 and < 20],
count(case when datediff(minute, logdate, closedate) > 20 and datediff(minute, logdate, closedate) <= 240 then 1 end) as [calls closed > 20mi and < 4hr]
from table
where isdate(closedate) = 1

you can also mix the comparisons! see the isdate() works even if empty space or null.

select count(case when datediff(minute, logdate, closedate) <= 5 then 1 end) as [calls closed <= 5],
count(case when datediff(minute, logdate, closedate) > 5 and datediff(minute, logdate, closedate) <= 20 then 1 end) as [calls closed > 5 and < 20],
count(case when datediff(minute, logdate, closedate) > 20 and datediff(hour, logdate, closedate) <= 4 then 1 end) as [calls closed > 20mi and < 4hr]
from table
where isdate(closedate) = 1

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-06-01 : 05:55:12

thank you for all the help, but I think my major problem is the dates are varchars ! I cannot change the DB table types.
I have tried changing logdate to convert(datetime,logdatE) but still the error appears :

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Warning: Null value is eliminated by an aggregate or other SET operation.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-01 : 05:59:23

have you tried using isdate on logdate & closedate to identify the record with problem ?

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-06-01 : 06:17:25

I have noticed some rows in closedate have 0 when doing isdate, but the date looks valid eg : 17/05/2006 13:24:16
and 23/05/2006 07:48:02

very strange.....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-01 : 06:21:58

your date format is mm/dd/yyyy hh:mm:ss

try using convert(datetime, longdate, 121)

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-06-01 : 06:25:53

still fails.
the date format in the fields are actually dd/mm/yyyy but it does seem to fail on anything with a dd greater than 12, which suggests it thinks it is mm/dd/yyyy .

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-06-01 : 06:32:23

If I change 121 to 113 I get this error :
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-06-01 : 06:35:47

I have just noticed the data in the table is actually like : 5/4/2006 9:20:33 AM
would that make any difference ?

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-06-01 : 06:39:01

That must be the problem . .
some dates are like 04/05/2006 12:15:25

others are like 5/4/2006 12:23:27 PM

Is there anyway to select this data without having to change the data in the tables ?

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-06-01 : 06:53:48

more investigation :
If I run :
select isdate(logdate), isdate(closedate), closedate
from opencall
where closedate is not null and closedate <> ''
and isdate(closedate) = 0

many records are returned with valid dates !?!
eg :

1 0 15/05/2006 11:24:34
1 0 17/05/2006 11:08:22
1 0 17/05/2006 13:24:16
1 0 17/05/2006 13:01:39
1 0 31/05/2006 09:06:48
1 0 23/05/2006 07:48:02
1 0 15/05/2006 08:03:39
1 0 18/05/2006 10:39:56
1 0 16/05/2006 08:32:22
1 0 15/05/2006 08:49:12
1 0 17/05/2006 09:02:26
1 0 18/05/2006 10:35:30
1 0 17/05/2006 09:34:27
1 0 17/05/2006 14:21:21
1 0 22/05/2006 13:51:13

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-01 : 09:22:35

SET DATEFORMAT dmy
GO

DECLARE @datevar datetime
SET @datevar = '22/05/2006'
SELECT @datevar
GO

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources