find phonecall records based on user at the time of call

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

>

>

>

>

>

>

Find all dimensions with calculated members using AdomdClient

Hi,

I need to find all dimensions in a cube that have calculated members but I can't afford to call GetMembers on every hierarchy level.

Visual Studio lists all the calculations on a cube (calculated members, named sets and script commands) - I'd like to find this information using AdomdClient

Thanks,

James

Background: Our product allows users to drag dimensions onto a report and filter them - we have to write the MDX on the fly to do this - in order to allow filtering of calculated members we need to distinguish if a dimension has calculated members.

> Visual Studio lists all the calculations on a cube (calculated members, named sets and script commands) - I'd like to find this information using AdomdClient

Not possible.

> Background: Our product allows users to drag dimensions onto a report and filter them - we have to write the MDX on the fly to do this - in order to allow filtering of calculated members we need to distinguish if a dimension has calculated members

Can you please clarify in more details what is the special about calculated members so that you want to know in advance whether hierarchy has them.

|||

Hi Mosha,

Thanks for the reply, I believe our very own Dave Grant showed you some of RSinteract’s MDX at PASS in Seattle.

We decided that placing the filters in the sub-query allowed us to provide more user friendly totalling (i.e. when filtering row or column attributes). However calculated members are not allowed in sub-queries, so we need to know which Dimension Attributes contain Calc Members and place any filters inline or in a where clause. Our current solution is to have an administrator run an AMO application which saves dimensions with calc members to a config file (AMO must be run with admin privileges).

Cheers,

James

|||What I wonder is why do you want to exclude the entire hierarchy just because it may have calculated members. It seems like if you just wrap the sets that you put inside subselects with StripCalculatedMembers function call, both you and your users will be better of.

Find a missing number ?

We have a database table, with a field ( int (4) ) , we'll call it
transid.
The field is being populated by an unknown random formula in a third party
program.
I have another program that i am writing that needs to insert records into
this table.
This field, transid must be unique.
I started in MY program with a function that said "select max(transid) from
table" , newid=max(transid) + 1, insert into table (newid,blah blah blah)
Problem being that because the OTHER program is using random numbers, it
eventually put in a value that is the max length of an int(4) field.
SO - i changed my syntax to be newid=(select max(transid) from table where
transid<1000000)+1 - works fine for a while until the other program came
along and inserted a value of 9999996 - so then, only my next four
transactions worked until i hit 9999999 and then the number kept repeating,
no longer unique, insert fails.
SO my question - is there a sql function that would say:
"Find me a number that is not already used in the field transid in table" '
Im trying to figure with EXISTS, NOT EXISTS, etc, but i cant make anything
work.
Appreciate any assistance !
Thanks,
M.Coz wrote:

> SO my question - is there a sql function that would say:
> "Find me a number that is not already used in the field transid in
> table" ' Im trying to figure with EXISTS, NOT EXISTS, etc, but i
> cant make anything work.
See this thread
SQL Problem: How to find UNUSED numbers
http://groups.google.pl/groups?hl=p...&rnu
m=1
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Find a missing number ?

We have a database table, with a field ( int (4) ) , we'll call it
transid.
The field is being populated by an unknown random formula in a third party
program.
I have another program that i am writing that needs to insert records into
this table.
This field, transid must be unique.
I started in MY program with a function that said "select max(transid) from
table" , newid=max(transid) + 1, insert into table (newid,blah blah blah)
Problem being that because the OTHER program is using random numbers, it
eventually put in a value that is the max length of an int(4) field.
SO - i changed my syntax to be newid=(select max(transid) from table where
transid<1000000)+1 - works fine for a while until the other program came
along and inserted a value of 9999996 - so then, only my next four
transactions worked until i hit 9999999 and then the number kept repeating,
no longer unique, insert fails.
SO my question - is there a sql function that would say:
"Find me a number that is not already used in the field transid in table" '
Im trying to figure with EXISTS, NOT EXISTS, etc, but i cant make anything
work.
Appreciate any assistance !
Thanks,
M.Coz wrote:
> SO my question - is there a sql function that would say:
> "Find me a number that is not already used in the field transid in
> table" ' Im trying to figure with EXISTS, NOT EXISTS, etc, but i
> cant make anything work.
See this thread
SQL Problem: How to find UNUSED numbers
http://groups.google.pl/groups?hl=pl&lr=&ie=UTF-8&oe=UTF-8&th=a3eb815a529ae1c5&rnum=1
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.