Looping Through Result sets -- Curor or Variables

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 2008 Forums

Transact-SQL (2008)

Looping Through Result sets -- Curor or Variables

Author

Topic

wbyoure1
Starting Member

3 Posts

Posted - 2011-12-07 : 16:34:13

I have an issue where in need to loop through results set several times and needed some feedback. The results set will include a member and admit date. I pull the results and rank them with the first admit date that occured as an Index Admission. If the person has 4 admissions it is possible to have more than 1 index admission. The logic for this would be that if the 2nd admit occurred less than 30 days from the index admission then it would just be classified as other. See example below
Member Rank Admit Index Comments
1 1 4/28/2001 Yes 1st admit always Index
1 2 5/20/2008 No < 30 days from Index
1 3 5/31/2008 Yes > 30 days from Index
1 4 6/15/2008 No < 30 days from New Index Date 5/31/2008
Let me know if this makes sense and if you think you can help. Many thanks!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-12-07 : 19:58:53

[CODE]declare @Admissions table (
Member int,
Rank int,
dtAdmit date
)

insert into @Admissions (
Member,
Rank,
dtAdmit
)
values
(1, 1, '4/28/2008'), -- Assuming this was a typo
(1, 2, '5/20/2008'),
(1, 3, '5/31/2008'),
(1, 4, '6/15/2008')

--select * from @Admissions

;with AdmitWIndex
as (
select Member, Rank, dtAdmit, dtAdmit lastTrueAdmit
from @Admissions
where Rank = 1

union all

select
nxt.Member,
nxt.Rank,
nxt.dtAdmit,
case when DATEDIFF(Day, this.lastTrueAdmit, nxt.dtAdmit) < 30
then this.dtAdmit else nxt.dtAdmit end
from
AdmitWIndex this
inner join
@Admissions nxt
on this.member = nxt.Member
and this.Rank = nxt.Rank - 1
)
select
Member,
Rank,
dtAdmit,
case when dtAdmit = lastTrueAdmit then 1 else 0 end Indx
from
AdmitWIndex[/CODE]Why loop?

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

wbyoure1
Starting Member

3 Posts

Posted - 2011-12-08 : 09:21:35

Thanks for the quick response and definitely points me in the right direction, will need to tweak the logic a bit as in this scenario members 222 and 333 are correct but if you look at the result set using updated values member 111 is not correct as the 4th admit should be an index as it's >30 from the inital index admission on 4/28/2008, while the other 2 admits for member 111 were <30 days they would not be considered and index.

(111, 1, '4/28/2008'), -- Assuming this was a typo
(111, 2, '5/20/2008'),
(111, 3, '5/24/2008'),
(111, 4, '6/15/2008'),
(222, 2, '10/14/2011'),
(222, 1, '9/13/2011'),
(333, 1, '7/15/2009'),
(333, 2, '7/20/2009'),
(333, 3, '8/19/2009'),
(333, 4, '8/30/2009'),
(333, 5, '9/10/2009'),
(333, 6, '9/29/2009')

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-12-08 : 11:48:14

Found it!

Replace the case statement in the CTE with this:[CODE] case when DATEDIFF(Day, this.lastTrueAdmit, nxt.dtAdmit) < 30
then this.lastTrueAdmit else nxt.dtAdmit end[/CODE]Bug fixes are rarely brain surgery events. This one was due to a bad case of cranial-rectitus on my part, alas.

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

wbyoure1
Starting Member

3 Posts

Posted - 2011-12-08 : 16:10:44

yes thanks so much I had coded it in vba and worked ok but now to get this all in sql is a big help!!!!!!

Thanks again....Here is the working code all in one place for anyone else that may need a reference:

declare @Admissions table (
Member int,
Rank int,
dtAdmit date
)

insert into @Admissions (
Member,
Rank,
dtAdmit
)
values
(111, 1, '4/28/2008'),
(111, 2, '5/20/2008'),
(111, 3, '5/24/2008'),
(111, 4, '6/15/2008'),
(222, 2, '10/14/2011'),
(222, 1, '9/13/2011'),
(333, 1, '7/15/2009'),
(333, 2, '7/20/2009'),
(333, 3, '8/19/2009'),
(333, 4, '8/30/2009'),
(333, 5, '9/10/2009'),
(333, 6, '9/29/2009')
--select * from @Admissions) x

;with AdmitWIndex
as (
select Member, Rank, dtAdmit, dtAdmit lastTrueAdmit
from @Admissions
where Rank = 1

union all

select
nxt.Member,
nxt.Rank,
nxt.dtAdmit,
case when DATEDIFF(Day, this.lastTrueAdmit, nxt.dtAdmit) < 30
then this.lastTrueAdmit else nxt.dtAdmit end
from
AdmitWIndex this
inner join
@Admissions nxt
on this.member = nxt.Member
--and DATEDIFF(day,this.dtadmit,nxt.dtadmit)>30
and this.Rank = nxt.Rank - 1 --org code
)
select
Member,
Rank,
dtAdmit,
case when dtAdmit = lastTrueAdmit then 1 else 0 end Indx
from
AdmitWIndex
order by Member, rank

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources