i have a table of phonecall records detailing amongst other things the time and duration of the call. there is a relationship linking this table to a table of handsets and from there to a table of users. this users table lists the start date the user had the phone. as ever sample data might make this easier to show:
PhoneUsers table
PhoneUserID Name StartDate PhoneID
1 john 1/08/2006 1
2 bob 20/08/2006 1
3 fred 2/08/2006 2
etc
Phones table:
PhoneID
1
2
etc
PhoneCalls table:
PhoneCallID PhoneID CallDate Duration
1 1 10/08/2006 25
2 1 23/082006 20
3 2 23/08/2006 20
i want the following result set:
user calldate duration
john 10/08/2006 25
bob 23/08/2006 20
fred 23/08/2006 20
essentially i am trying to link the phonecalls to the user of the phone at the time of the call. i make the following assumptions regarding the data:
the user is responsible for all calls for a given handset from the 'startdate' until the next 'startdate' for a different user, if such a record exists, if not then for all calls after that date
thanks
This should be close:select
U1.Name as [user],
C.CallDate as calldate,
C.Duration as duration
from PhoneCalls as C
left outer join
PhoneUsers as U1
on U1.PhoneID = C.PhoneID
and U1.StartDate <= C.CallDate
and not exists (
select * from PhoneUsers as U2
where U2.PhoneID = U1.PhoneID
and U2.StartDate > U1.StartDate
and U2.StartDate <= C.CallDate
)
Steve Kass
Drew University
http://www.stevekass.com
milo68@.discussions.microsoft.com wrote:
> i have a table of phonecall records detailing amongst other things the
> time and duration of the call. there is a relationship linking this
> table to a table of handsets and from there to a table of users. this
> users table lists the start date the user had the phone. as ever sample
> data might make this easier to show:
>
> PhoneUsers table
>
> PhoneUserID Name StartDate PhoneID
>
> 1 john 1/08/2006 1
>
> 2 bob 20/08/2006 1
>
> 3 fred 2/08/2006 2
>
> etc
>
> Phones table:
>
> PhoneID
>
> 1
>
> 2
>
> etc
>
> PhoneCalls table:
>
> PhoneCallID PhoneID CallDate Duration
>
> 1 1 10/08/2006 25
>
> 2 1 23/082006 20
>
> 3 2 23/08/2006 20
>
> i want the following result set:
>
> user calldate duration
>
> john 10/08/2006 25
>
> bob 23/08/2006 20
>
> fred 23/08/2006 20
>
> essentially i am trying to link the phonecalls to the user of the phone
> at the time of the call. i make the following assumptions regarding the
> data:
>
> the user is responsible for all calls for a given handset from the
> 'startdate' until the next 'startdate' for a different user, if such a
> record exists, if not then for all calls after that date
>
>
>
> thanks
>
>
>
>
>
>
