Comparing within a Self Join

Greetings All,
Have a table with the following sample data
select * from t1
PatientName RxFillDate
JACK 01/01/2006
JACK 02/01/2006
JACK 03/01/2006
JILL 04/04/2006
JILL 05/25/2006
JILL 06/25/2006
Here is what my objective is..
I want to output the PatientNames who have a Difference of more than 30 days
in any of their RxFillDate.
So in the above Sample I would just get JILL as the Output , Since the
difference
between one pair of he RxFillDate ( 04/04/2006 and 05/25/2006 ) is more than
30 Days.
Hope I explained it clearly - Any help will be appreciated.
Thanks in advance,
AbTry:
select distinct
x.PatientName
from
t1 x
join
t2 y on y.PatientName = x.PatientName
and y.RxFillDate > x.RxFillDate +30
and not exists
(
select
*
from
t1 z
where
z.PatientName = x.PatientName
and z.RxFillDate between x.RxFillDate and y.RxFillDate
)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ab" <Ab@.discussions.microsoft.com> wrote in message
news:17A7AA22-364E-46CE-97E6-7FC128C888AE@.microsoft.com...
Greetings All,
Have a table with the following sample data
select * from t1
PatientName RxFillDate
JACK 01/01/2006
JACK 02/01/2006
JACK 03/01/2006
JILL 04/04/2006
JILL 05/25/2006
JILL 06/25/2006
Here is what my objective is..
I want to output the PatientNames who have a Difference of more than 30 days
in any of their RxFillDate.
So in the above Sample I would just get JILL as the Output , Since the
difference
between one pair of he RxFillDate ( 04/04/2006 and 05/25/2006 ) is more than
30 Days.
Hope I explained it clearly - Any help will be appreciated.
Thanks in advance,
Ab|||Tom, Thanks for your reply
Will this also work for in situation as below ( maybe i should have
mentioned it before)

> select * from t1
> PatientName RxFillDate
> JACK 01/01/2006
> JACK 02/01/2006
> JACK 03/01/2006
> JILL 04/04/2006
> JILL 05/25/2006
> JILL 06/25/2006
> JILL 06/28/2006
>ROCKY 04/01/2006
>MARK 05/03/2006
In the above - again Patient JILL would showup in the output..
"Tom Moreau" wrote:

> Try:
> select distinct
> x.PatientName
> from
> t1 x
> join
> t2 y on y.PatientName = x.PatientName
> and y.RxFillDate > x.RxFillDate +30
> and not exists
> (
> select
> *
> from
> t1 z
> where
> z.PatientName = x.PatientName
> and z.RxFillDate between x.RxFillDate and y.RxFillDate
> )
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Ab" <Ab@.discussions.microsoft.com> wrote in message
> news:17A7AA22-364E-46CE-97E6-7FC128C888AE@.microsoft.com...
> Greetings All,
> Have a table with the following sample data
> select * from t1
> PatientName RxFillDate
> JACK 01/01/2006
> JACK 02/01/2006
> JACK 03/01/2006
> JILL 04/04/2006
> JILL 05/25/2006
> JILL 06/25/2006
> Here is what my objective is..
> I want to output the PatientNames who have a Difference of more than 30 da
ys
> in any of their RxFillDate.
> So in the above Sample I would just get JILL as the Output , Since the
> difference
> between one pair of he RxFillDate ( 04/04/2006 and 05/25/2006 ) is more th
an
> 30 Days.
> Hope I explained it clearly - Any help will be appreciated.
> Thanks in advance,
> Ab
>|||She should show up, since there was at least one period of time where she
waited longer than 30 days to get her meds. What's the exact requirement?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ab" <Ab@.discussions.microsoft.com> wrote in message
news:4BAC8463-D694-43F3-83F7-AB0E538F3954@.microsoft.com...
Tom, Thanks for your reply
Will this also work for in situation as below ( maybe i should have
mentioned it before)

> select * from t1
> PatientName RxFillDate
> JACK 01/01/2006
> JACK 02/01/2006
> JACK 03/01/2006
> JILL 04/04/2006
> JILL 05/25/2006
> JILL 06/25/2006
> JILL 06/28/2006
>ROCKY 04/01/2006
>MARK 05/03/2006
In the above - again Patient JILL would showup in the output..
"Tom Moreau" wrote:

> Try:
> select distinct
> x.PatientName
> from
> t1 x
> join
> t2 y on y.PatientName = x.PatientName
> and y.RxFillDate > x.RxFillDate +30
> and not exists
> (
> select
> *
> from
> t1 z
> where
> z.PatientName = x.PatientName
> and z.RxFillDate between x.RxFillDate and y.RxFillDate
> )
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Ab" <Ab@.discussions.microsoft.com> wrote in message
> news:17A7AA22-364E-46CE-97E6-7FC128C888AE@.microsoft.com...
> Greetings All,
> Have a table with the following sample data
> select * from t1
> PatientName RxFillDate
> JACK 01/01/2006
> JACK 02/01/2006
> JACK 03/01/2006
> JILL 04/04/2006
> JILL 05/25/2006
> JILL 06/25/2006
> Here is what my objective is..
> I want to output the PatientNames who have a Difference of more than 30
> days
> in any of their RxFillDate.
> So in the above Sample I would just get JILL as the Output , Since the
> difference
> between one pair of he RxFillDate ( 04/04/2006 and 05/25/2006 ) is more
> than
> 30 Days.
> Hope I explained it clearly - Any help will be appreciated.
> Thanks in advance,
> Ab
>|||select distinct t1.PatientName
from t1
where dateadd(day, 30, RxFillDate) < (
select min(t2.RxFillDate) from t1 t2
where t1.PatientName = t2.PatientName
and t1.RxFillDate < t2.RxFillDate)|||Thanks for your postings...But somehow I could not get the correct results.
Also I have the requirement now in much more detail...
Thanks for your responses and help.
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
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.
Hope this helps in understanding the problem..thanks in advance.
"Alexander Kuznetsov" wrote:

> select distinct t1.PatientName
> from t1
> where dateadd(day, 30, RxFillDate) < (
> select min(t2.RxFillDate) from t1 t2
> where t1.PatientName = t2.PatientName
> and t1.RxFillDate < t2.RxFillDate)
>|||The example below should be fine, you just need to replace the literal 30
with DaysSupply.
select distinct t1.PatientName
from t1
where dateadd(day, t1.DaysSupply, t1.RxFillDate) < (
select min(t2.RxFillDate) from t1 t2
where t1.PatientName = t2.PatientName
and t1.RxFillDate < t2.RxFillDate)
"Kanti Gala" <KantiGala@.discussions.microsoft.com> wrote in message
news:9BFEAEFA-9830-4DC9-A327-02564E4D10FE@.microsoft.com...
> Thanks for your postings...But somehow I could not get the correct
results.
> Also I have the requirement now in much more detail...
> Thanks for your responses and help.
> 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
> 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.
> Hope this helps in understanding the problem..thanks in advance.
>
> "Alexander Kuznetsov" wrote:
>