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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 select query setting act dates based on 2nd table

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_DATE
from 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_DATE
from glnames

Hope this helps.

Duane
Go to Top of Page

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.

Go to Top of Page

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_DATE
from glnames


Duane
Go to Top of Page
   

- Advertisement -