Set-based approach for getting a date range from mult rows

Question

Set-based approach for getting a date range from mult rows

Dave-3000

SSCertifiable

Points: 6186
More actions
February 18, 2008 at 10:35 am

#62339

Hi all,
I have a table of employees (about 1400 rows) that shows which pay periods the employee was eligible for profit sharing. I need to combine the rows of consecutive eligibility so that they are combined into a row with a start and end date for eligibility. When column Eligible = 1, they are eligible for profit sharing during that pay period. As you can see, the eligibility can change from period to period.
DECLARE @Rows TABLE (Company char(3), EmployeeID char(8), BegDate datetime, EndDate datetime, Eligible smallint)
INSERT INTO @Rows VALUES ('914', '91406103', '01/01/2002', '10/30/2002', 0)
INSERT INTO @Rows VALUES ('914', '91406103', '10/31/2002', '12/30/2003', 1)
INSERT INTO @Rows VALUES ('914', '91406103', '12/31/2003', '04/12/2004', 1)
INSERT INTO @Rows VALUES ('914', '91406103', '04/13/2004', '07/01/2004', 0)
INSERT INTO @Rows VALUES ('914', '91406103', '07/02/2004', '09/14/2004', 0)
INSERT INTO @Rows VALUES ('914', '91406103', '09/15/2004', '01/31/2006', 1)
INSERT INTO @Rows VALUES ('914', '91406103', '02/01/2006', '05/02/2007', 1)
INSERT INTO @Rows VALUES ('914', '91406103', '05/03/2007', '05/13/2007', 1)
INSERT INTO @Rows VALUES ('914', '91406103', '05/14/2007', '12/31/2199', 0)
The rows where Eligibility = 1 for this employee would collapse into two rows, like so:
INSERT INTO @Rows VALUES ('914', '91406103', '10/31/2002', '04/12/2004', 1)
INSERT INTO @Rows VALUES ('914', '91406103', '09/15/2004', '05/13/2007', 1)
I can visualize a RBAR process (Hi Jeff!) but surely there is a set based method that will work.
Any ideas? Thanks.
There is no "i" in team, but idiot has two.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Well - this is an adaptation of the "running totals update" algorithm. It's not exactly set-based, but it certainly does go tearing through the process quite efficiently.

drop table #mytable

DECLARE @Rows TABLE (Company char(3), EmployeeID char(8), BegDate datetime, EndDate datetime, Eligible smallint)

INSERT INTO @Rows VALUES ('914', '91406103', '01/01/2002', '10/30/2002', 0)

INSERT INTO @Rows VALUES ('914', '91406103', '10/31/2002', '12/30/2003', 1)

INSERT INTO @Rows VALUES ('914', '91406103', '12/31/2003', '04/12/2004', 1)

INSERT INTO @Rows VALUES ('914', '91406103', '04/13/2004', '07/01/2004', 0)

INSERT INTO @Rows VALUES ('914', '91406103', '07/02/2004', '09/14/2004', 0)

INSERT INTO @Rows VALUES ('914', '91406103', '09/15/2004', '01/31/2006', 1)

INSERT INTO @Rows VALUES ('914', '91406103', '02/01/2006', '05/02/2007', 1)

INSERT INTO @Rows VALUES ('914', '91406103', '05/03/2007', '05/13/2007', 1)

INSERT INTO @Rows VALUES ('914', '91406103', '05/14/2007', '12/31/2199', 0)

Select *,0 as periodNum into #myTable

from @rows

create unique clustered index ix_mytbl on #mytable(company,employeeID,begdate)

--"update method"

declare @previd char(11)

declare @period int

declare @prevEndDate datetime

set @previd=''

update #mytable

set @period=periodnum=case when @previd=company+employeeID and begdate=@prevenddate then @period

when @previd=company+employeeID and begdate>@prevenddate then @period+1

else 1 end,

@previd=company+employeeID,

@prevenddate=enddate+1

from #mytable with(index(ix_mytbl),tablockX)

where eligible=1

select company,employeeID,periodnum,min(begdate),max(enddate) from #mytable

where eligible=1

group by company,employeeID,periodnum

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004645 More actions · Answer 2

Heh... the method certainly is useful for a lot of things that would otherwise require a cursor... and a heck of a lot faster, too!

Matt, that's perfect... the only thing that I might change is to add a criteria to where the temp table is made to only include elegible rows and remove it from the places that follow...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Jeff Moden (2/18/2008)
Heh... the method certainly is useful for a lot of things that would otherwise require a cursor... and a heck of a lot faster, too!
Matt, that's perfect... the only thing that I might change is to add a criteria to where the temp table is made to only include elegible rows and remove it from the places that follow...

You're right - I noticed the "I only want to talk about eligible" criterion half-way through....Should have bounced the excess records earlier....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 4

Thanks very much.

There is no "i" in team, but idiot has two.