Query to retrieve 1st record of my service call

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

SQL Server 2005 Forums

Transact-SQL (2005)

Query to retrieve 1st record of my service call

Author

Topic

sunrag77
Starting Member

8 Posts

Posted - 2009-08-17 : 07:35:50

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

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.

Sachin.Nand

2937 Posts

Posted - 2009-08-17 : 07:41:01

[code]
select * from
(
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,
ROW_NUMBER()over(partition by callid order by clgcode)as rowid
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
)T where rowid=1
[/code]

PBUH

sunrag77
Starting Member

8 Posts

Posted - 2009-08-17 : 07:51:42

Thanks its working great.
Regards
Sundaram

Sachin.Nand

2937 Posts

Posted - 2009-08-17 : 07:52:43

quote:
Originally posted by sunrag77

Thanks its working great.
Regards
Sundaram

My pleasure I could help.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources