SQLTeam.com | Weblogs | Forums

Get most recent date out of two columns


#1

I need to get most recent date out of these two columns..

first look for most recent date in Moddate, if moddate is null ..go to adddate and get most recent date from adddate.


#2

select coalesce(moddate, adddate) mostrecentdate

Another approach to take would be to populate moddate at initial insertion time; just like adddate. Moddate would always have a value and would always be the most recent.


#3

what if moddate is null...then it has to go adddate and then select max(adddate)


#4

if you see in above image..

id: 000000015 has three rows...it should display only one result with one most recent date. for a particular id.
and this id has moddate is null..it should look for most recent date ..and display one most recent date but its not ..its showing all dates...


#5

select master_customer_id, MAX(coalesce(moddate, adddate)) mostrecentdate from MyTable group by master_customer_idBut this only underscores why populating the modate is a better approach. When you insert a new record (or records) you are modifying the data from nothing to something. Set moddate to the same initial value as adddate and it will always be available as the latest date for that record. A simple DEFAULT constraint would accomplish this.


#6

I am quite new. can you tell me about default constraint


#7

You can find the details in Books Online, the MS help doc that comes with SQL Server but the idea is simple. A default constraint on a column just defines a default value to use for that column if no value is supplied at insert time. Look up CREATE TABLE or ALTER TABLE in Books Online for the syntax.


#8

ok. thanks. one more query

i am trying to take id's with most recent date and priority seq != 0 at any rows of address_type_code in ('work','home') for a partuclar id.


#9

Since you used an image I can't cut and paste the code to modify it so I won't.In order to resolve the error, you need to "select" only one column name, in this case MASTER_CUSTOMER_ID. The other four columns in the IN subquery should be removed.


#10

ok.

SELECT *
INTO #NON_ZERO_PRIORITY_ROWS
FROM #USR_CUS_ADDRESS_DETAILS_TEMP CA1
WHERE PRIORITY_SEQ<>0
AND
NOT EXISTS(SELECT MASTER_CUSTOMER_ID FROM #USR_CUS_ADDRESS_DETAILS_TEMP CA2
WHERE CA1.MASTER_CUSTOMER_ID=CA2.MASTER_CUSTOMER_ID
AND CA1.ADDRESS_TYPE_CODE=CA2.ADDRESS_TYPE_CODE AND PRIORITY_SEQ=0)

DECLARE @LV_HOME_MODDATE DATETIME,
@LV_WORK_MODDATE DATETIME,
@LV_CAMPUS_MODDATE DATETIME,
@LV_HOME_SEQ INT,
@LV_WORK_SEQ INT,
@LV_CAMPUS_SEQ INT

IF EXISTS(SELECT 1 MODDATE FROM #NON_ZERO_PRIORITY_ROWS WHERE MODDATE IS NOT NULL)
BEGIN

     SELECT @LV_HOME_MODDATE=MAX(MODDATE)
     FROM #NON_ZERO_PRIORITY_ROWS
     WHERE  MODDATE IS NOT NULL AND ADDRESS_TYPE_CODE='HOME'
     
   
     SELECT @LV_WORK_MODDATE=MAX(MODDATE)
     FROM #NON_ZERO_PRIORITY_ROWS
     WHERE  MODDATE IS NOT NULL AND ADDRESS_TYPE_CODE='WORK'
     
     
   
     SELECT @LV_CAMPUS_MODDATE=MAX(MODDATE)
     FROM #NON_ZERO_PRIORITY_ROWS
     WHERE  MODDATE IS NOT NULL AND ADDRESS_TYPE_CODE='CAMPUS' 
	
	 SELECT  MASTER_CUSTOMER_ID,
		    CUS_ADDRESS_ID,
		    ADDDATE,
		    MODDATE,
		    ADDRESS_TYPE_CODE,
		    ADDRESS_STATUS_CODE='GOOD',
		    PRIORITY_SEQ
	 FROM #USR_CUS_ADDRESS_DETAILS_TEMP
	 WHERE MODDATE=@LV_HOME_MODDATE 
	 UNION 
	 SELECT  MASTER_CUSTOMER_ID,
		    CUS_ADDRESS_ID,
		    ADDDATE,
		    MODDATE,
		    ADDRESS_TYPE_CODE,
		    ADDRESS_STATUS_CODE='GOOD',
		    PRIORITY_SEQ
	 FROM #USR_CUS_ADDRESS_DETAILS_TEMP
	 WHERE MODDATE=@LV_WORK_MODDATE 
	 UNION 
	 SELECT  MASTER_CUSTOMER_ID,
		    CUS_ADDRESS_ID,
		    ADDDATE,
		    MODDATE,
		    ADDRESS_TYPE_CODE,
		    ADDRESS_STATUS_CODE='GOOD',
		    PRIORITY_SEQ
	 FROM #USR_CUS_ADDRESS_DETAILS_TEMP
	 WHERE MODDATE=@LV_CAMPUS_MODDATE --AND MASTER_CUSTOMER_ID=000024174
	 
	 
	 
   END
  ELSE IF EXISTS(SELECT 1 ADDDATE FROM  #USR_CUS_ADDRESS_DETAILS_TEMP WHERE ADDDATE IS NOT NULL)
  BEGIN

     
   
     SELECT /*@LV_HOME_MODDATE=*/(MODDATE)
     FROM #NON_ZERO_PRIORITY_ROWS
     WHERE  ADDDATE IS NOT NULL AND ADDRESS_TYPE_CODE='HOME'
     
   
     SELECT /*@LV_WORK_MODDATE=*/(MODDATE)
     FROM #NON_ZERO_PRIORITY_ROWS
     WHERE  ADDDATE IS NOT NULL AND ADDRESS_TYPE_CODE='WORK'
     
     
   
     SELECT /*@LV_CAMPUS_MODDATE=*/(MODDATE)
     FROM #NON_ZERO_PRIORITY_ROWS
     WHERE  ADDDATE IS NOT NULL AND ADDRESS_TYPE_CODE='CAMPUS' 
	
	 SELECT  MASTER_CUSTOMER_ID,
		    CUS_ADDRESS_ID,
		    ADDDATE,
		    MODDATE,
		    ADDRESS_TYPE_CODE,
		     ADDRESS_STATUS_CODE='GOOD',
		    PRIORITY_SEQ
	 FROM #USR_CUS_ADDRESS_DETAILS_TEMP
	 WHERE ADDDATE=@LV_HOME_MODDATE  
	 UNION 
	 SELECT  MASTER_CUSTOMER_ID,
		    CUS_ADDRESS_ID,
		    ADDDATE,
		    MODDATE,
		    ADDRESS_TYPE_CODE,
		    ADDRESS_STATUS_CODE='GOOD',
		    PRIORITY_SEQ
	 FROM #USR_CUS_ADDRESS_DETAILS_TEMP
	 WHERE ADDDATE=@LV_WORK_MODDATE 
	 UNION 
	 SELECT  MASTER_CUSTOMER_ID,
		    CUS_ADDRESS_ID,
		    ADDDATE,
		    MODDATE,
		    ADDRESS_TYPE_CODE,
		     ADDRESS_STATUS_CODE='GOOD',
		    PRIORITY_SEQ
	 FROM #USR_CUS_ADDRESS_DETAILS_TEMP
	 WHERE ADDDATE=@LV_CAMPUS_MODDATE 
  END

ELSE
BEGIN
SELECT @LV_HOME_SEQ=MAX(PRIORITY_SEQ)
FROM #NON_ZERO_PRIORITY_ROWS
WHERE ADDRESS_TYPE_CODE='HOME'

     SELECT @LV_WORK_SEQ=MAX(PRIORITY_SEQ)
     FROM #NON_ZERO_PRIORITY_ROWS
     WHERE  ADDRESS_TYPE_CODE='WORK'
     
     
   
     SELECT @LV_CAMPUS_SEQ=MAX(PRIORITY_SEQ)
     FROM #NON_ZERO_PRIORITY_ROWS
     WHERE  ADDRESS_TYPE_CODE='CAMPUS' 
	
	 SELECT  MASTER_CUSTOMER_ID,
		    CUS_ADDRESS_ID,
		    ADDDATE,
		    MODDATE,
		    ADDRESS_TYPE_CODE,
		     ADDRESS_STATUS_CODE='GOOD',
		    PRIORITY_SEQ
	 FROM #USR_CUS_ADDRESS_DETAILS_TEMP
	 WHERE PRIORITY_SEQ=@LV_HOME_SEQ 
	 UNION 
	 SELECT  MASTER_CUSTOMER_ID,
		    CUS_ADDRESS_ID,
		    ADDDATE,
		    MODDATE,
		    ADDRESS_TYPE_CODE,
		     ADDRESS_STATUS_CODE='GOOD',
		    PRIORITY_SEQ
	 FROM #USR_CUS_ADDRESS_DETAILS_TEMP
	 WHERE PRIORITY_SEQ=@LV_WORK_SEQ 
	 UNION 
	 SELECT  MASTER_CUSTOMER_ID,
		    CUS_ADDRESS_ID,
		    ADDDATE,
		    MODDATE,
		    ADDRESS_TYPE_CODE,
		    ADDRESS_STATUS_CODE='GOOD',
		    PRIORITY_SEQ
	 FROM #USR_CUS_ADDRESS_DETAILS_TEMP
	 WHERE PRIORITY_SEQ = @LV_CAMPUS_SEQ 
  END

#11

I the above i am trying display home, work, campus addresses where users not having priority_seq zero at all ..and . address_status_code = 'good'

But this query is taking max moddate from all users ..but i need max(moddate) for single master_customer_id. and master_customer_id is same for work, home , and campus..the only thing is unique is cus_address_id for each change in address or add new address...

example:

master_customer_id moddate

0000015 2015-10-02
0000015 2015-11-02
0000015 2016-10-02

it should give max moddate for single id that is 2016-10-02 not for all max modadate .


#12

Originally, the issue was how to pick the max from two columns within one record. Every time you respond the requirements change. Now, I don't even know what you are trying to do. Most people here are more than willing to help but we can't do the whole thing for you.
Having said that, if you want the MAX within a data subset, you need to "GROUP BY" the columns that define the data subset.


#13

Can find a code sample instantly, sorry, but one trick I have seen is to use CTE or JOIN a Sub Select, that provides a UNION of the two (or more, of course) columns so that the outer query can do a simple MAX on results from the sub-select


#14

Yea. you are right. I am in learning stage. so spend a lot of time. Didnt get .But I will do it hopefully. Thanks a lot . I really appreciate