A better way of writing this - 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

SQL Server 2005 Forums

Transact-SQL (2005)

A better way of writing this

Author

Topic

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2009-10-09 : 08:08:44

Hi All

Good day, good people of sqlteam. I have a table of workflow like process.

Kindly see below

req_id type startdate enddate
1 Approval 20/02/09 25/02/09
1 Response 28/02/09 28/02/09
1 Resolution 28/02/09 null

and I want an output like this

req_id approval_startdate approval_enddate response_startdate response_enddate resolution_startdate resolution_enddate
1 20/02/09 25/02/09 28/02/09 28/02/09 28/02/09 null

my approach

select
distint req_id
,approval_startdate = (select startdate from tablename where req_id =a.req_id and type= 'approval')
,approval_enddate = (select enddate from tablename where req_id =a.req_id and type= 'approval')
,response_startdate = (select startdate from tablename where req_id =a.req_id and type= 'response')
,response_enddate = (select enddate from tablename where req_id =a.req_id and type= 'response')
,resolution_startdate = (select startdate from tablename where req_id =a.req_id and type= 'resolution')
,resolution_enddate = (select enddate from tablename where req_id =a.req_id and type= 'resolution')
from
table name

Kindly show me another method of arriving at that without using my method.

Thanks a bunch

I sign for fame not for shame but all the same, I sign my name.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-09 : 08:21:18

select req_id,
max(case when type= 'approval' then startdate end) as approval_startdate,
max(case when type= 'approval' then enddate end) as approval_enddate,
.
.
from your_table
group by req_id

Madhivanan

Failing to plan is Planning to fail

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2009-10-09 : 09:02:02

Thanks a bunch..

My first approach took 7 secs to complete
This Mr.Madhivanan yours took 1 sec.. Yes, that's what am looking for :PERFORMANCE:

Thanks A LOT

I sign for fame not for shame but all the same, I sign my name.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-09 : 09:44:47

quote:
Originally posted by abacusdotcom

Thanks a bunch..

My first approach took 7 secs to complete
This Mr.Madhivanan yours took 1 sec.. Yes, that's what am looking for :PERFORMANCE:

Thanks A LOT

I sign for fame not for shame but all the same, I sign my name.

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources