Hi
I am working on GM V6.0, my problem sounds easy, but is proving to me more difficult, for me at least, than i thought.
I want to create a report that lists all of our prospects
and tells me when the were last called and when they are next scheduled to be called.
I have got so far, and have written enough to be able to pull the dates, but not the codes or userid's as i am using the max function to get the dates
Can anybody help me I've been staring at this for the last 7 hours
select a1.accountno, a1.company,a1.phone1,a1.country,a1.key1,a1.key2,a1.key3,a3.umobnetsup,
a3.usysinstal,max (a4.ondate)as 'lastcall',max(a4.recid) as 'histrecid',min(a2.ondate)as 'nextcall',min(a2.recid) as 'pendrecid'
from contact1 a1,cal a2,contact2 a3,conthist a4
where a1.accountno *= a2.accountno and a1.accountno *= a3.accountno and a1.accountno *= a4.accountno
group by a1.accountno,a1.company ,a1.phone1,a1.country,a1.key1,a1.key2,a1.key3,a3.umobnetsup,a3.usysinstal
order by a1.company,a1.accountno,min(a2.ondate)desc
thanks
I am working on GM V6.0, my problem sounds easy, but is proving to me more difficult, for me at least, than i thought.
I want to create a report that lists all of our prospects
and tells me when the were last called and when they are next scheduled to be called.
I have got so far, and have written enough to be able to pull the dates, but not the codes or userid's as i am using the max function to get the dates
Can anybody help me I've been staring at this for the last 7 hours
select a1.accountno, a1.company,a1.phone1,a1.country,a1.key1,a1.key2,a1.key3,a3.umobnetsup,
a3.usysinstal,max (a4.ondate)as 'lastcall',max(a4.recid) as 'histrecid',min(a2.ondate)as 'nextcall',min(a2.recid) as 'pendrecid'
from contact1 a1,cal a2,contact2 a3,conthist a4
where a1.accountno *= a2.accountno and a1.accountno *= a3.accountno and a1.accountno *= a4.accountno
group by a1.accountno,a1.company ,a1.phone1,a1.country,a1.key1,a1.key2,a1.key3,a3.umobnetsup,a3.usysinstal
order by a1.company,a1.accountno,min(a2.ondate)desc
thanks
