March 28, 2012 at 2:33 pm
{Resolved}
Hi
i have the following query, which i thought would give me the maximum date for each record_id however, it does not.
Any assistance would be greatly appreciated:
select fk_fha_number,max(date_final_settlement) max_settlement_date
from mf_fha_claim
where date_final_settlement is not null
and fk_fha_number in (06435113,
07135575,
09235585,
09235599,
06235384,
10135218,
09235399,
08544032,
08435265,
08535312)
group by fk_fha_number,date_final_settlement
order by fk_fha_number
Results
fk_fha_number Max_date_settlement
062353841999-09-08 00:00:00.000
062353842004-01-30 00:00:00.000
064351131995-09-27 00:00:00.000
071355751997-05-27 00:00:00.000
071355752007-11-15 00:00:00.000
084352651999-08-03 00:00:00.000
084352652005-03-17 00:00:00.000
085353121990-06-08 00:00:00.000
085353122004-09-24 00:00:00.000
085440321999-06-24 00:00:00.000
085440322005-03-23 00:00:00.000
092353992012-02-01 00:00:00.000
092355852009-06-17 00:00:00.000
092355852009-09-22 00:00:00.000
092355992009-06-09 00:00:00.000
092355992009-09-22 00:00:00.000
101352182002-01-28 00:00:00.000
101352182005-09-30 00:00:00.000
March 28, 2012 at 2:50 pm
Change your query
select fk_fha_number,max(date_final_settlement) max_settlement_date
from mf_fha_claim
where date_final_settlement is not null
and fk_fha_number in (06435113,
07135575,
09235585,
09235599,
06235384,
10135218,
09235399,
08544032,
08435265,
08535312)
group by fk_fha_number--,date_final_settlement
order by fk_fha_number
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 28, 2012 at 5:01 pm
SQLRNNR (3/28/2012)
Change your query
you almost never want the aggregate your creating in your group by. it basically gets rid of any aggregation.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 28, 2012 at 11:43 pm
You also should not need:
date_final_settlement is not null and
Assuming of course that your database is set to sort NULLs to before any non-NULL entry.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 29, 2012 at 10:08 am
thank you
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply