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?
 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.