How to determine first valid enrollment date

Question

How to determine first valid enrollment date

jwmott

SSC Eights!

Points: 979
More actions
January 11, 2011 at 1:25 pm

#230025

Hello everyone,
I have a data set that contains 5 records.
create table #myTable
(clientId int, enrollDate smalldatetime, exitdate smalldatetime, fundingsource int)
insert into #myTable values(1,'2009-03-23','2010-07-31',1)
insert into #myTable values(1,'2010-08-01','2010-08-31',2)
insert into #myTable values(1,'2010-09-01','2010-09-03',1)
insert into #myTable values(1,'2009-09-07',NULL,1)
I need to pick the earliest enrolldate for each funding source when there is no interruption in funding sources. So in the data example I would need to pick 9/1/2010 for the earliest enrolldate for funding source 1 and 8/1/2010 for funding source 2. Even though I have an enroll date of 3/23/2009 for funding source 1 the record doesn't count because there is a different funding source recorded in the middle of the set.

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

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

Heh... it's not as easy as it looks, huh?

Thank you for setting up the sample data correctly. This should do it for you... the CTE's are named after what they do.

WITH

cteEnumerateGroups AS

(

SELECT FundingGroup = ROW_NUMBER() OVER (ORDER BY EnrollDate)

- ROW_NUMBER() OVER (PARTITION BY FundingSource ORDER BY EnrollDate),

EnrollDate, FundingSource

FROM #MyTable

),

cteEnumerateRowsByGroupDate AS

(

SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY FundingSource ORDER BY FundingGroup DESC, EnrollDate ASC),

EnrollDate, FundingSource

FROM cteEnumerateGroups

)

SELECT FundingSource, EnrollDate

FROM cteEnumerateRowsByGroupDate

WHERE RowNum = 1

;

--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)

jwmott SSC Eights! Points: 979 More actions · Answer 2

Thanks. It looks like it does exactly what I need. I would like to learn more about how this works. Got any good reference sources to recommend?

WayneS SSC Guru Points: 95409 More actions · Answer 3

jwmott (1/12/2011)
Thanks. It looks like it does exactly what I need. I would like to learn more about how this works. Got any good reference sources to recommend?

I'm taking a guess here, but you're probably asking about what the common table expressions and row_number() functions are doing.

The best reference sources for these are BOL - just highlight "WITH" or "ROW_NUMBER()", and press the F1 key.

You might also want to read this article on SQL Server Ranking Functions[/url].

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

jwmott SSC Eights! Points: 979 More actions · Answer 4

I now have learned what WITH does and ROW NUMBER. On a quest to figure out OVER and PARTITION.

I am now trying to apply the suggested solution to a dataset with more than one client. The solution provided seems to provide the earliest enrollment date in each possible funding source for a single client. I need to provide the earliest enrollment date for each funding source for all clients

drop table #myTable

create table #myTable(clientId int, enrollDate smalldatetime, exitdate smalldatetime, fundingsource int)

insert into #myTable values(1,'2009-03-23','2010-07-31',1)

insert into #myTable values(1,'2010-08-01','2010-08-31',2)

insert into #myTable values(1,'2010-09-01','2010-09-03',1)

insert into #myTable values(1,'2009-09-07',NULL,1)

insert into #myTable values(2,'2009-02-17','2010-09-03',1)

insert into #myTable values(2,'2010-09-07',NULL,1)

Thanks to everyone who provides help. I have learned a lot and I greatly appreciate the help.