Selecting max(date) from view

[esilva002]

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!

 

[gmmastros]

Just to be clear....

Do you want to show only one row for each CaseNumber1?

The row that is shown should be the one with the most recent TermDate, right?

Is is possible to have the same CaseNumber1 with the same date?

What version of SQL Server are you using?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

select ID, CaseNumber1, DateFiled, OpenActionCode, JudgeNumber, TermDate, TermActionCodeID, ReOpenedCase, VisitJudge, CaseTypeNumber, SubmittedDate, RulingDate,
PreviousFileDate, Note, CDatefiled, CTermdate, CSubmittedDate, CRulingDate
from (
Try

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 Row
FROM
tblSupremeCourt
WHERE
TermDate IS NULL OR TermActionCodeID IN ( 'U','B' 'X')) Derived where Row = 1
ORDER BY
CaseNumber1

Read these two blog posts to get a better idea of this query and alternative solutions:


Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2


PluralSight Learning Library

 

[markros]

Oops, put code tag in the wrong place

select ID, CaseNumber1, DateFiled, OpenActionCode, JudgeNumber, TermDate, TermActionCodeID, ReOpenedCase, VisitJudge, CaseTypeNumber, SubmittedDate, RulingDate,
PreviousFileDate, Note, CDatefiled, CTermdate, CSubmittedDate, CRulingDate, Code
 from (
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 Row
FROM
tblSupremeCourt
WHERE
TermDate IS NULL OR TermActionCodeID IN ( 'U','B' 'X')) Derived where Row = 1
ORDER BY
CaseNumber1

PluralSight Learning Library

 

[esilva002]

Do you want to show only one row for each CaseNumber1?
--- I want to show the last CaseNumber based on the TermDate

The row that is shown should be the one with the most recent TermDate, right?
--Yes

Is is possible to have the same CaseNumber1 with the same date?
--No

What version of SQL Server are you using?
--2005

 

[gmmastros]

Try the code suggested by markros. At first glance, it looks like it should work very well for you.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[esilva002]

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 row gets a 1 or 2?

 

[markros]

You should not be getting this record with that code if your CaseNumber1 is the same. Take a close look at ROW_NUMBER() function in BOL to understand what does partition means.

PluralSight Learning Library