Most recent records - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Most recent records

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-03-11 : 13:45:55

Ok, I was able to find the most recent Effective date and it works well. The only problem I am having is with the CarrierName. See there are different Carriers with so then it makes us have Mult effective dates. I need to pull the most recent effective date with carrier. So, I though to max the pcsiNumber2, but that didn’t work. Do you have any other ideas? Let me know.

Select Distinct
--PCSNumber,
PCSINumber1,
max(PCSINumber2) as mostrecentPCS,
LastName,
FirstName,
State,
--InsuranceCarrier,
CarrierName,
MAX(CoverageEffectiveDate)as MostRecentEff,
MAX(TerminationDate) as MostRecentTerm,
AmtPerClaim,
Aggreg

FROM VW_Medical_Malpractice_Project

Where
State = 'PA'

Group By PCSINumber1, LastName,FirstName,State,CarrierName,AmtPerClaim,Aggreg

Order By PCSINumber1

Example

00004301 0003 MUSSER WILLIAM American Professional Agency 2002-06-01

00004301 0002 MUSSER WILLIAM Columbia Casualty 2000-02-20

00004301 0001 MUSSER WILLIAM MCIC Vermont Inc. 1998-07-01

00004301 0004 MUSSER WILLIAM ProMutual Insurance Co. 2005-05-16

00004301 0005 MUSSER WILLIAM Tri-Century Insurance Company 2007-01-01

So, what I need is the most recent effective date which would be 2007-01-01, which I have but what is not letting me do this is the names, the 5ith column. I was trying to use the 2nd column to use the max , but it is not working. What am I doing wrong. I need the whole last row, but that number 0005 can change because there may only be 2 dup records.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-03-12 : 01:28:21

you are using max in select list on several columns and grouping them wrong. try with subquery on join.

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-03-12 : 09:26:18

How do I do that? I am not sure what you mean?

quote:
Originally posted by slimt_slimt

you are using max in select list on several columns and grouping them wrong. try with subquery on join.

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-03-12 : 11:45:48

Ok, I am trying something different this time.

But now it is only giving me the max row in the query. What am I doing wrong?

Select Distinct
PCSNumber,
PCSINumber1,
PCSINumber2,
LastName,
FirstName,
State,
--InsuranceCarrier,
CarrierName,
CoverageEffectiveDate,
TerminationDate,
AmtPerClaim,
Aggreg

FROM VW_Medical_Malpractice_Project

WHERE PCSINumber2=
(SELECT MAX(PCSINumber2)
FROM VW_Medical_Malpractice_Project
WHERE PCSNumber = PCSINumber1)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 11:50:00

Something similar to this

SELECT		t.*
FROM		{YourViewHere} AS t
INNER JOIN	(
			SELECT		PcsNumber,
					MAX(TerminationDate) AS TerminationDate
			FROM		{YourViewHere}
			GROUP BY	PcsNumber
		) AS z ON z.PcsNumber = t.PcsNumber
WHERE		t.TerminationDate = z.TerminationDate

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-12 : 12:44:59

Select Distinct
v.PCSNumber,
v.PCSINumber1,
v.PCSINumber2,
v.LastName,
v.FirstName,
v.State,
--InsuranceCarrier,
v.CarrierName,
v.CoverageEffectiveDate,
v.TerminationDate,
v.AmtPerClaim,
v.Aggreg

FROM VW_Medical_Malpractice_Project v
INNER JOIN (SELECT PCSNumber1,MAX(TerminationDate) as MostRecentTerm
FROM VW_Medical_Malpractice_Project
GROUP BY PCSNumber1)t
ON t.PCSNumber1=v.PCSNumber1
AND t.MostRecentTerm=v.TerminationDate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources