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.
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.
1 Like
what if moddate is null...then it has to go adddate and then select max(adddate)
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...
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.
1 Like
I am quite new. can you tell me about default constraint
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.
1 Like
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.
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.
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
ENDI 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 .
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.
2 Likes
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
1 Like
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
hi
i know this topic is from 2 years ago
it has a solution .. union the two columns adddate and moddate
then take max
I am practicing my sql
I have given a different solution
drop create sample data
use tempdb
go
drop table data
go
create table data
(
adddate date ,
moddate date
)
go
insert into data select '2018-12-08',null
insert into data select '2018-11-01','2018-11-08'
insert into data select '2018-05-01','2018-12-10'
insert into data select '2018-12-01','2018-12-08'
go
SQL
SELECT CASE
WHEN Max(adddate) > Max(moddate) THEN Max(adddate)
ELSE Max(moddate)
END
FROM data
go
Results
