INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL 2012 Query help to get max value

SQL 2012 Query help to get max value

(OP)
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.

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
 


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.

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
 


CODE

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

RE: SQL 2012 Query help to get max value

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

Tamar

RE: SQL 2012 Query help to get max value

(OP)
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

RE: SQL 2012 Query help to get max value

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close