Help with union all on select top x statements!
Posted on 2006-04-19
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.
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
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
exec sp_executesql @cmd