November 24, 2011 at 4:50 am
Hello,
I have a document history table which records all of the documents created on all cases. I currently return the date of the most-recently created document and a case ref for each case.
SELECT MAX(CREATIONDATE) [CREATIONDATE], CaseRef FROM VEC_DOCHISTORY
GROUP BY CaseRef
ORDER by CaseRef
However I want to update my query to include a Document Reference field. When I do so, I get multiple rows returned for each case.
SELECT MAX(CREATIONDATE) [CREATIONDATE], CaseRef, DocRef FROM VEC_DOCHISTORY
GROUP BY CaseRef, DocRef
ORDER by CaseRef
How can I restructure my query to return the MAX(CREATIONDATE), CaseRef and DocumentRef of the most-recently created document for each case?
Thanks in advance.
November 24, 2011 at 4:56 am
James123 (11/24/2011)
Hello,I have a document history table which records all of the documents created on all cases. I currently return the date of the most-recently created document and a case ref for each case.
SELECT MAX(CREATIONDATE) [CREATIONDATE], CaseRef FROM VEC_DOCHISTORY
GROUP BY CaseRef
ORDER by CaseRef
However I want to update my query to include a Document Reference field. When I do so, I get multiple rows returned for each case.
SELECT MAX(CREATIONDATE) [CREATIONDATE], CaseRef, DocRef FROM VEC_DOCHISTORY
GROUP BY CaseRef, DocRef
ORDER by CaseRef
How can I restructure my query to return the MAX(CREATIONDATE), CaseRef and DocumentRef of the most-recently created document for each case?
Thanks in advance.
This is a guess because you haven't supplied sample data or DDL.
SELECT a.[CREATIONDATE], a.CaseRef, b.DocRef
FROM (SELECT MAX(CREATIONDATE) AS [CREATIONDATE], CaseRef
FROM VEC_DOCHISTORY
GROUP BY CaseRef) a
INNER JOIN VEC_DOCHISTORY b ON b.CREATIONDATE = a.CREATIONDATE AND b.CaseRef = a.CaseRef
ORDER BY a.CaseRef
November 24, 2011 at 5:27 am
Thanks for that. That's narrowed the returned dataset but we still have duplicates where rows with the same CREATIONDATE exist
CREATIONDATE | CaseRefID|DocRefID
2008-01-08 00:00:00.000|11 |1020
2008-01-08 00:00:00.000|11 |2517
2007-01-29 00:00:00.000|16 |5016
2007-01-29 00:00:00.000|16 |5017
2007-01-29 00:00:00.000|16 |2941
2007-01-30 00:00:00.000|17 |4112
November 24, 2011 at 5:46 am
Use ROW_NUMBER() OVER (PARTITION BY ORDER BY)
To filter & do tie breaker.
November 24, 2011 at 5:53 am
James123 (11/24/2011)
Thanks for that. That's narrowed the returned dataset but we still have duplicates where rows with the same CREATIONDATE existCREATIONDATE | CaseRefID|DocRefID
2008-01-08 00:00:00.000|11 |1020
2008-01-08 00:00:00.000|11 |2517
2007-01-29 00:00:00.000|16 |5016
2007-01-29 00:00:00.000|16 |5017
2007-01-29 00:00:00.000|16 |2941
2007-01-30 00:00:00.000|17 |4112
Each case you have posted there was created at the same time, so which is the latest?
November 24, 2011 at 6:55 am
Sorry, I should have said that I'm working in SQL 2000 so I can't use OVER()
You're right, I could check the ROW_NUMBER() but I only actually need to know the date of the most recent activity for each case. If there are multiple entries for a particular case (they will all have the same date) I only need to return one row.
November 24, 2011 at 6:59 am
James123 (11/24/2011)
Sorry, I should have said that I'm working in SQL 2000 so I can't use OVER()You're right, I could check the ROW_NUMBER() but I only actually need to know the date of the most recent activity for each case. If there are multiple entries for a particular case (they will all have the same date) I only need to return one row.
Yes, but which row do you want to return?
November 24, 2011 at 7:04 am
It doesn't matter which row is returned. If a case has two documents created on it in one day, I just need to know that a document was created on that day so I only need to see one row.
November 24, 2011 at 7:11 am
James123 (11/24/2011)
It doesn't matter which row is returned. If a case has two documents created on it in one day, I just need to know that a document was created on that day so I only need to see one row.
Doesn't sound like a good design to me, but this will do what you're after.
SELECT a.[CREATIONDATE], a.CaseRef, MAX(b.DocRef)
FROM (SELECT MAX(CREATIONDATE) AS [CREATIONDATE], CaseRef
FROM VEC_DOCHISTORY
GROUP BY CaseRef) a
INNER JOIN (SELECT CREATIONDATE, CaseRef, DocRef
FROM VEC_DOCHISTORY) b ON b.CREATIONDATE = a.CREATIONDATE AND b.CaseRef = a.CaseRef
GROUP BY a.[CREATIONDATE], a.CaseRef
ORDER BY a.CaseRef
November 24, 2011 at 7:16 am
Perfect. Thanks very much.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply