Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Posts
    111

    Unanswered: query works fine outside union, but doesn't 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

    end

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try using UNION ALL.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •