Max Date Horrors

Question

Max Date Horrors

richeaton

Newbie

Points: 8
More actions
April 12, 2010 at 2:16 pm

#223153

I have reviewed the forum and found one example on fixing the group by statement so that you dont return more than 1 row with MAX(Date). however I still am stuck.
Here is what I have:
SELECT A1.PASTDEALDATE, A2.Account, A1.AccountId, A1.PROCEEDS, A1.TOTALCOST, A1.MKTDISC, A1.UWFEE, A1.WARRANTCOST, A1.IMPACTCOST, A1.UWAGENT, A1.NOTES
FROM CC_CAPITALRAISES A1 Left Join Account A2 on(A1.AccountId = A2.AccountId)
Where Exists
(SELECT Max(A1.PASTDEALDATE), A1.AccountId
FROM CC_CAPITALRAISES A1
Group By A1.AccountId)
Group By
A2.Account, A1.PASTDEALDATE ,A1.AccountId, A1.PROCEEDS, A1.TOTALCOST, A1.MKTDISC, A1.UWFEE, A1.WARRANTCOST, A1.IMPACTCOST, A1.UWAGENT, A1.NOTES
==========================================================================================================
This is generating multiple rows..? I need only the row with the Latest PASTDEALDATE to show for each AccountId..?
Thanks

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Please take some time to read the first article I reference below in my signature block. If you follow the instructions in that article you will get much better answers to your questions. Two things, please use sample data that reflects the problem domain, not real data. And more importantly, and not covered in the article, provide expected results based on the sample data you provide.

Ben Teraberry SSChampion Points: 11845 More actions · Answer 2

This may or may not be what you're looking for. (As has been pointed out, you've given very little information.)

SELECT A1.PASTDEALDATE,

A2.Account,

A1.AccountId,

A1.PROCEEDS,

A1.TOTALCOST,

A1.MKTDISC,

A1.UWFEE,

A1.WARRANTCOST,

A1.IMPACTCOST,

A1.UWAGENT,

A1.NOTES

FROM CC_CAPITALRAISES A1

LEFT JOIN Account A2

ON(A1.AccountId = A2.AccountId)

WHERE A1.PASTDEALDATE =

(SELECT Max(sq.PASTDEALDATE)

FROM CC_CAPITALRAISES sq

WHERE sq.AccountID = A1.AccountID)

I would highly suggest doing some reading so you understand how the GROUP BY clause works.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

richeaton Newbie Points: 8 More actions · Answer 3

THanks all this works.. and the links are great .. ty so much