July 22, 2010 at 2:44 pm
I'm at a point beyond my coding abilities.
I have a table with thousands of members and their effective and end enrollment dates. I need to find which where continuously enrolled with less than 30 day gap between last end date and new enroll day and then for those not continously enrolled I need the latest membership span.
Someone mentioned using cursors but I have not idea what they are talking about.
Example
Pt not continuously enrolled (need last continuous date span)
Pt Nbr Eff Date end date
49700 2006120120090930 --
497002009120120091231
497002010010120100531
pt continuously enrolled ( no gap in enrollment)
Pt Nbr Eff Date end date
497022007120120071231
4970220080101
Any help would be appreciated.:crazy:
July 22, 2010 at 3:02 pm
I'd probably use a CTE(common table expression)/subquery with ROW_NUMBER() and a self join to it. Then I would check for DATEDIFF(dd, cte1.enddate, cte2.EffDate)<30.
If you'd like to see a coded example please provide table definition, sample data and expected result in a ready to use format as described in the first link in my signature.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply