asked on

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

Vitor Montalvão

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

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dsacker

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.

ferguson_jerald

ASKER

Scott - it works! Thanks for the help.