1st Transaction - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

1st Transaction

Author

Topic

sunrag77
Starting Member

8 Posts

Posted - 2009-08-17 : 05:47:18

Hi

i have compiled a query for my service calls. For every call id there will be multiple activity. I want to know my 1st activity of my every call id. My query is giving all the activities related to service call. below is the query compiled me, can anyone help me to retrieve the 1st activity of my each call id.

select oscl.custmrname, oscl.callid,oscl.createdate,oscl.assigndate, oclg.clgcode, oclg.cntctdate,
datediff(dd,oscl.assigndate,oclg.cntctdate) as Diff, oscl.closedate, ohem.firstname, oscs.name
from oscl left join
oclg on oscl.callid = oclg.parentid left join
ohem on oscl.technician = ohem.empid left join
oscs on oscl.status = oscs.statusid
here oscl.callid is service call id and oclg.clgcode is activity no

Pl help
Thanks
sundaram

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-08-17 : 05:51:26

Which is used for the first activity? createdate,assigndate or cntctdate?

Whichever it is, to find the first, you will want to use either TOP 1 and sort the results descending on the date or use MIN() to get the lowest date.

sunrag77
Starting Member

8 Posts

Posted - 2009-08-17 : 06:00:35

Hi
Thanks for your response.

For every call one or more activity created and attached. i need the 1st activity of every call. 1st Call id date ie is create date will be created and if there is an activity that will created and that activity will be attached to that call and activity date will get stored in oclg table.

Pl help.

Regards
Sundaram

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-17 : 07:39:37

seems like this


select oscl.custmrname, oscl.callid,oscl.createdate,oscl.assigndate, oclg.clgcode, oclg2.cntctdate,
datediff(dd,oscl.assigndate,oclg.cntctdate) as Diff, oscl.closedate, ohem.firstname, oscs.name 
from oscl left join 
(select o1.* from oclg o1
join (select parentid,min(cntctdate) as first
from oclg
group by parentid) o2
on o2.parentid=o1.parentid
and o2.first=o1.cntctdate
)oclg2
on oscl.callid = oclg2.parentid left join
ohem on oscl.technician = ohem.empid left join
oscs on oscl.status = oscs.statusid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources