Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
cplusplus
Aged Yak Warrior
567 Posts |
Posted - 2013-08-16 : 10:00:01
|
I want to set the active_date for all acct_units based on second table join.this second table 'glnamesau' is a newly created table not all acct_units are found. in those cases hardcode the date value for column Active_date.first table is: glnames, second table is glnamesau.select acct_unit, Case When active_status='A' then (select max(r_date) from glnamesau where acct_unit=acct_unit and new_value='A') END AS Active_DATEfrom glnames If record is not found in glnamesau based on glnames.acct_unit and glnamesau.new_value = 'A' then apply this date as active_date ELSE CAST('19000101' as date) |
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2013-08-16 : 10:37:10
|
Try this:select acct_unit, Case When active_status='A' then (select COALESCE(max(r_date),CAST('19000101' as date)) from glnamesau where acct_unit=acct_unit and new_value='A') END AS Active_DATEfrom glnames Hope this helps.Duane |
|
|
cplusplus
Aged Yak Warrior
567 Posts |
Posted - 2013-08-16 : 10:51:45
|
Hello Duane, Thanks.I am getting all 596 rows total rows active date as 16th august which is today.it should show 19000101 as active_date. |
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2013-08-16 : 11:40:38
|
How about something like this?select acct_unit, Case When active_status='A' then (select max(CAST(r_date AS DATE)) from glnamesau where glNames.acct_unit=glnamesau.acct_unit AND new_value='A') ELSE CAST('19000101' as date) END AS Active_DATEfrom glnames Duane |
|
|
|
|
|