I am pretty new to working in SQL at and have a gaps and islands problem in which I am trying to get a series of consecutive months of membership. Any member may have multiple memberships and their membership may have gaps or overlap. My thought was to order memberships by member and plan and work backward within this group month by month until I find a gap, then assign the begin and end date of the membership.
Here's a sampling of my data:
Member YYYYMM Plan 1 201601 123 1 201602 123 2 201505 123 2 201506 123 2 201510 123 2 201511 123 2 201512 123 1 201510 456 1 201511 456 1 201512 456 1 201601 456
The above data would represent memberships for two separate members to same/different plans for different time spans. Now, I need to get it to look more like this:
Member YYYYMM_Begin YYYYMM_End Plan 1 201601 201602 123 2 201505 201506 123 2 201510 201512 123 1 201510 201601 456
So far, I've got a recursive join that partitions based on Member and Plan and ranks them in descending order by YYYYMM, which seems to be working. But I'm stuck on how to figure out the begin and end dates for consecutive months of membership.
SELECT DISTINCT A.Member, A.Plan, A.YYYYMM, B.Rank FROM Member_Hist A JOIN (SELECT MH.Member, MH.Plan, MH.YYYYMM, RANK() OVER (PARTITION BY Member, Plan ORDER BY YYYYMM DESC) AS Rank FROM Member_Hist MH) B ON A.Member = B.Member AND A.Plan = B.Plan ORDER BY Member ASC, Plan ASC, YYYYMM DESC
Edit: I'm using Oracle, not MySQL; Also, I only have read permission. Edit: Sorry, NOT using MySQL or SQL Server