Selecting the max date from view

Question

Post reply

Selecting the max date from view

esilva002

Valued Member

Points: 63
More actions
September 17, 2010 at 7:19 am

#227013

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!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 1

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

esilva002 Valued Member Points: 63 More actions · Answer 2

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?

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 3

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

Nevyn SSChampion Points: 14210 More actions · Answer 4

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.

esilva002 Valued Member Points: 63 More actions · Answer 5

esilva002

Valued Member

Points: 63

September 17, 2010 at 10:08 am

#1223144

I have an attached spreadsheet of the TOP 100 rows

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 6

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

esilva002 Valued Member Points: 63 More actions · Answer 7

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.

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 8

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

gofrancesc Hall of Fame Points: 3269 More actions · Answer 9

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