SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 select query setting act dates based on 2nd table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cplusplus
Aged Yak Warrior

534 Posts

Posted - 08/16/2013 :  10:00:01  Show Profile  Reply with Quote
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)

Edited by - cplusplus on 08/16/2013 10:19:44

duanecwilson
Constraint Violating Yak Guru

USA
273 Posts

Posted - 08/16/2013 :  10:37:10  Show Profile  Reply with Quote
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

534 Posts

Posted - 08/16/2013 :  10:51:45  Show Profile  Reply with Quote
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

USA
273 Posts

Posted - 08/16/2013 :  11:40:38  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000