Selecting the max date from view
-
I have a View with the following code:
SELECT
ID, CaseNumber1, DateFiled, OpenActionCode, JudgeNumber, TermDate, TermActionCodeID, ReOpenedCase, VisitJudge, CaseTypeNumber, SubmittedDate, RulingDate,
PreviousFileDate, Note, CDatefiled, CTermdate, CSubmittedDate, CRulingDate
FROM
tblSupremeCourt
WHERE
TermDate IS NULL OR TermActionCodeID = 'U' OR TermActionCodeID = 'B' OR TermActionCodeID = 'X'
ORDER BY
CaseNumber1
I have a CaseNumber that has two records in tblSupremeCourt
CaseNumber1~~~~~TermDate~~~~~TermActionCodeID
00CV000773~~~~~~2002-08-15~~~~~~X
00CV000773~~~~~~2003-01-23~~~~~~S
Currently it displays in the view because it has an X as the TermActionCodeID, but I don't want it to be displayed because it has a later TermDate that happens have a TermActionCodeID of S.
Could someone please Help, Thanks!
-
Try this
WITH CTE AS (
SELECT
ID, CaseNumber1, DateFiled, OpenActionCode, JudgeNumber, TermDate, TermActionCodeID, ReOpenedCase, VisitJudge, CaseTypeNumber, SubmittedDate, RulingDate,
PreviousFileDate, Note, CDatefiled, CTermdate, CSubmittedDate, CRulingDate,
ROW_NUMBER() OVER(PARTITION BY CaseNumber1 ORDER BY TermDate DESC) AS rn
FROM
tblSupremeCourt
WHERE
TermDate IS NULL OR TermActionCodeID = 'U' OR TermActionCodeID = 'B' OR TermActionCodeID = 'X'
)
SELECT
ID, CaseNumber1, DateFiled, OpenActionCode, JudgeNumber, TermDate, TermActionCodeID, ReOpenedCase, VisitJudge, CaseTypeNumber, SubmittedDate, RulingDate,
PreviousFileDate, Note, CDatefiled, CTermdate, CSubmittedDate, CRulingDate
FROM CTE
WHERE rn=1
ORDER BY
CaseNumber1
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
I'm still receiving the record 00CV000773 with the TermActionCodeID of X.
Should the line below be changed?
ROW_NUMBER() OVER(PARTITION BY CaseNumber1 ORDER BY TermDate DESC) AS rn
What exactly does the PARTITION BY statement actually do? I think I might need to partition it with one more record.
I know I have 31 records that returns a 2. What determines that rn gets a 1 or 2?
-
Can you post some sample data?
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
It would really help to have DDL so we could test.
Could you also clarify your requirement? Can the view have multiple records for a given case number?
For example, if a CaseNumber has 3 records that all have an X, should all 3 be returned or only the latest one?
If you only want one record per casenumber, I think all you need to do is move the where clause conditions in Mark's CTE to the where clause of the main query. That way the CTE always gets the most recent for each case, then the main query determines whether it should be included.
-
I have an attached spreadsheet of the TOP 100 rows
-
It's not really clear what your rules are here. Do you want the latest CaseNumber1 based on TermDate regardless of TermActionCodeID. If so, then this should work
WITH CTE AS (SELECT
ID, CaseNumber1, DateFiled, OpenActionCode, JudgeNumber, TermDate, TermActionCodeID, ReOpenedCase, VisitJudge, CaseTypeNumber, SubmittedDate, RulingDate,
PreviousFileDate, Note, CDatefiled, CTermdate, CSubmittedDate, CRulingDate,
ROW_NUMBER() OVER(PARTITION BY CaseNumber1 ORDER BY TermDate DESC) AS rn
FROM
tblSupremeCourt
)
SELECT
ID, CaseNumber1, DateFiled, OpenActionCode, JudgeNumber, TermDate, TermActionCodeID, ReOpenedCase, VisitJudge, CaseTypeNumber, SubmittedDate, RulingDate,
PreviousFileDate, Note, CDatefiled, CTermdate, CSubmittedDate, CRulingDate
FROM CTE
WHERE rn=1
ORDER BY
CaseNumber1;
You can find information ROW_NUMBER here
http://msdn.microsoft.com/en-us/library/ms186734.aspx
Also what should happen if TermDate is NULL?
Your sample data should be in an "easily digestable" format, you'll generally get more replies if you do. Example attached.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
I'm sorry. I am looking for all cases where TermDate is NULL or any cases where TermActionCodeID = 'U' or TermActionCodeID = 'B' or TermActionCodeID = 'X'. Of these cases I only want the latest date. So if there are two records as follows:
CaseNumber1~~~~~TermDate~~~~~TermActionCodeID
00CV000773~~~~~~2002-08-15~~~~~~X
00CV000773~~~~~~2003-01-23~~~~~~S
In this example I am pulling two records with the same case number. The first record does have an X as the TermActionCodeID, but the TermDate is smaller than the second record with the same CaseNumber1. Therefore, both of these records should not be displayed. Because the second record has the bigger TermDate and does not have a TermActionCodeID of U, B or X.
-
Still not sure about NULL TermDate, is it later or earlier than non-NULL?
See if this works
WITH CTE AS (SELECT
ID, CaseNumber1, DateFiled, OpenActionCode, JudgeNumber, TermDate, TermActionCodeID, ReOpenedCase, VisitJudge, CaseTypeNumber, SubmittedDate, RulingDate,
PreviousFileDate, Note, CDatefiled, CTermdate, CSubmittedDate, CRulingDate,
ROW_NUMBER() OVER(PARTITION BY CaseNumber1 ORDER BY COALESCE(TermDate,'29991231') DESC) AS rn
FROM tblSupremeCourt
)
SELECT
ID, CaseNumber1, DateFiled, OpenActionCode, JudgeNumber, TermDate, TermActionCodeID, ReOpenedCase, VisitJudge, CaseTypeNumber, SubmittedDate, RulingDate,
PreviousFileDate, Note, CDatefiled, CTermdate, CSubmittedDate, CRulingDate
FROM CTE
WHERE TermDate IS NULL OR TermActionCodeID IN ('U','B','X')
AND rn=1
ORDER BY CaseNumber1
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Hello esilva,
as I read your latest reply you are contradictory, your first paragraph says one thing and your second paragraph states the opposite. As I understand it, you need:
- First of all, for each CaseNumber you need to take the latest TermDate and ignore all other rows.
- From this selection, you need to filter all cases where TermDate is NULL or TermActionCodeID IN ('U', 'B', 'X').
It I understand it correctly, the query added by Mark is the one you need. If a NULL TermDate is later than non-NULL TermDate the query is OK, If a NULL TermDate is earlier than non-NULL TermDate you should change "ORDER BY COALESCE(TermDate,'29991231') DESC" by "ORDER BY TermDate DESC".
Regards,
Francesc
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply