can i avoid cursor? - 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)

can i avoid cursor?

Author

Topic

safderalimd
Starting Member

32 Posts

Posted - 2008-07-16 : 15:19:40

How can I convert result set I into result set II ?

DDL and sample data script provided.

I am basically looking for set of admit and discharge records for a given client. If a client has more than one admit records before he discharges then I need max_id(row id) and min_id(row id) of admits. Else, in case of one admit record before discharge then max_id and min_id for admit record will be same. Same applies to discharge records.

RESULT SET - I
(id) (client) (action)
--------------------------------------------
(1) (100) (admit
(2) (100) (admit
(3) (100) (admit)
(4) (100) (discharge)
(5) (100) (admit)
(6) (100) (discharge)
(7) (200) (admit)
(8) (200) (discharge)
(9) (300) (admit)
(10) (300) (discharge)
(11) (300) (discharge)

RESULT SET II
(min_id) (max_id) (client) (action)
----------------------------------------------------------------------
(1) (3) (100) (admit)
(4) (4) (100) (discharge)
(5) (5) (100) (admit)
(6) (6) (100) (discharge)
(7) (7) (200) (admit)
(8) (8) (200) (discharge)
(9) (9) (300) (admit)
(10) (11) (300) (discharge)

create table #census(id int, client int, action varchar(20))

insert into #census values(1,100,'admit')
insert into #census values(2,100,'admit')
insert into #census values(3,100,'admit')
insert into #census values(4,100,'discharge')

insert into #census values(5,100,'admit')
insert into #census values(6,100,'discharge')

insert into #census values(7,200,'admit')
insert into #census values(8,200,'discharge')

insert into #census values(9,300,'admit')
insert into #census values(10,300,'discharge')
insert into #census values(11,300,'discharge')

select * from #census order by client, id

Edited: Today @ 8:17 PM by ALI

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-16 : 16:13:46

[code]-- Peso
alter table #census add seq int
create clustered index ix_peso on #census (id)

declare @seq int, @action varchar(20), @client int

select top 1 @action = action,
@client = client,
@seq = 0
from #census
order by id

update #census
set @seq = seq = case when action = @action and client = @client then @seq else @seq + 1 end,
@action = action

select min(id) as min_id, max(id) as max_id, client, action
from #census
group by client, action, seq
order by min(id)

drop table #census[/code]

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-16 : 16:55:16

See:

http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data

I believe there is a more efficient way to do this in SQL 2005+ with ROW_NUMBER(), I will try to find the link. though I am sure Peso probably knows!

- Jeff
http://weblogs.sqlteam.com/JeffS

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-07-16 : 20:28:25

[code]
select min(aa.ID) as Min_ID,Max(aa.ID) as Max_ID,aa.[Action],aa.Client
from
(Select *
, coalesce((
select top 1 b.ID
from
#Census b
where
b.Action = 'Discharge'
and b.ID >= a.ID
and a.Client = b.Client
order by b.ID asc
),a.ID) as Dis
From #Census a
) aa
group by aa.[Action],aa.[Dis],aa.client
order by Max_ID
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-17 : 00:41:27

Peso & Vinnie
Your solutions are not returning the expected output of op
here's my solution:-

select c.client,c.action,min(b.mn) as mn,b.mx from #census c
outer apply (select min(id) as mn
             from #census
             where (client=c.client
             and action<>c.action)
             and id>c.id)a
cross apply (select min(id) as mn,max(id) as mx
             from #census
             where client=c.client
             and action=c.action
			and id>=c.id
			 and id<coalesce(a.mn,99999))b
group by c.client,c.action,b.mx
order by mn


drop table #census
output
---------------------
client      action               mn          mx
----------- -------------------- ----------- -----------
100         admit                1           3
100         discharge            4           4
100         admit                5           5
100         discharge            6           6
200         admit                7           7
200         discharge            8           8
300         admit                9           9
300         discharge            10          11

Peso's & Vinnie's output

min_id      max_id      client      action
----------- ----------- ----------- --------------------
1           3           100         admit
4           4           100         discharge
5           5           100         admit
6           6           100         discharge
7           7           200         admit
8           8           200         discharge
9           9           300         admit
10          10          300         discharge
11          11          300         discharge

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-17 : 01:58:23

quote:
Originally posted by visakh16

Peso & Vinnie
Your solutions are not returning the expected output of op

My bad.
But just add the protion id red and my suggestion blows the socks off your solution.

update	#census
set	@seq = seq = case when action = @action and client = @client then @seq else @seq + 1 end,
	@action = action,
	@client = client

E 12°55'05.25"
N 56°04'39.16"

safderalimd
Starting Member

32 Posts

Posted - 2008-07-17 : 08:52:59

Thank you guys!!!.

safderalimd
Starting Member

32 Posts

Posted - 2008-07-17 : 09:03:36

I never heard of outer apply and cross apply in T-SQL. Is there any book that covers most of T-SQL techniques?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-17 : 09:18:24

Yes. Books Online.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources