March 25, 2010 at 12:14 am
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
March 25, 2010 at 4:10 pm
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!
March 25, 2010 at 8:37 pm
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!
March 25, 2010 at 8:53 pm
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! 🙂
March 25, 2010 at 10:47 pm
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
March 25, 2010 at 11:12 pm
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!
March 26, 2010 at 12:02 am
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/
March 26, 2010 at 1:05 am
Thank you, Arjun.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply