Finding Records that were totally termed out and now there is an active record

1

I have a client that is looking for a report that would flag When a new location has been added to a previously termed provider (example PCS 82798 and location 26 (both 26 and 27 will need to be deleted/termed)).

Here is a little print screen of my data. We would probably need to base it on the PCSP_id1 (That is the Unique Key). The location number is pcsp_id2. I don't think we would need to do anything with pcsp_id3.

2

Is this what you're after?

SELECT 
	pl1.*
FROM
	#ProviderLocation pl1
WHERE 
	EXISTS (
			SELECT 1 
			FROM 
				#ProviderLocation pl2 
			WHERE 
				pl1.pcsp_id1 = pl2.pcsp_id1 
				AND pl1.pcsp_eff > pl2.pcsp_eff
				AND pl2.pcsp_trm IS not NULL
			)
	AND pl1.pcsp_trm IS NULL;
3

The effective date is working perfectly with 05/01/2017, but in the pcsp_trm I need the most recent term date in there. which could 12/01/2013.

4

How about this one???

SELECT 
	pl1.pcsp_id1, 
	pl1.pcsp_id2, 
	pl1.pcsp_id3, 
	pl1.pcsp_eff, 
	pl1.pcsp_trm,
	plx.RecientTermDate
FROM
	#ProviderLocation pl1
	CROSS APPLY (
			SELECT
				RecientTermDate = MAX(pl2.pcsp_trm)
			FROM 
				#ProviderLocation pl2 
			WHERE 
				pl1.pcsp_id1 = pl2.pcsp_id1 
				AND pl1.pcsp_eff > pl2.pcsp_eff
				AND pl2.pcsp_trm IS not NULL
			) plx
WHERE 
	pl1.pcsp_trm IS NULL;