Help with union all on select top x statements!

The following runs fine without the union (returns 3 sets), but how do I union these together? I need the 5 most recent records in each select, and I don't know how to union a dynmaic sql statement in to the mix.

TIA!


set @cmd = 'select top 5 isnull(u.userid,'''') as uid, ''CALL'' as type, cdatetime as date,
case when len(rtrim(cext))=3 then ''CALL OUT'' else ''CALL IN'' end + '' (''+ rtrim(pro30.dbo.seconds(cdur)) + '')'' as note from pro30..calls c, pro30..syuuser u where c.cext *= u.phone and
(' + @outc + ' or ' + @inc + ') order by cdatetime desc'


select top 5 adduser as uid, 'NOTE' as type, adddate as date, note as note from arnote where custno = @custno order by adddate desc
 
union all

select top 5 adduser as uid, 'SALE' as type, adddate as date,
('INVNO: ' + ltrim(invno) + '|SONO: ' + rtrim(ltrim(ornum)) + '|AMOUNT: ' + str(invamt,8,2) + '|TERMS: ' + ltrim(pterms)) as note
 from armast where custno = @custno order by adddate desc

union all

exec sp_executesql @cmd
TerryBurgerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeff TennessenAssistant Vice PresidentCommented:
If one of the SELECTs is dynamic SQL, they will all have to be part of the dynamic SQL string. Like so:

    set @cmd = 'select top 5 isnull(u.userid,'''') as uid, ''CALL'' as type, cdatetime as date,
    case when len(rtrim(cext))=3 then ''CALL OUT'' else ''CALL IN'' end + '' (''+ rtrim(pro30.dbo.seconds(cdur)) + '')'' as note from pro30..calls c, pro30..syuuser u where c.cext *= u.phone and
    (' + @outc + ' or ' + @inc + ')
    union all
    select top 5 adduser as uid, ''NOTE'' as type, adddate as date, note as note from arnote where custno = @custno
    union all
    select top 5 adduser as uid, ''SALE'' as type, adddate as date,
    (''INVNO: '' + ltrim(invno) + ''|SONO: '' + rtrim(ltrim(ornum)) + ''|AMOUNT: '' + str(invamt,8,2) + ''|TERMS: '' + ltrim(pterms)) as note
    from armast where custno = @custno
    order by date desc'
   
    exec sp_executesql @cmd

Jeff
0
Jeff TennessenAssistant Vice PresidentCommented:
Sorry, slight change to handle the other local variable (@custno):

    set @cmd = 'select top 5 isnull(u.userid,'''') as uid, ''CALL'' as type, cdatetime as date,
    case when len(rtrim(cext))=3 then ''CALL OUT'' else ''CALL IN'' end + '' (''+ rtrim(pro30.dbo.seconds(cdur)) + '')'' as note from pro30..calls c, pro30..syuuser u where c.cext *= u.phone and
    (' + @outc + ' or ' + @inc + ')
    union all
    select top 5 adduser as uid, ''NOTE'' as type, adddate as date, note as note from arnote where custno = ''' + @custno + '''
    union all
    select top 5 adduser as uid, ''SALE'' as type, adddate as date,
    (''INVNO: '' + ltrim(invno) + ''|SONO: '' + rtrim(ltrim(ornum)) + ''|AMOUNT: '' + str(invamt,8,2) + ''|TERMS: '' + ltrim(pterms)) as note
    from armast where custno = ''' + @custno + '''
    order by date desc'
   
    exec sp_executesql @cmd

Jeff
0
TerryBurgerAuthor Commented:
Hi Jeff, the union works, but without the order by in each catagory I don't get the 5 most recent items from each. Any suggestions?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeff TennessenAssistant Vice PresidentCommented:
Hi TerryBurger,

Are you sure? I get the 5 top values per the ORDER BY when I run it. Of course, I don't have your exact table structure and data, but I ran something like this to test it:

    SET @cmd = 'SELECT TOP 5 Field1,
        Field2
    FROM Table1
    WHERE Field2 = 123
    UNION ALL SELECT TOP 5 Field1,
        Field2
    FROM Table1
    WHERE Field2 = 234
    UNION ALL SELECT TOP 5 Field1,
        Field2
    FROM Table1
    WHERE Field2 = 345
    ORDER BY Field1 DESC'
   
    EXEC sp_executesql @cmd

The results I get are:

    Field1      Field2
    397612    123
    397611    123
    397610    123
    397609    123
    397608    123
    397460    234
    397426    234
    397222    234
    397221    234
    397187    234
    393931    345
    372534    345
    371617    345
    371258    345
    371257    345

Field1 is an identity column, and those values do correspond to the 5 highest for each of the Field2 "categories" represented. If you're still getting incorrect results, could you tell me about your table schema and some representative data? I will try to figure out what's going wrong.

Jeff
0
TerryBurgerAuthor Commented:
There is really nothing special about the data, as in each case adddate resolves to a datetime (might be a small datetime in one of the selects, could short datetime not play well with datetime?)

Here are my results:

<dynamic union as we discussed:>

ALBL      NOTE      2006-04-17 07:15:00.000      MORE ON THE WAY VIA 2 DAY -                                                                  
ALBL      NOTE      2006-04-11 09:15:00.000      SENT A BACKORDER OF NS AND RYO -                                                                                                                                                                        
ALBL      NOTE      2006-04-10 07:14:00.000      2 DAY FOR MIKE - CALL WHEN NS ARE BACK-IN-                                                                                                                                                              
ALBL      NOTE      2006-04-05 10:26:00.000      .                                                                                                                                                                                                      
ALBL      NOTE      2006-04-05 07:31:00.000      MIKE WILL GET HIS B/O STUFF ON MONDAY - TOO MANY CHECKS IN 1 WEEK -                                                                                                                                    
ALBL      CALL      2006-03-10 08:09:45.000      CALL OUT (0:00)
ALBL      CALL      2006-03-09 12:30:30.000      CALL OUT (0:01)
ALBL      CALL      2006-03-06 07:23:26.000      CALL OUT (0:00)
ARTW      SALE      2005-10-13 11:50:00.000      INVNO: 626576|SONO: 624919|AMOUNT:    14.55|TERMS: Net 15 Days        
ALBL      CALL      2005-09-29 12:03:21.000      CALL OUT (0:00)
ALBL      CALL      2005-09-21 12:05:37.000      CALL OUT (0:01)
CARM      SALE      2005-06-13 14:02:00.000      INVNO: 608834|SONO: 607344|AMOUNT:  3969.25|TERMS: C.O.D.              
ALBL      SALE      2005-04-11 00:00:00.000      INVNO: 600935|SONO: 599588|AMOUNT:  3036.71|TERMS: C.O.D.              
AUTO      SALE      2004-07-19 00:00:00.000      INVNO: 568433|SONO: 567999|AMOUNT:  3070.55|TERMS: C.O.D.              
ALOC      SALE      2003-05-27 00:00:00.000      INVNO: 700766|SONO: 700813|AMOUNT:  2355.80|TERMS: C.O.D.  


<< Without union, 3 seperate sets:>

ALBL      NOTE      2006-04-17 07:15:00      MORE ON THE WAY VIA 2 DAY -                                                                                                                                                                            
ALBL      NOTE      2006-04-11 09:15:00      SENT A BACKORDER OF NS AND RYO -                                                                                                                                                                        
ALBL      NOTE      2006-04-10 07:14:00      2 DAY FOR MIKE - CALL WHEN NS ARE BACK-IN-                                                                                                                                                              
ALBL      NOTE      2006-04-05 10:26:00      .                                                                                                                                                                                                      
ALBL      NOTE      2006-04-05 07:31:00      MIKE WILL GET HIS B/O STUFF ON MONDAY - TOO MANY CHECKS IN 1 WEEK -


ALBL      SALE      2006-04-17 00:00:00      INVNO: 652803|SONO: 765691|AMOUNT:  2959.11|TERMS: C.O.D.              
ALBL      SALE      2006-04-11 14:46:00      INVNO: 651940|SONO: 764929|AMOUNT:   343.42|TERMS: C.O.D.              
ALBL      SALE      2006-04-10 14:57:00      INVNO: 651657|SONO: 764600|AMOUNT:  3026.07|TERMS: C.O.D.              
ALBL      SALE      2006-04-03 11:08:00      INVNO: 650485|SONO: 763433|AMOUNT:  2287.11|TERMS: C.O.D.              
MMAC      SALE      2006-03-30 09:05:00      INVNO: 650001|SONO: 762998|AMOUNT:   473.66|TERMS: C.O.D.          


            CALL      2006-04-17 06:15:19.000      CALL IN (0:11)
ALBL      CALL      2006-04-11 08:18:06.000      CALL OUT (0:00)
          CALL      2006-04-10 06:14:38.000      CALL IN (0:10)
ALBL      CALL      2006-04-05 06:33:28.000      CALL OUT (0:03)
          CALL      2006-04-03 06:14:46.000      CALL IN (0:10)
 
                                                                                                                                 

Note the "SALE" category, I get 4 from April 06, and 1 from March 06. In the combined statement above, all sales seem to fall in 2005.
I seem to remember generally you can't use order by within a union statement unless you also have a top x command in the same statement, but in this it doesn't allow the order by in each. Maybe I need to rearrange some parentheses?
0
Jeff TennessenAssistant Vice PresidentCommented:
I see what you're saying. Try this and see if you get what you're expecting:

    set @cmd = 'select * from (select top 5 isnull(u.userid,'''') as uid, ''CALL'' as type, cdatetime as date,
    case when len(rtrim(cext))=3 then ''CALL OUT'' else ''CALL IN'' end + '' (''+ rtrim(pro30.dbo.seconds(cdur)) + '')'' as note from pro30..calls c, pro30..syuuser u where c.cext *= u.phone and
    (' + @outc + ' or ' + @inc + ') order by cdatetime desc) q1
    union all select * from
    (select top 5 adduser as uid, ''NOTE'' as type, adddate as date, note as note from arnote where custno = ''' + @custno + ''' order by adddate desc) q2
    union all select * from
    (select top 5 adduser as uid, ''SALE'' as type, adddate as date,
    (''INVNO: '' + ltrim(invno) + ''|SONO: '' + rtrim(ltrim(ornum)) + ''|AMOUNT: '' + str(invamt,8,2) + ''|TERMS: '' + ltrim(pterms)) as note
    from armast where custno = ''' + @custno + '''
    order by adddate desc) q3'
   
    exec sp_executesql @cmd

Jeff
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TerryBurgerAuthor Commented:
Works great! I just added an additonal 'order by date' and I'm set. Thanks Jeff!


Terry
0
Jeff TennessenAssistant Vice PresidentCommented:
You're welcome, Terry. Glad I could help!

Jeff
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.