Developer Forum

Hi


Can any of you guys help med with two queries (MSSQL)?


sCreateCDR_TICKETS_tbl = 'create table %s (' +
'CallTime timestamp NOT NULL,'+
'TotalCalls integer,'+
'AnsweredCalls integer,'+
'ReturnCalls integer,'+
'LostCalls integer,'+
'BPLanCalls integer,'+
'CampOnCalls integer,'+
'DurationOfCalls integer,'+
'QueueTimeOfCalls integer,'+
'ParkTimeOfCalls integer,'+
'CampOnTimeOfCalls integer'+
')';

I have this table where I store some CDR (Telephony call information).

Query1, I need all records from the table between a startdate/starttime and
finishdate/finishtime, regarding the time it must be within the same time
every day not just on the start/finish date (kind of Zoom function).

Query2, I need to summerize all records per. day?

can you help me?

regards
Henry

timestamp in SQL server is not date and time as in "normal" databases :)

Read more in Books Online.

The design of the table is not very clear... Can you provide sample data and
what you want to get as result.

Bojidar Alexandrov



and

Data looks like this (sorry it's a mess)

CALLTIME TOTALCALLS ANSWEREDCALLS RETURNCALLS LOSTCALLS
BPLANCALLS CAMPONCALLS DURATIONOFCALLS QUEUETIMEOFCALLS PARKTIMEOFCALLS
CAMPONTIMEOFCALLS
==================== =========== ============= =========== ===========
=========== =========== =============== ================ ===============
=================

16-APR-2004 10:45:00 0 0 0 0 0 0 0 0
0 0
16-APR-2004 11:15:00 2 2 0 0 0 0 0 7
0 0
16-APR-2004 11:00:00 1 1 0 0 0 0 0 2
0 0
16-APR-2004 11:30:00 7 4 0 3 0 0 0 18
4 0
16-APR-2004 12:00:00 11 7 1 0 0 1 0 46
0 10
16-APR-2004 11:45:00 1 1 0 0 0 0 0 3
0 0
16-APR-2004 12:15:00 5 2 0 1 2 0 0 9
0 0
16-APR-2004 12:45:00 0 0 0 0 0 0 0 0
0 0
16-APR-2004 12:30:00 4 4 0 0 0 0 0 9
0 0
16-APR-2004 13:00:00 0 0 0 0 0 0 0 0
0 0
16-APR-2004 13:30:00 15 9 3 4 2 0 0 44
0 1
16-APR-2004 13:15:00 14 12 3 1 0 0 0 43
0 0


Query1
E.g. I would like to select all records (date between Apr. 16-2004 and Apr
20-2004) and (time between 10.00.00 and 13.00.00)


Query2
I would like the have all records summerized per day.

select sum(CALLTIME), sum(TOTALCALLS), sum(ANSWEREDCALLS) etc from
cdr_tickets
something to seperate it per day.

Result
16-APR-2004 14 12 3 1 0 0 0 43 0
0
17-APR-2004 114 12 3 1 0 0 0 43 0
0
18-APR-2004 214 12 3 1 0 0 0 43 0
0
19-APR-2004 314 12 3 1 0 0 0 43 0
0
etc


does that help?
Henry

Probbaly timestamp te does not come like timestamp to the DB because of BDE
probbaly.

Anyway.. These are untested, also it is good to have a look at SQL tutorial.
Here free one -
http://www.yqcomputer.com/

You have to use datepart function that is SQL Server's standart one.

Select CALLTIME, TOTALCALLS, ANSWEREDCALLS, RETURNCALLS, LOSTCALLS,
BPLANCALLS, CAMPONCALLS, DURATIONOFCALLS, QUEUETIMEOFCALLS,
PARKTIMEOFCALLS, CAMPONTIMEOFCALLS
From cdr_tickets
Where
(CALLTIME between '2004/04/16' and '2004/04/20') OR
(DatePart(hour,CALLTIME) between 10 and 13)

For the second one

select DatePart(year,CALLTIME) as call_year, DatePart(dayofyear,CALLTIME)
as call_dayofyear, sum(CALLTIME), sum(TOTALCALLS), sum(ANSWEREDCALLS)
from
cdr_tickets
Group by DatePart(year,CALLTIME) as call_year, DatePart(dayofyear,CALLTIME)
as call_dayofyear


Bojidar Alexandrov



0
7
2
18
46
3
9
0
9
0
44
43
0
0
0