Help with Self Join

We have a table with following information.
MemberName FillDate DaysSupply
PETER GAMBINI 09/22/05 30
PETER GAMBINI 09/24/05 30
PETER GAMBINI 12/25/05 30
MARIA ROSA 10/03/05 15
MARIA ROSA 10/18/05 30
MARIA ROSA 11/18/05 30
DAN JONES 04/01/05 5
DAN JONES 04/30/05 30
DAN JONES 04/30/05 10
I am trying to find out Members with multiple Claims who had any lapses in
the Fill Dates plus a lag of 15 Days.
For eg: Member PETER GAMBINI in the 2nd record has a
Fill Date of 09/24/05 with Days Supply as 30.
and his next FillDate is 12/25/05
So ( 12/25/05 - 09/24/05 ) - 30 > 15 (Lag Days) is TRUE so Member PETER
GAMBINI would show up in my Select and hence in the Report.
The above is not true for MARIA ROSA so she would not be selected in my Quer
y.
because
...
MARIA ROSA 10/03/05 15
MARIA ROSA 10/18/05 30
MARIA ROSA 11/18/05 30
...
Her First FillDate Days Supply is 15 so
(10/18/05 - 10/03/05 ) - 15 > 15 is False
Next iteration
Her First FillDate Days Supply is 30 so
(11/18/05 - 10/08/05 ) - 30 > 15 is False
So she would not show up in the result my Query.
The same logic applies to DAN JONES and since ( 04/30/05 - 04/01/05 ) - 5 >
15 , so therefore he will show up in the Report.
I have tried using Self joins and subquery but somehow it fails when the
Member has more than 2 records in the table.I want to use a cursor as the
last resort.
Hope this helps in understanding the problem..thanks in advance for your hel
p.
NHIt would help if you posted your DDL with INSERT statements of your data.
here's an untested solution:
select
m1.*
from
MyTable m1
join
MyTable m2 on m2.MemberName = m1.MemberName
where not exists
(
select
*
from
MyTable m3
where
m3.MemberName = m1.MemebrName
and m3.FillDate between m1.FillDate and m2.FillDate
)
and m2.FillDate > m1.FillDate + m1.DaysSupply
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"NeedHelp" <NeedHelp@.discussions.microsoft.com> wrote in message
news:0E1F4643-3618-4EFA-87AB-E2C8EE4E1E95@.microsoft.com...
We have a table with following information.
MemberName FillDate DaysSupply
PETER GAMBINI 09/22/05 30
PETER GAMBINI 09/24/05 30
PETER GAMBINI 12/25/05 30
MARIA ROSA 10/03/05 15
MARIA ROSA 10/18/05 30
MARIA ROSA 11/18/05 30
DAN JONES 04/01/05 5
DAN JONES 04/30/05 30
DAN JONES 04/30/05 10
I am trying to find out Members with multiple Claims who had any lapses in
the Fill Dates plus a lag of 15 Days.
For eg: Member PETER GAMBINI in the 2nd record has a
Fill Date of 09/24/05 with Days Supply as 30.
and his next FillDate is 12/25/05
So ( 12/25/05 - 09/24/05 ) - 30 > 15 (Lag Days) is TRUE so Member PETER
GAMBINI would show up in my Select and hence in the Report.
The above is not true for MARIA ROSA so she would not be selected in my
Query.
because
...
MARIA ROSA 10/03/05 15
MARIA ROSA 10/18/05 30
MARIA ROSA 11/18/05 30
...
Her First FillDate Days Supply is 15 so
(10/18/05 - 10/03/05 ) - 15 > 15 is False
Next iteration
Her First FillDate Days Supply is 30 so
(11/18/05 - 10/08/05 ) - 30 > 15 is False
So she would not show up in the result my Query.
The same logic applies to DAN JONES and since ( 04/30/05 - 04/01/05 ) - 5 >
15 , so therefore he will show up in the Report.
I have tried using Self joins and subquery but somehow it fails when the
Member has more than 2 records in the table.I want to use a cursor as the
last resort.
Hope this helps in understanding the problem..thanks in advance for your
help.
NH|||It would help if you posted your DDL with INSERT statements of your data.
here's an untested solution:
select
m1.*
from
MyTable m1
join
MyTable m2 on m2.MemberName = m1.MemberName
where not exists
(
select
*
from
MyTable m3
where
m3.MemberName = m1.MemberName
and m3.FillDate between m1.FillDate and m2.FillDate
)
and m2.FillDate > m1.FillDate + m1.DaysSupply
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"NeedHelp" <NeedHelp@.discussions.microsoft.com> wrote in message
news:0E1F4643-3618-4EFA-87AB-E2C8EE4E1E95@.microsoft.com...
We have a table with following information.
MemberName FillDate DaysSupply
PETER GAMBINI 09/22/05 30
PETER GAMBINI 09/24/05 30
PETER GAMBINI 12/25/05 30
MARIA ROSA 10/03/05 15
MARIA ROSA 10/18/05 30
MARIA ROSA 11/18/05 30
DAN JONES 04/01/05 5
DAN JONES 04/30/05 30
DAN JONES 04/30/05 10
I am trying to find out Members with multiple Claims who had any lapses in
the Fill Dates plus a lag of 15 Days.
For eg: Member PETER GAMBINI in the 2nd record has a
Fill Date of 09/24/05 with Days Supply as 30.
and his next FillDate is 12/25/05
So ( 12/25/05 - 09/24/05 ) - 30 > 15 (Lag Days) is TRUE so Member PETER
GAMBINI would show up in my Select and hence in the Report.
The above is not true for MARIA ROSA so she would not be selected in my
Query.
because
...
MARIA ROSA 10/03/05 15
MARIA ROSA 10/18/05 30
MARIA ROSA 11/18/05 30
...
Her First FillDate Days Supply is 15 so
(10/18/05 - 10/03/05 ) - 15 > 15 is False
Next iteration
Her First FillDate Days Supply is 30 so
(11/18/05 - 10/08/05 ) - 30 > 15 is False
So she would not show up in the result my Query.
The same logic applies to DAN JONES and since ( 04/30/05 - 04/01/05 ) - 5 >
15 , so therefore he will show up in the Report.
I have tried using Self joins and subquery but somehow it fails when the
Member has more than 2 records in the table.I want to use a cursor as the
last resort.
Hope this helps in understanding the problem..thanks in advance for your
help.
NH|||Do you keep asking the same question over and over, or is this a homework
assignment? I find it hard to believe the same person would still be stuck
on this since February.
http://groups.google.com/groups?sou...ply&sa=N&tab=wg
"NeedHelp" <NeedHelp@.discussions.microsoft.com> wrote in message
news:0E1F4643-3618-4EFA-87AB-E2C8EE4E1E95@.microsoft.com...
> We have a table with following information.
> MemberName FillDate DaysSupply
> PETER GAMBINI 09/22/05 30
> PETER GAMBINI 09/24/05 30
> PETER GAMBINI 12/25/05 30
> MARIA ROSA 10/03/05 15
> MARIA ROSA 10/18/05 30
> MARIA ROSA 11/18/05 30
> DAN JONES 04/01/05 5
> DAN JONES 04/30/05 30
> DAN JONES 04/30/05 10
> I am trying to find out Members with multiple Claims who had any lapses in
> the Fill Dates plus a lag of 15 Days.
> For eg: Member PETER GAMBINI in the 2nd record has a
> Fill Date of 09/24/05 with Days Supply as 30.
> and his next FillDate is 12/25/05
> So ( 12/25/05 - 09/24/05 ) - 30 > 15 (Lag Days) is TRUE so Member PETER
> GAMBINI would show up in my Select and hence in the Report.
> The above is not true for MARIA ROSA so she would not be selected in my
Query.
> because
> ...
> MARIA ROSA 10/03/05 15
> MARIA ROSA 10/18/05 30
> MARIA ROSA 11/18/05 30
> ...
> Her First FillDate Days Supply is 15 so
> (10/18/05 - 10/03/05 ) - 15 > 15 is False
> Next iteration
> Her First FillDate Days Supply is 30 so
> (11/18/05 - 10/08/05 ) - 30 > 15 is False
> So she would not show up in the result my Query.
> The same logic applies to DAN JONES and since ( 04/30/05 - 04/01/05 ) - 5
> 15 , so therefore he will show up in the Report.
> I have tried using Self joins and subquery but somehow it fails when the
> Member has more than 2 records in the table.I want to use a cursor as the
> last resort.
> Hope this helps in understanding the problem..thanks in advance for your
help.
> NH|||Thanks for the link.
I saw a similar problem and therefore copied the desc - but the solutions
proposed from the previous threads did not work.
Thanks again.
"Jim Underwood" wrote:

> Do you keep asking the same question over and over, or is this a homework
> assignment? I find it hard to believe the same person would still be stuc
k
> on this since February.
> http://groups.google.com/groups?sou...ply&sa=N&tab=wg
>
> "NeedHelp" <NeedHelp@.discussions.microsoft.com> wrote in message
> news:0E1F4643-3618-4EFA-87AB-E2C8EE4E1E95@.microsoft.com...
> Query.
> help.
>
>sql