query works fine outside union, but doesnt work .. .

hi all

I have the following query which works fine when it's executed as a single query. but when i union the result of this query with other queries, it returns a different set of data.

any one know why that might be the case??

select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc

i've included the union query here for completeness of the question

begin
declare @.current_date datetime
set @.current_date = GETDATE()

select top 100 _id, callback_date, priority, recency, frequency from
(
(
select top 10 _id, callback_date, 10 priority, @.current_date recency, 1 frequency --, DATEPART(hour, callback_date) "hour", DATEPART(minute, callback_date) "min"
from topcat.class_contact
where status ='callback'
and (DATEPART(year, callback_date) <= DATEPART(year, @.current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @.current_date)) -- all call backs within that hour will be returned
and (DATEPART(hour, callback_date) <= DATEPART(hour, @.current_date))
and (DATEPART(hour, callback_date) <> 0)
order by callback_date asc
--order by priority desc, DATEPART(hour, callback_date) asc, DATEPART(minute, callback_date) asc, callback_date asc
)
union
(
select top 10 _id, callback_date, 9 priority, @.current_date recency, 1 frequency
from topcat.class_contact
where status = 'callback'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @.current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @.current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @.current_date))
and (DATEPART(hour, callback_date) = 0)
order by callback_date asc
)
union
(
select top 10 _id, callback_date, 8 priority, @.current_date recency, 1 frequency
from topcat.class_contact
where status = 'No Connect'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @.current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @.current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @.current_date))
order by callback_date asc
)
union
(
select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc
)
) contact_queue
order by priority desc, recency desc, callback_date asc, frequency desc

endTry using UNION ALL.

-PatP

Query to get values from datetime column into comma separated text

Hi All

I am working on a query to get all the datetime values in a column in a table into a comma separated text.

eg.

ColumnDate
--------
2005-11-09 00:00:00.000
2005-11-13 00:00:00.000
2005-11-14 00:00:00.000
2005-11-16 00:00:00.000

I wanted to get something like

2005-11-09, 2005-11-13, 2005-11-14, 2005-11-16

Have just started SQL and hence am getting confused in what I think should be a relatively simple query. Any help will be much appreciated. Thanks

DECLARE @.List varchar(8000)
SET @.List = ''
select @.List = @.List + convert(varchar, datefield, 102) + ',' from MyTable

Just be careful, because a varchar can hold only 8000 characters, so if your results are more then they are chopped off. For more info on CONVERT function look in the BOL.