Join failure between two converted to char() dates

[sstengler]

I have the following SQL (6.5) that apparently doesn't see the join correctly, as it doesn't see matching dates that have been converted to char().

select
ia.client_id,
ia.issue_number,
ia.action_date,
convert(varchar(10),ia.action_date,110),
ia2.action_date_c,
ia2.isscount

from
issue_activity ia,
	(select distinct
	ia3.client_id,
	convert(varchar(10),ia3.action_date,110) as action_date_c,
	count(distinct ia3.issue_number) as isscount

	from
	issue_activity ia3

	where
	ia3.action_description not like " Assigned%"
	and ia3.action_description not like " Re%"

	group by ia3.client_id,convert(varchar(10),ia3.action_date,110) ) ia2

where
ia.action_description not like " Assigned%"
and ia.action_description not like " Re%"
and ia2.client_id = ia.client_id
and ia2.action_date_c = convert(varchar(10),ia.action_date,110)

I must be missing something here...all suggestions cheerfully contemplated and tested!

Sam Stengler

 

[JayKusch]

Needs to be ...

ia.action_description not like " Assigned%"
and ia.action_description not like " Re%"
and ia2.client_id = ia.client_id
and convert(varchar(10),ia2.action_date_c,110) = convert(varchar(10),ia.action_date,110)


Thanks

J. Kusch

 

[sstengler]

Thank you, that was it!

However, that introduces another question: the column ia2.action_date_c has already been converted to a char() in the in-line view..why does it need to be converted agian in the WHERE clause in the main SELECT?

Anyone?

Sam