Selecting the max date from view

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Selecting the max date from view

Author

Topic

esilva002
Starting Member

10 Posts

Posted - 2010-09-16 : 13:21:47

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!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-16 : 13:29:58

you mean you need to include it only if it doesnt have any later records with other statuses than one you want?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

esilva002
Starting Member

10 Posts

Posted - 2010-09-16 : 15:10:16

Yes, you are correct. 00CV000773 really should not be displayed at all. I want the latest (max) TermDate along with the original credentials of TermDate IS NULL OR TermActionCodeID = 'U' OR TermActionCodeID = 'B' OR TermActionCodeID = 'X'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-17 : 12:03:19

[code]
SELECT
ID, CaseNumber1, DateFiled, OpenActionCode, JudgeNumber, TermDate, TermActionCodeID, ReOpenedCase, VisitJudge, CaseTypeNumber, SubmittedDate, RulingDate,
PreviousFileDate, Note, CDatefiled, CTermdate, CSubmittedDate, CRulingDate
FROM
tblSupremeCourt sc
OUTER APPLY (SELECT COUNT(*) AS Cnt
FROM tblSupremeCourt
WHERE CaseNumber1 = sc.CaseNumber1
AND TermDate > sc.TermDate
AND TermActionCodeID NOT IN ('U','B','X')
)sc1
WHERE
TermDate IS NULL OR TermActionCodeID = 'U' OR TermActionCodeID = 'B' OR TermActionCodeID = 'X'
AND ISNULL(sc1.Cnt,0) = 0
ORDER BY
CaseNumber1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources