Complex SQL Query?? I can't seem to come up with the logic. 1

[BarFlyNJ]

First off, I'm new to this forum and I would just like to send a big HI! to everyone.

Ok, so I am fairly new at this SQL/StoredProcedure stuff, I am an experienced C/C++ programmer (about 10years) but I was just thrown into this project and I have to come through.

Here is the situation, we have a PBX phone system, that puts out data onto an MS SQL server 7. There is a table with 3 (relevant) fields:

PhoneNumber (varchar(10)), CallStart (datetime) and CallEnd (datetime).

There are two queries that I need:

I needed one that tells me the number of calls that were made within any given day and that day only (meaning the call started and ended between 00:00:00AM and 23:59:59) this one I have written as:

set @DateFrom = '01/22/04 00:00:01'
set @DateTo = '01/22/04 23:59:59'
Select @Calls = count(*)
from PBXRecords
where CallStart > @DateFrom and CallEnd < @DateTo

Which seems to work fine...

The BIG problem is that I need a query to know the seconds that the PBX was used on a given date and not just add up the seconds of the calls that a query similar to the previous one would return. But true number od seconds used during the day.

The problem comes when a call starts before midnight and ends during the day in question (TDIQ). There are 4 cases:

Case 1 Calls that start and end within TDIQ. (simple case)
Case 2 Calls that start before TDIQ and end during TDIQ.
Case 3 Calls that start during TDQI and end after TDIQ.
Case 4 Calls that start before TDIQ and ends after TDIQ.

Diagram:
timeline -------------|--TODAY--|------------
case 1 ------------------[CALL]--------------
case 2 -------------[CALL]-------------------
case 3 -----------------------[CALL]---------
case 4 -----------[ VERY LONG CALL ]---------
(fixed width font helps visualize this diagram...)


I need to know the ONLY the total number of seconds of all the calls that happened ONLY WITHIN TDIQ.

And similarly I need a list of all the phone numbers dialed from the PBX during TDIQ, with a total number of seconds per phonenumber (not per call) of the number of seconds that each phone number was called. Again with all the calls that had any portion of it duringTDIQ.

I've been racking my brain against this problem and I can't come up with a solution and I don't think it should be &quot;that&quot; hard.

Any SQL geniuses out there that can give a hand?

Thanks in advance for any and all responses,

BarFly

 

[nigelrivett]

create table #a (callstart datetime, callend datetime, phonenumber int)
insert #a select '20040123 02:00', '20040123 03:00', 1
insert #a select '20040122 23:00', '20040123 02:00', 1
insert #a select '20040123 21:00', '20040124 01:00', 1
insert #a select '20040122 23:00', '20040124 01:00', 1
insert #a select '20040122 02:00', '20040122 03:00', 1
insert #a select '20040124 02:00', '20040124 03:00', 1

declare @d datetime
select @d = '20040123'

declare @d1 datetime
declare @d2 datetime
select @d1 = @d, @d2 = dateadd(dd,1,@d)

select sum(datediff(ss,callstart, callend))
from
(select callstart = case when callstart < @d1 then @d1 else callstart end,
callend = case when callend > @d2 then @d2 else callend end
from #a
where callstart < @d2
and callend > @d1
) a

You may end up with something too big for an int though.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[clapag22]

--I haven't had time to test this...

declare @tdiq datetime,
@calls int,
@seconds

select @tdiq = '1/24/04' -- just setting the date makes it set to midnight

-- Get calls
Select @Calls = count(*)
from PBXRecords
where CallStart between @tdiq and dateadd(second, -1, dateadd(day, 1, @tdiq))

--Started and Ended in TDIQ
select @seconds = sum(datepart(second, CallStart, CallEnd))
from pbxrecords
where convert(varchar(10), callstart, 101) = @tdiq
and convert(varchar(10), callend, 101) = @tdiq

--Started before TDIQ, Ended in TDIQ
select @seconds = @second + sum(datepart(second, @tdiq, CallEnd))
from pbxrecords
where convert(varchar(10), callstart, 101) < @tdiq
and convert(varchar(10), callend, 101) = @tdiq

--Started in TDIQ, Ended after TDIQ
select @seconds = @second + sum(datepart(second, CallStart, dateadd(second, -1, dateadd(day, 1, @tdiq))))
from pbxrecords
where convert(varchar(10), callstart, 101) = @tdiq
and convert(varchar(10), callend, 101) > @tdiq

--Started before TDIQ, Ended after TDIQ
select @seconds = @second + sum(datepart(second, @tdiq, dateadd(second, -1, dateadd(day, 1, @tdiq))))
from pbxrecords
where convert(varchar(10), callstart, 101) < @tdiq
and convert(varchar(10), callend, 101) > @tdiq

--Detail:
select phonenumber, sum(seconds) as seconds
from (
select phonenumber, sum(datepart(second, CallStart, CallEnd)) as seconds
from pbxrecords
where convert(varchar(10), callstart, 101) = @tdiq
and convert(varchar(10), callend, 101) = @tdiq
group by phonenumber
union all
select phonenumber, sum(datepart(second, @tdiq, CallEnd))
from pbxrecords
where convert(varchar(10), callstart, 101) < @tdiq
and convert(varchar(10), callend, 101) = @tdiq
group by phonenumber
union all
select phonenumber, sum(datepart(second, CallStart, dateadd(second, -1, dateadd(day, 1, @tdiq))))
from pbxrecords
where convert(varchar(10), callstart, 101) = @tdiq
and convert(varchar(10), callend, 101) > @tdiq
group by phonenumber
union all
select phonenumber, sum(datepart(second, @tdiq, dateadd(second, -1, dateadd(day, 1, @tdiq))))
from pbxrecords
where convert(varchar(10), callstart, 101) < @tdiq
and convert(varchar(10), callend, 101) > @tdiq
group by phonenumber
) as tb1
group by phonenumber

 

[royjimenez]

u should capture only startdate:

to capture today calls :

select count(*)
from mytable
where startDate >= '2004-01-01' and
startDate < '2004-01-02' and
finishDate < '2004-01-02'

Now, u have to capture the calls which started &quot;yesterday&quot; and finish &quot;today&quot;.

select count(*)
from mytable
where startDate >= '2004-01-01' and
startDate < '2004-01-02' and
finishDate >= '2004-01-02'


if u need to get the seconds for each day, u can capture the calls with two selects ( or u can do an union ) and to get the difference with the midnight.

I hope that it helps

Regards


The life is too short to cry and long enough to try it... God bless us.

 

[BarFlyNJ]

Thanks to all I will be trying out all the solutions, and verifying results.

THANKS A LOT!!!

Barfly.

 

[BarFlyNJ]

NigelRivett,

Thank you Sir,

Your approach not only worked like a charm but is elegantly written. Again I thank you and my hat is off to you.

The second part of my question where I need to list the total number of seconds used to each specific phone number doesnt quite work right though, I'm sure I am missing something, but what is doing is listing each individual call to each phone number, so if say 3 people call the same number each call with its duration is listed. Where I would need just the phone number dialed dialed by the three people, listed just once and with the combined number of seconds of the three calls.

Could you please give me another hand, I've been playing with distinct, and unique an group by... but I am just not good at this stuff yet... (but learning from the pros!!)

Thanks,

BTW this is how I am doing it now but its giving me the desired results above descrived.

select (datediff(second, starttime, endtime)), phonenumber
from
(select phonenumber, starttime = case when starttime < @d1 then @d1 else starttime end,
endtime = case when endtime > @d2 then @d2 else endtime end
from APCDR
where starttime < @d2
and endtime > @d1
)a order by phonenumber



P.S.
Clapag22 & Ray, Since I got Nigel's response first and it worked so great I didnt get a chance to test yours out.

 

[BarFlyNJ]

Hi, its me again, I'm still stuck with this problem, I just thougth I'd post something as a reminder to see if NigelRivett can help me out with the second part of the problem.

Thanks in advance.

BarFly