How to query the most recent record based on a date field in SQL Server 2012?

Hello Experts,

I need to query some data that is  contained in one table.  I have a field titled 'POLICY_ID'.  This field contains strings that are 6 or 8 characters long.  I need all of them to be 6 digits in length so I remove the last two characters for all strings that are 8 characters long.   Once removed I need the query to return the distinct POLICY_ID with the most recent EFFECTIVE_DATE.  Two other fields included in the report are PREM_FIN_NAME_1 AND CLASS_OF_BUSINESS.  These two fields are the same for all POLICY_ID records, regardless of EFFECTIVE_DATE.

Here's what I've tried so far, but I continue to get duplicate POLICY_ID records (for example POLICY_ID 246246 shows for each EFFECTIVE_DATE (3/1/2014 and 4/1/2014), instead of returning just the POLICY_ID for the most recent EFFECTIVE_DATE (4/1/2014):

Attempt #1:
SELECT DISTINCT CASE
	WHEN LEN(POLICY_ID) > 6
		THEN LEFT(POLICY_ID, LEN(POLICY_ID)-2)
		ELSE POLICY_ID
	END AS POL_NUM, 
PREM_FIN_NAME_1,
CLASS_OF_BUSINESS,
EFFECTIVE_DATE
FROM VW_PR_POLICY_DESCRIPTION VPPD
WHERE EFFECTIVE_DATE =
	(SELECT MAX(EFFECTIVE_DATE) 
	FROM VW_PR_POLICY_DESCRIPTION 
	WHERE POLICY_ID=VPPD.POLICY_ID) AND EFFECTIVE_DATE >= '2014-04-01'
ORDER BY POL_NUM

Open in new window


Attempt 2 (Same results as above):
SELECT DISTINCT CASE
	WHEN LEN(POLICY_ID) > 6
		THEN LEFT(POLICY_ID, LEN(POLICY_ID)-2)
		ELSE POLICY_ID
	END AS POL_NUM,
PREM_FIN_NAME_1,
CLASS_OF_BUSINESS,
EFFECTIVE_DATE
FROM (SELECT POLICY_ID, PREM_FIN_NAME_1, CLASS_OF_BUSINESS, EFFECTIVE_DATE,
RANK() OVER (PARTITION BY POLICY_ID ORDER BY EFFECTIVE_DATE DESC) AS RK
FROM VW_PR_POLICY_DESCRIPTION VPPD
WHERE EFFECTIVE_DATE >= '2014-04-01') T
WHERE RK=1
ORDER BY POL_NUM

Open in new window


Any help would be greatly appreciated.  

Thanks,
J
ferguson_jeraldAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this one:
SELECT LEFT(POLICY_ID, 6) AS POL_NUM, 
       PREM_FIN_NAME_1,
       CLASS_OF_BUSINESS,
       MAX(EFFECTIVE_DATE) AS MAX_DATE
FROM VW_PR_POLICY_DESCRIPTION VPPD
GROUP BY LEFT(POLICY_ID, 6), PREM_FIN_NAME_1, CLASS_OF_BUSINESS
ORDER BY 1

Open in new window

0
Scott PletcherSenior DBACommented:
Your second query is extremely close.  I think it was just the PARTITION BY that messed up your results:

SELECT
    POLICY_NUM,
    PREM_FIN_NAME_1,
    CLASS_OF_BUSINESS,
    EFFECTIVE_DATE
FROM (
    SELECT
        LEFT(VPPD.POLICY_ID, 6) AS POLICY_NUM,
        VPPD.PREM_FIN_NAME_1,
        VPPD.CLASS_OF_BUSINESS,
        VPPD.EFFECTIVE_DATE,
        ROW_NUMBER() OVER(PARTITION BY LEFT(VPPD.POLICY_ID, 6) ORDER BY VPPD.EFFECTIVE_DATE DESC) AS row_num
    FROM VW_PR_POLICY_DESCRIPTION VPPD
    WHERE
        VPPD.EFFECTIVE_DATE >= '20140401'
) AS derived
WHERE
    row_num = 1
ORDER BY
    POLICY_NUM
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dsackerContract ERP Admin/ConsultantCommented:
I think you'll want something like this:
SELECT  v2.POLICY_ID,
        v2.PREM_FIN_NAME_1,
        v2.CLASS_OF_BUSINESS,
        v2.EFFECTIVE_DATE
FROM   (SELECT LEFT(POLICY_ID, 6)  AS POLICY6,
               MAX(EFFECTIVE_DATE) AS LATEST_DATE
        FROM   VW_PR_POLICY_DESCRIPTION
        WHERE  EFFECTIVE_DATE > '2014-04-01') v0
CROSS APPLY (SELECT TOP 1 * FROM VW_PR_POLICY_DESCRIPTION v1
             WHERE  LEFT(v1.POLICY_ID, 6) = POLICY6
             AND    EFFECIVE_DATE = LATEST_DATE
             ORDER BY POLICY_ID DESC) v2

Open in new window

The problem could be that there are more than one policy_id that starts with the same first six characters, and this considers that possibility for even the same latest effective date. The CROSS APPLY will only select one of them. It assumes that the larger policy number for the same effective date would be the latest one.

So this covers you both ways.
0
ferguson_jeraldAuthor Commented:
Scott - it works!  Thanks for the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.