November 17, 2010 at 9:39 am
I have two tables in my view, tblAttorney and tblCaseParty. Each Attorney has a CaseNumber, CaseParty, and NameID. NameID = Name of the attorney, Caseparty = Name of person that NameID is representing, and the CaseNumber = Identification of the case at hand. My view Looks Similar to this:
CaseNumber```````````CaseParty`````````````````NameID``````````````````TitleID
00CAS00003```````MC COY, CRAIGARY`````NORMAN P SOLZE, ESQ.```````````PLAINTIFF
00CAS00003````````````OHIO````````````RONALD J MAYLE, ESQ.```````````DEFENDANT
00CAS00003````````````OHIO``````````````SIXTH DISTRICT```````````````DEFENDANT
I am pulling TitleID from tblCaseParty, the other 3 columns are coming tblAttorney. My select statement looks like this:
SELECT TOP (100) PERCENT dbo.tblAttorney.CaseNumber, dbo.tblAttorney.CaseParty, dbo.tblAttorney.NameID, dbo.tblCaseParty.TitleID
FROM dbo.tblAttorney INNER JOIN dbo.tblCaseParty ON dbo.tblAttorney.CaseNumber = dbo.tblCaseParty.CaseNumber AND dbo.tblAttorney.CaseParty = dbo.tblCaseParty.NameID AND dbo.tblAttorney.CaseParty#ID = dbo.tblCaseParty.Name#ID
ORDER BY dbo.tblAttorney.CaseNumber
I want to select the first CaseNumber, Plaintiff, Plaintiff Attorney, Defendant, Defendant Attorney all within one row. That would mean that SIXTH DISTRICT row would not be included and my final view would look like this:
CaseNumber``````Plaintiff````````````PlaintiffAttorney````````Defendant`````DefendantAttorney
00CAS00003```MC COY, CRAIGARY```NORMAN P SOLZE, ESQ.````OHIO```````RONALD J MAYLE, ESQ.
Now, I believe I can do this based off of another view that i created where i numbered the rows then took all the rows where rn = 1. 1 being the first row. and that looked just like this:
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
I basically used two select statements which worked fine but how would i do the same concept above to fix my current view issue? I think what is throwing me off is the fact I have 2 tables to work with.
November 29, 2010 at 1:36 pm
How you implemented it is how you do it.
🙂
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
November 30, 2010 at 12:59 pm
It would help if you would provide the table definitions and some sample data for each of the tables.
Todd Fifield
December 1, 2010 at 6:05 am
No worries, I figured it out by making a view of the data i needed then making another view from that view I created, I added some reference tables. It work out great, but instead of making one view to accomplish my goal, i make 3 views all together. Thanks anyways guys!! At least I know I have support at SQL ServerCentral.com!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply