SQL 2012 Query help to get max value

[ITALIAORIANA]

Hi,
I am having a problem trying to figure out how to write a query to get the results I need. I need to see both the IPCD and IPCD2 codes as there can be several different IPCD2 codes associated with one IPCD. There are also several generations which is where I having a problem. I don't want to see all the generations. I just need to see the max term date for each code set.

My end result is to find all the codes in the TEMP table that show a NULL deletion date but do not have generations for 2016 in the CEIP table so I can create them.

So from the example data below, my query should pull IPCD 11042 with IPCD2 0213T because the TEMP table has a deletion date as "NULL" but the CEIP table does not have 2016 generations. BUT I only want to see the generation in red in my CEIP table example. I want to add these to a temp table and create the 4 new generations for each quarter.

It should not pull code 0001M because it has the 2016 generations with a term date of 12/31/2099 for 10-1-2016 generation.
It should also not pull codes 11042 96361 because the 2016 generations are already created.


EXAMPLE FROM TEMP TABLE: All codes are showing "NULL" in the deletion date.

[pre]COLUMN1, COLUMN2, EFFECTIVE DATE, DELETION DATE, MODIFIER
11042, 0213T, 2010-07-01 00:00:00.000, NULL, 0
11042, 96361, 2016-04-01 00:00:00.000, NULL, 1
0001M, 36592, 2015-10-01 00:00:00.000, NULL, 0
[/pre]


EXAMPLE FROM CEIP TABLE: These codes exist in the CEIP table as well. CODE 11042 has two IPCD2 codes, one has 2016 generations and one does not. 0001M has 2016 generations.

[pre]PREFIX, IPCD, TYPE, IPCD2, EFFECTIVE DATE, TERM DATE
0001, 11042, S2, 0213T, 2012-01-01 00:00:00.000, 2012-09-30 00:00:00.000
0001, 11042, S2, 0213T, 2012-10-01 00:00:00.000, 2013-06-30 00:00:00.000
0001, 11042, S2, 0213T, 2013-07-01 00:00:00.000, 2013-09-30 00:00:00.000
0001, 11042, S2, 0213T, 2013-10-01 00:00:00.000, 2013-12-31 00:00:00.000
0001, 11042, S2, 0213T, 2014-01-01 00:00:00.000, 2014-06-30 00:00:00.000
0001, 11042, S2, 0213T, 2014-07-01 00:00:00.000, 2014-09-30 00:00:00.000
0001, 11042, S2, 0213T, 2014-10-01 00:00:00.000, 2014-12-31 00:00:00.000
0001, 11042, S2, 0213T, 2015-01-01 00:00:00.000, 2015-06-30 00:00:00.000
0001, 11042, S2, 0213T, 2015-07-01 00:00:00.000, 2015-09-30 00:00:00.000
0001, 11042, S2, 0213T, 2015-10-01 00:00:00.000, 2015-12-31 00:00:00.000
0001 11042, S2, 96361, 2016-04-01 00:00:00.000, 2016-06-30 00:00:00.000
0001 11042, S2, 96361, 2016-07-01 00:00:00.000, 2016-09-30 00:00:00.000
0001 11042, S2, 96361, 2016-10-01 00:00:00.000, 2099-12-31 00:00:00.000
0001, 0001M, S2, 36592, 2015-10-01 00:00:00.000, 2015-12-31 00:00:00.000
0001, 0001M, S2, 36592, 2016-01-01 00:00:00.000, 2016-03-31 00:00:00.000
0001, 0001M, S2, 36592, 2016-04-01 00:00:00.000, 2016-06-30 00:00:00.000
0001, 0001M, S2, 36592, 2016-07-01 00:00:00.000, 2016-09-30 00:00:00.000
0001, 0001M, S2, 36592, 2016-10-01 00:00:00.000, 2099-12-31 00:00:00.000
[/pre]

SELECT * FROM CMC_CEIP_REL_CRIT T1 
INNER JOIN TEMP_EDITS_FULL T2 
     ON T1.IPCD_ID = T2.COLUMN1 AND T1.IPCD2 = T2.COLUMN2 
WHERE T2.DELETION_DATE IS NULL 
     AND T1.IPCD_ID = T2.COLUMN1
     AND T1.IPCD2 = T2.COLUMN2 
     AND IPCD_ID NOT IN (SELECT T2.IPCD_ID FROM CMC_CEIP_REL_CRIT T2
	                 INNER JOIN CMC_CEIP_REL_CRIT T3
			 ON  T1.IPCD_ID = T2.IPCD_ID
                         AND T1.IPCD2 = T2.IPCD2 
			 WHERE T2.CEIP_TERM_DT ='2099-12-31'
			 AND T3.CEIP_EFF_DT NOT LIKE '%2016')

Thanks
Deana

 

[TamarGranor]

Not sure I fully understand, but from your description, maybe you need an EXISTS subquery?

Tamar

 

[ITALIAORIANA]

Hi TamarGranor,

It was difficult to try and clearly state what I needed. I finally figured this out! It seems a bit convoluted and I'm sure there would have been an easier way to complete what I needed to do but this worked.

I ended up using MAX(ceip_term_dt) in the select statement with the seq_no so I only got one row for the codes I needed. I put these in a temp table. I created a source table with 1 row for all the other fields I needed and then did a Cartesian query to combine them.

I am dealing with millions of rows so it has been super frustrating but it finally works.

Thanks
Deana

 

[TamarGranor]

I suspect there's a better way (it's rare for a Cartesian join to be the right approach), but if you're happy with what you have, then great.

Tamar