complexity of nested self left joins?

Hi,

I'm curious about the computational complexity of a query I have. The
query contains multiple nested self left joins, starting with a simple
select, then doing a self left join with the results, then doing a self
left join with those results, etc. What puzzles me is that the time
required for the query seems to grow exponentially as I add additional
left joins, which I didn't expect. I expected the inner select to
return about 25 rows (it does), then I expected the self join to result
in about 25 rows (it does), etc. Each join just adds another column; it
doesn't add more rows. So the left part of the join is staying the same
size, and so is the right part of the join, since I'm always joining
with the same table.

So I would think the time for this query should be (time to join 25
rows against the source table) * (num joins), but it seems to be
something like (num rows) ^ (num joins). Any ideas? I'm just trying to
understand the system a little better. (But if you have any ideas about
improving the query, I'm always open to those, too.)

The execution plan is what you'd expect: an index seek loop-joined with
another index seek, the results of which are merge-joined with another
index seek, the results of which are merge-joined with another index
seek, ad nauseum, until a final "compute scalar cost (39%)" and "select
(0%)"

For the brave and curious, I've pasted the query below.

Thanks

select right(x.cp_yyyymm, 2)+'-'+left(x.cp_yyyymm, 4) as [Month],
table0.cp_num_loans/1 as [AFCM9704], table1.cp_num_loans/1 as
[AFC9104], table2.cp_num_loans/1 as [BFAT01C], table3.cp_num_loans/1 as
[BFAT02B], table4.cp_num_loans/1 as [BFAT03D], table5.cp_num_loans/1 as
[BFAT03E], table6.cp_num_loans/1 as [BFAT03F], table7.cp_num_loans/1 as
[BFAT04A], table8.cp_num_loans/1 as [BFAT04C], table9.cp_num_loans/1 as
[BFAT04D], table10.cp_num_loans/1 as [BFAT99C] from (((((((((((select
distinct cp_yyyymm from cp_deal_history where cp_deal_id in
('AFCM9704', 'AFC9104', 'BFAT01C', 'BFAT02B', 'BFAT03D', 'BFAT03E',
'BFAT03F', 'BFAT04A', 'BFAT04C', 'BFAT04D', 'BFAT99C') and cp_yyyymm
between 200304 and 200504) as x left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='AFCM9704') as
table0 on x.cp_yyyymm=table0.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='AFC9104') as table1
on x.cp_yyyymm=table1.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT01C') as table2
on x.cp_yyyymm=table2.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT02B') as table3
on x.cp_yyyymm=table3.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT03D') as table4
on x.cp_yyyymm=table4.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT03E') as table5
on x.cp_yyyymm=table5.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT03F') as table6
on x.cp_yyyymm=table6.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT04A') as table7
on x.cp_yyyymm=table7.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT04C') as table8
on x.cp_yyyymm=table8.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT04D') as table9
on x.cp_yyyymm=table9.cp_yyyymm) left join (select cp_yyyymm,
cp_num_loans from cp_deal_history where cp_deal_id='BFAT99C') as
table10 on x.cp_yyyymm=table10.cp_yyyymm order by x.cp_yyyymmFor those of you who's cranial parser requires liberal use of indents
and line breaks, I spent about an hour cleaning this up.

BTW, did you write this query to experiment with running times or are
you actually trying to accomplish something useful?

select right(x.cp_yyyymm, 2)+'-'+left(x.cp_yyyymm, 4) as [Month],
table0.cp_num_loans/1 as [AFCM9704],
table1.cp_num_loans/1 as [AFC9104],
table2.cp_num_loans/1 as [BFAT01C],
table3.cp_num_loans/1 as [BFAT02B],
table4.cp_num_loans/1 as [BFAT03D],
table5.cp_num_loans/1 as [BFAT03E],
table6.cp_num_loans/1 as [BFAT03F],
table7.cp_num_loans/1 as [BFAT04A],
table8.cp_num_loans/1 as [BFAT04C],
table9.cp_num_loans/1 as [BFAT04D],
table10.cp_num_loans/1 as [BFAT99C]
from (select distinct cp_yyyymm
from cp_deal_history
where cp_deal_id in
('AFCM9704', 'AFC9104', 'BFAT01C',
'BFAT02B', 'BFAT03D', 'BFAT03E',
'BFAT03F', 'BFAT04A', 'BFAT04C',
'BFAT04D', 'BFAT99C')
and cp_yyyymm between 200304 and 200504) as x

left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='AFCM9704') as table0
on x.cp_yyyymm=table0.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='AFC9104') as table1
on x.cp_yyyymm=table1.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT01C') as table2
on x.cp_yyyymm=table2.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT02B') as table3
on x.cp_yyyymm=table3.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT03D') as table4
on x.cp_yyyymm=table4.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT03E') as table5
on x.cp_yyyymm=table5.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT03F') as table6
on x.cp_yyyymm=table6.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT04A') as table7
on x.cp_yyyymm=table7.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT04C') as table8
on x.cp_yyyymm=table8.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT04D') as table9
on x.cp_yyyymm=table9.cp_yyyymm)
left join (select cp_yyyymm, cp_num_loans
from cp_deal_history
where cp_deal_id='BFAT99C') as table10
on x.cp_yyyymm=table10.cp_yyyymm
order by x.cp_yyyymm|||Still not entirely sure what you're trying to accomplish, but -- if
you're doing what I think you are -- why not try something like this:

select right(x.cp_yyyymm, 2)+'-'+left(x.cp_yyyymm, 4) as [Month],
case when y.cp_deal_id = 'AFCM9704'
then y.cp_num_loans/1 else null end as [AFCM9704],
case when y.cp_deal_id = 'AFC9104'
then y.cp_num_loans/1 else null end as [AFC9104],
case when y.cp_deal_id = 'BFAT01C'
then y.cp_num_loans/1 else null end as [BFAT01C],
case when y.cp_deal_id = 'BFAT02B'
then y.cp_num_loans/1 else null end as [BFAT02B],
case when y.cp_deal_id = 'BFAT03D'
then y.cp_num_loans/1 else null end as [BFAT03D],
case when y.cp_deal_id = 'BFAT03E'
then y.cp_num_loans/1 else null end as [BFAT03E],
case when y.cp_deal_id = 'BFAT03F'
then y.cp_num_loans/1 else null end as [BFAT03F],
case when y.cp_deal_id = 'BFAT04A'
then y.cp_num_loans/1 else null end as [BFAT04A],
case when y.cp_deal_id = 'BFAT04C'
then y.cp_num_loans/1 else null end as [BFAT04C],
case when y.cp_deal_id = 'BFAT04D'
then y.cp_num_loans/1 else null end as [BFAT04D],
case when y.cp_deal_id = 'BFAT99C'
then y.cp_num_loans/1 else null end as [BFAT99C],
from (select distinct cp_yyyymm
from cp_deal_history
where cp_deal_id in
('AFCM9704', 'AFC9104', 'BFAT01C',
'BFAT02B', 'BFAT03D', 'BFAT03E',
'BFAT03F', 'BFAT04A', 'BFAT04C',
'BFAT04D', 'BFAT99C')
and cp_yyyymm between 200304 and 200504) as x
left outer join cp_deal_history y
on x.cp_yyyymm = y.cp_yyyymm
order by x.cp_yyyymm|||That is not great surprise. LEFT OUTER JOINs are not well optimized
and thigns get flaky about five self joins in most queries. The real
proglem is that your code is awful.

Why would anyone, except an 1950's COBOL programmer, store temporal
data in strings! Why did you name a column for its format and not its
contents? Time is always stored as durations in a temporal data type.

Why would you divide by integer 1 when the column looks like a count of
loans? Tell me that you did not store that data as strings too and
have to force type conversions!! Try this:

SELECT deal_date,
MAX(CASE WHEN deal_id = 'AFCM9704'
THEN loan_cnt ELSE NULL END) AS AFCM9704,
..
MAX(CASE WHEN deal_id = 'BFAT99C'
THEN loans_cnt ELSE NULL END) AS BFAT99C
FROM DealHistory
WHERE deal_date BETWEEN '2003-04-01' AND '2005-04-30'
GROUP BY deal_date;|||Sweet Jesus, ZeldorBlat, if I'd thought anyone was actually going to
look at that I'd have pretty-printed it myself. So I apologize, and
thank you for your effort. The query constructs the data for a chart,
with cp_yyyymm being the independent variable, and each row needing the
value of cp_num_loans for each month and each particular deal id. So
the result set needs exactly one row per month. Your suggested query is
definitely moving in the right direction, but it just pulls all the
data - it doesn't "filter" it and arrange it as required. After playing
around a little bit, the query I eventually got working was basically
what CELKO suggested:

select right(z.cp_yyyymm, 2)+'-'+left(z.cp_yyyymm, 4) as [Month],
max(AFCM9704_unfiltered) as [AFCM9704],
max(AFC9104_unfiltered) as [AFC9104],
max(BFAT01C_unfiltered) as [BFAT01C],
max(BFAT02B_unfiltered) as [BFAT02B],
max(BFAT03D_unfiltered) as [BFAT03D],
max(BFAT03E_unfiltered) as [BFAT03E],
max(BFAT03F_unfiltered) as [BFAT03F],
max(BFAT04A_unfiltered) as [BFAT04A],
max(BFAT04C_unfiltered) as [BFAT04C],
max(BFAT04D_unfiltered) as [BFAT04D],
max(BFAT99C_unfiltered) as [BFAT99C]
from
(select cp_yyyymm, cp_deal_id,
case when y.cp_deal_id = 'AFCM9704'
then y.cp_num_loans/1 else null end as [AFCM9704_unfiltered],
case when y.cp_deal_id = 'AFC9104'
then y.cp_num_loans/1 else null end as [AFC9104_unfiltered],
case when y.cp_deal_id = 'BFAT01C'
then y.cp_num_loans/1 else null end as [BFAT01C_unfiltered],
case when y.cp_deal_id = 'BFAT02B'
then y.cp_num_loans/1 else null end as [BFAT02B_unfiltered],
case when y.cp_deal_id = 'BFAT03D'
then y.cp_num_loans/1 else null end as [BFAT03D_unfiltered],
case when y.cp_deal_id = 'BFAT03E'
then y.cp_num_loans/1 else null end as [BFAT03E_unfiltered],
case when y.cp_deal_id = 'BFAT03F'
then y.cp_num_loans/1 else null end as [BFAT03F_unfiltered],
case when y.cp_deal_id = 'BFAT04A'
then y.cp_num_loans/1 else null end as [BFAT04A_unfiltered],
case when y.cp_deal_id = 'BFAT04C'
then y.cp_num_loans/1 else null end as [BFAT04C_unfiltered],
case when y.cp_deal_id = 'BFAT04D'
then y.cp_num_loans/1 else null end as [BFAT04D_unfiltered],
case when y.cp_deal_id = 'BFAT99C'
then y.cp_num_loans/1 else null end as [BFAT99C_unfiltered]
from cp_deal_history as y
where cp_deal_id in ('AFCM9704', 'AFC9104', 'BFAT01C', 'BFAT02B',
'BFAT03D', 'BFAT03E',
'BFAT03F', 'BFAT04A', 'BFAT04C', 'BFAT04D', 'BFAT99C')
and cp_yyyymm between 200304 and 200504) as z
group by z.cp_yyyymm
order by z.cp_yyyymm

Note the lack of a single join. Sigh - I don't know what I was
thinking. Thanks very much to both of you for your help. (I'm still
curious as to why the joins weren't working the way I expected, though.)

Complex Self Join

This is a very complex query and i have tried everything with no sucess.

I'm having 3 Tables,

Orders which is having fields like CustomerID, ManufactureID, MerchID :- These all ID Fields (around 6) are foreign key of Contacts and Address Tables.

Address Table is having AddrID(Primary Key), ShortName

Contacts is having ContID (Primary Key), AddrID (Foreign Key), PersonName

I want to retreieve info in a single query which can return a single row with following columns :-

OrderNo, CustomerName, ManufactureName, MerchName etc. (all 6 columns) by joining these 3 tables

can anybody help in this.

You will need to join each table with a unique alias.

For example:

SELECT OrderNo, CustomerName = cust.PersonName,
ManufactureName = manu.PersonName,......
FROM Orders or
JOIN Contacts cust ON cust.ContactID = or.CustomerID
JOIN Address custadd ON custadd.AddrID = cust.AddrID
JOIN Contacts manu ON manu.ContactID = or.ManufactureID
JOIN Address manuadd ON manuadd.AddrID = manu.AddrID

etc

I will warn you this will be relatively slow, if there are lots of records in Contacts.

Another way would be:

Select OrderNo,
CustomerName = (SELECT PersonName FROM Contacts cn WHERE cn.ContactID = or.CustomerID)
ManufactureName = (SELECT PersonName FROM Contacts cn WHERE cn.ContactID = or.ManufactureID)

FROM Orders or|||

Could you use one IDE (for example, Design Query in Editor in SQL Server 2005) to do this? You drag and drop your tables in the editor, and then you link them through key fields, and you pick the columns you are interested.

The query will be something look like this:

SELECT a.OrderNo, b.CustomerName, b.ManufactureName, c.PersonName as MerchName FROM Orders a INNER JOIN Contacts b ON a.CustomerID=b.ContID INNER JOIN Address c ON c.AddrID=a.AddrID

|||

Thanks for your response.

pls note i can also use seperate queries to extract each column, even that will do. Do you think that will be faster then joining all together..

|||It depends on the contacts table whether it will be faster or not. I have had experience in SQL 2000 where self joining 5 tables, worked fine, then the 6th make it take 4 times longer to return.

You will have to play with it and see what works for your situation.

Complex query problem

Hello!

I have a query that queries two tables (including a self join on one of them) and returns a result set that almost (but not quite) gives me what I want , and was wondering if someone could give me some pointers. Rather than show the whole query (complex), I'll show the result set and describe what i want:

Tab1.efID Tab1.VID Tab2.efID Tab2.VID
$00046342 7 $00046342 8
$00046342 7 $00046342 19
$00046342 18 $00046342 19

I want to amend the query so that it returns a count of the distinct rows of Tab1.efID,Tab1.VID - from the above result set, it should return just a count of the first and third rows, i.e 2

The statement SELECT DISTINCT Tab1.efID,Tab1.VID would return the two rows, but obviously SELECT COUNT(DISTINCT Tab1.efID,Tab1.VID) doesn't work.

SELECT COUNT(DISTINCT Tab1.efID + CAST(Tab1.VID AS VARCHAR(2))) does work, but i thought perhaps there may be a more elegant solution - anyone have any pointers?

Cheers
GregSee your other thread (http://www.dbforums.com/t1008216.html) asking this question.

-PatP

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:
>