Find max over past month, 6 months, 9 months

[MsChelle]

I am working on a query that would provide maximum values per record over the past month, 6 months, and 9 months.

There is a one-to-many relationship between the table containing the records and the one containing the values.

There are about 1200 records to get these values for.

I've tried this a couple different ways.

First, I tried a case statement:

select table1.recordID,
max(case when table2.date_val > dateadd(d,-30,getdate()) then table2.value end) as val30,
max(case when table2.date_val > dateadd(d,-90, getdate()) then table2.value end) as val90,
max(case when table2.date_val > dateadd(d,-180, getdate()) then table2.value end) as val180
from table1 inner join table2 on table1.recordID = table2.recordID

This takes 43 seconds to pull just the Top 5.

I've also tried joining seperate subqueries:
select table1.recordID, q30.val30, q90.val90, q180.val180
from table1 inner join
(select recordID, max(value) from table2 where date_val > dateadd(d,-30,getdate()
group by recordID) q30
on table1.recordID = q30.recordID
etc...

and that's shorter, but still takes way too long (almost 2 minutes for everything).

When the subqueries are run individually (not joined, but with a where clause like: table2.recordID IN(select distinct recordID from table1)
they all run in 15-20 seconds. Much better, but I have 3 seperate data sets.

Any ideas??

 

[gmmastros]

select table1.recordID,
max(case when table2.date_val > dateadd(d,-30,getdate()) then table2.value end) as val30,
max(case when table2.date_val > dateadd(d,-90, getdate()) then table2.value end) as val90,
max(case when table2.date_val > dateadd(d,-180, getdate()) then table2.value end) as val180
from table1 inner join table2 on table1.recordID = table2.recordID 
Group By table1.recordID
[!]Where Table2.date_val > DateAdd(d, -180, GetDate())[/!]

Since you only care about records less than 180 days old, adding the where clause will allow you to filter out the records earlier.

You could also try adding this condition to the join clause, it may perform better.

from table1 inner join table2 on table1.recordID = table2.recordID and table1.date_val > DateAdd(d, -180, GetDate())

Also... I would suggest you take a look at your indexes. I suspect there should be an index on table1.recordID, and another index on table2.RecordId and/or table2.date_val. You'll need to play around with the indexes a bit to optimize the performance.

Let us know how you make out.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom