Maximum Date that exists for two or three parties and also another table

Question

Post reply

Maximum Date that exists for two or three parties and also another table

Viewing 8 posts - 16 through 22 (of 22 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

Nugby (3/24/2010)
...I'd get nothing back despite there being a potential match for the 21st.

Ah, I see! Thanks for clarifying that. Just for completeness, all we need do is change HAVING to AND:

SELECT MAX(SQ.date)

FROM (

SELECT T1.date,

T1.Name,

rnk = RANK() OVER (PARTITION BY Date ORDER BY Name)

FROM #Table1 T1

WHERE Name IN (N'John', N'Mark', N'Mary')

GROUP BY

T1.date,

T1.Name

) SQ

WHERE SQ.rnk = 3

AND EXISTS (SELECT * FROM #Table2 T2 WHERE T2.date = SQ.date);

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Nugby SSCommitted Points: 1612 More actions · Answer 2

Wow, that works astoundingly well! First run against Production Data was 3 seconds and 0 seconds for runs after that. You're going to force me to rewrite a few existing Functions with performance like that 😀

Thanks Paul!

Quatrei.X SSCrazy Points: 2697 More actions · Answer 3

Hi there,

Just wanna try out my solution ^__^

SELECT @date=t1.[date]

FROM @Table1 t1

INNER JOIN @Table2 t2 ON (t1.[date]=t2.[date])

GROUP BY t1.[date]

HAVING COUNT(name)=(

SELECT COUNT(DISTINCT Name)

FROM @Table1 t1

INNER JOIN @Table2 t2 ON (t1.[date]=t2.[date]))

_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!

"Press any key...
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson

Nugby SSCommitted Points: 1612 More actions · Answer 4

Quatrei.X (3/25/2010)
Hi there,
Just wanna try out my solution ^__^
SELECT @date=t1.[date]
FROM @Table1 t1
INNER JOIN @Table2 t2 ON (t1.[date]=t2.[date])
GROUP BY t1.[date]
HAVING COUNT(name)=(
SELECT COUNT(DISTINCT Name)
FROM @Table1 t1
INNER JOIN @Table2 t2 ON (t1.[date]=t2.[date]))

Works perfectly for the sample data that I actually provided, but if @Table2 doen't have the date from @Table1, it would return a NULL values as per an earlier comment I made -

I found that while it would work perfectly for this data, if I changed the 22nd of the 3rd references in @Table2 (this is part of a function hence my constantly reverting to Table Variables) to the 21st and added a 21st reference for each of the Clients in @Table1 (e.g. so the maximum date for the three clients was the 22nd but there was no match in @Table 2 for that date) I'd get nothing back despite there being a potential match for the 21st. In hindsight, I probably didn't make that requirement clear enough though (that I'd need @Table2 to contain that value also rather than just joining what data is available).

Had thought this thread would be closed now but I'm thinking I should edit the original post in case anyone else has a crack at it. Thanks for looking at it! 🙂

Paul White SSC Guru Points: 150467 More actions · Answer 5

Nugby (3/25/2010)
Had thought this thread would be closed now but I'm thinking I should edit the original post in case anyone else has a crack at it. Thanks for looking at it! 🙂

I wouldn't worry about it - it happens all the time. Thanks for the feedback on my 'improved' method though, I appreciate the time you have taken to respond, even after the original problem was solved.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Nugby SSCommitted Points: 1612 More actions · Answer 6

No worries; so long as I get my email notification about new posts I'll return to the thread (though I regretted posting in the "Are the questions on here getting worse" thread and then going away on Holidays a while back!).

I know I learnt plenty of techniques from questions I've seen in the "Database Pros Who Need Your Help!" section starting out and I do hope that others will also benefit from this one. Besides that, I'll never post a question I haven't spent a couple of frustrating hours on first, so you know I'm going to appreciate and attempt to reciprocate the help that I get!

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 7

Paul White NZ (3/25/2010)
Nugby (3/24/2010)
...I'd get nothing back despite there being a potential match for the 21st.
Ah, I see! Thanks for clarifying that. Just for completeness, all we need do is change HAVING to AND:
SELECT MAX(SQ.date)
FROM (
SELECT T1.date,
T1.Name,
rnk = RANK() OVER (PARTITION BY Date ORDER BY Name)
FROM #Table1 T1
WHERE Name IN (N'John', N'Mark', N'Mary')
GROUP BY
T1.date,
T1.Name
) SQ
WHERE SQ.rnk = 3
AND EXISTS (SELECT * FROM #Table2 T2 WHERE T2.date = SQ.date);

That's very clear thinking Paul. 🙂 Glad to be here.

https://sqlroadie.com/

Paul White SSC Guru Points: 150467 More actions · Answer 8

Thank you, Arjun.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi