need to create a complex stored procedure

[DataDivaJill]

i need a stored procedure to garb info from 3 goldmine tables

first i want all contacts from contact1 table.
then i want all history for those records from the conthist table.
but i also want all pending activity from the calendar table.
they are all linked via gm accountno.
from contact1 table want:
contact1.accountno,contact1.company,contact1.contact

from calendar table i want:
cal.accountno,cal.ondate,cal.userid,cal.rectype

from the history table i want:
conthist.accountno,conthist.ondate,conthist.userid,conthist.rectype

can anyone help me with this??
my goal is to then create a crystal report from all that data.
sam smith , abc company had a pending call on 2/4/2004 and had a history appointment on 1/31/2004

so i want only contacts that have either history or calendar.

jill messier
computer control corp
8605296232

 

[markVII]

There is nothing complex about this, or I might be missing something... create a procedure that accepts your parameters and returns resultset of the following query:
select
c1.accountno
,c1.company
,c1.contact
,cal.accountno
,cal.ondate
,cal.userid
,cal.rectype
,hist.accountno
,hist.ondate
,hist.userid
,hist.rectype
from calendar cal, history hist, contact1 c1
[your JOIN conditions]
where cal.ondate = 1/31/2004
and hist.appointment= 1/31/2004
and c1.company = 'abc'
and c1.contact = 'Smith'

 

[DataDivaJill]

that is good but what if it is more than one company and i want a data range for both. say i want all cal and hist for the year 2004. and all contacts with it.

 

[markVII]

Nothing mysterious there either:
-----------------
and c1.company IN('abc','def','hij')
and DATEPART(YEAR,cal.ondate)= '2004'
and DATEPART(YEAR,hist.appointment)= '2004'
-----------------
---and just forget the contacts
The only problem I can see is that SQL Server does not allow you to use array, so you would have to come up with some idea to specify range of company names
(I would use subquery)

 

[DataDivaJill]

ok i tested your query like this.
select
c1.accountno
,c1.company
,c1.contact
,cal.accountno
,cal.ondate
,cal.userid
,cal.rectype
,hist.accountno
,hist.ondate
,hist.userid
,hist.rectype
from cal, conthist hist, contact1 c1
where c1.accountno='A4032457726%740+@Tek'
and cal.accountno ='A4032457726%740+@Tek'
and hist.accountno='A4032457726%740+@Tek'
and the record shows calendar and history but nothing is returned in the query analizer.

 

[markVII]

Where is your JOIN condition? Without it you are bound to get a cartesian product (cross-join).

something like this:

where cal.field = hist.field and hist.field=c1.field

or, if you prefer SQL92/99 syntax:

from cal JOIN hist ON field1=field2 JOIN c1 ONfield=field

And if after that nothing shows in QA, then there are no records satisfying criteria (which I suspect is the case, since you did not get the cartesian product)