December 11, 2006 at 10:26 am
I have a table with the following structure:
MemberNbr varchar(11)
StartDate int
EndDate int
PlanCode varchar(3)
Sample Data:
MemberNbr StartDate EndDate Plan
12345678901 20050101 20050501 ABC
12345678901 20050501 20051231 BBB
12345678901 20060401 0 BBB
I need to select each member record where the StartDate is the earliest
for the member where there is no prvious break in service. In this case, the record needed would be
MemberNbr StartDate EndDate Plan
12345678901 20060401 0 BBB
since the prior record ended before the current record started.
December 11, 2006 at 11:06 am
How about
SELECT MAX(startdate), MemberNbr FROM MemberNbr GROUP BY MemberNbr
December 11, 2006 at 11:12 am
That would work for those records I sampled. But what about records which
exists in the sample where the 3rd record is not there. In that case, the desired record would be :
MemberNbr StartDate EndDate Plan
12345678901 20050101 20050501 ABC
December 11, 2006 at 11:25 am
How about something like:
SELECT MAX(startdate), MemberNbr FROM MemberNbr GROUP BY MemberNbr HAVING A <> 0
December 11, 2006 at 11:40 am
For problems like this, you typcially solve them by joining to a derived table which gives you the required record for each group:
Select t.MemberNbr, t.StartDate, t.EndDate, t.Plan
From YourTable As t
Inner Join
-- Join derived table
(
Select MemberNbr, Min(StartDate) As EarliestDate
From YourTable
Group By MemberNbr
) dt
On (t.MemberNbr = dt.MemberNbr And
t.StartDate = dt.EarliestDate)
-- And check for break in service
Where Not Exists
(
Select *
From YourTable As t2
Where t.MemberNbr = t2.MemberNbr
And t.StartDate = t2.EndDate
)
December 11, 2006 at 11:41 am
DECLARE @T TABLE(MemberNbr varchar(11),StartDate int,
EndDate int,PlanCode varchar(3))
INSERT INTO @T
SELECT '12345678901', 20050101, 20050501, 'ABC'
UNION ALL SELECT '12345678901', 20050501, 20051231, 'BBB'
UNION ALL SELECT '12345678901', 20060401, 20060601, 'BBB'
--UNION ALL SELECT '12345678901', 20060601, 0 , 'BBB'
SELECT * FROM @T
SELECT A.MemberNbr,MAX(A.StartDate) StDate
FROM
@T A LEFT OUTER JOIN @T B
ON A.MemberNbr=B.MemberNbr and A.StartDate=b.EndDate
WHERE B.MemberNbr IS NULL
GROUP BY A.MemberNbr
Vasc
December 11, 2006 at 12:21 pm
Thank you all for your prompt and workable solutions.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply