CODEWITHSUNDEEP
sqlsql-serverwhile-loop
Wolves
Wolves

Reputation: 515

Replace 'while' with set-based operation for where

I'm using SQL server 2012. I have a query which will look at a given month and determine which members are active/effective during that month, by checking the enrollment span for that member for the given month. Here's what the query looks like:

select monthstart, monthend
into #tmp 
from monthtable --This table contains the month start and end dates I need

create table #result(ID varchar(20), monthstart varchar(8))

declare @eff varchar(8)
declare @end varchar(8)

while (select count(*) from #tmp) > 0
begin

select top 1 @eff = monthstart from #tmp order by monthstart
select top 1 @end = monthend from #tmp order by monthstart

insert into #result
select ID, @eff
from members
where ymdeff <= @end and ymdend >= @eff -- Ensures the member is active for at least one day in this month

end

select * from #result

I'm looking at a set of about 50 members for about 8 months. This query has been running since I started writing this post, and it's still going. I think it will get to the right answer eventually, but it's taking too long. This seems like it should be simple, but I can't think of any other way to do this, except writing out the where statement for each month, or using dynamic sql which would still be iterative rather than set-based. Is there any way for me to get this done more quickly?

Thank you for your help!

Upvotes: 0

Views: 98

Answers (3)

David Rushton
David Rushton

Reputation: 5040

It's easier to answer these when the question includes sample data and expected output. So please accept my apologies if I've misinterpreted your schema.

Query

SELECT
    m.ID,
    mt.MonthStart
FROM
    MonthTable mt
        INNER JOIN Members m        ON m.ymdeff BETWEEN mt.MonthStart AND mt.MonthEnd
GROUP BY
    m.ID,
    mt.MonthStart
;

This query joins the MonthTable directly to the members table.

Upvotes: 1

Ajay
Ajay

Reputation: 785

The loop you have created is infinite . You need to delete the data from #tmp on each iteration .

while (select count(*) from #tmp) > 0
begin
select top 1 @eff = monthstart from #tmp order by monthstart
select top 1 @end = monthend from #tmp order by monthstart

insert into #result
select ID, @eff
from members
where ymdeff <= @end and ymdend >= @eff -- Ensures the member is active  
for at least one day in this month

delete #tmp where monthstart=@eff and monthend =@end

end

instead of loop you can use joins as below 

select m.ID, t.monthstart from members m , #tmp t where m.ymdeff <= t.monthstart  and m.ymdend >= t.monthend

Upvotes: 2

Tom H
Tom H

Reputation: 47402

As @Ajay says, you forgot to DELETE rows from your temporary table as you loop, so you're stuck in an infinite loop.

To avoid the loop altogether though (which should almost always be done when possible in SQL):

SELECT
    M.id,
    T.monthstart
FROM
    #tmp T
INNER JOIN Members M ON M.ymdeff <= T.monthend AND M.ymdend >= T.monthend

I hope that ymdeff isn't a date sitting in the DB as a VARCHAR though...

Upvotes: 2

Related Questions