Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
panthagani
Yak Posting Veteran
58 Posts |
 Posted - 2009-07-21 : 09:21:10
|
Hi,
I need to show the results of each of these queries as a single result set. I tried with UNION ALL but only the result set of the first query is shown. Please advise.
select B.tname,count(A1.aglobalcaseid) as resolved from tblcase A1 INNER JOIN tbluser B
on A1.nOwnerID = B.aUserID
where A1.dlastresolvedAT between getdate() and getdate()-1 and
A1.nsubcasetype is null and A1.bdeleted=0 group by B.tname
UNION ALL
select C.tname as User_Name, count(distinct A2.aglobalcaseid) as Cr_intCount from tblcase A2 INNER JOIN tblcasecr B
on A2.aGlobalCaseID = B.nCaseID
INNER JOIN tbluser C on A2.nOwnerID = C.aUserID
INNER JOIN tblcrtitle D on B.nCRID = D.nCRID
where A2.dlastresolvedAT between getdate() and getdate()-1
and A2.nqueueid=5 group by C.tname |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-07-21 : 09:26:35
|
try to execute the 2nd query by itself. Any result return ?
KH
[spoiler]Time is always against us[/spoiler]
|
 |
|
|
panthagani
Yak Posting Veteran
58 Posts |
Posted - 2009-07-21 : 09:32:20
|
Thanks KH. No rows returned. Just the column headings:
User_name Cr_IntCount |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-07-21 : 09:41:15
|
why are you using "between getdate() and getdate() - 1" ?
Did you try to run this ?
select getdate(), getdate() - 1
I wonder how did you get any result from your 1st query
KH
[spoiler]Time is always against us[/spoiler]
|
|
|
|
panthagani
Yak Posting Veteran
58 Posts |
Posted - 2009-07-21 : 09:56:06
|
OK. I changed the query a little bit... Please advise.
select B.tname,count(A1.aglobalcaseid) as resolved from tblcase A1 INNER JOIN tbluser B
on A1.nOwnerID = B.aUserID
where A1.dlastresolvedAT <= getdate() and A1.dlastresolvedAT > getdate()-1 and
A1.nsubcasetype is null and A1.bdeleted=0 group by B.tname
UNION ALL
select C.tname as User_Name, count(distinct A2.aglobalcaseid) as Cr_intCount from tblcase A2 INNER JOIN tblcasecr B
on A2.aGlobalCaseID = B.nCaseID
INNER JOIN tbluser C on A2.nOwnerID = C.aUserID
INNER JOIN tblcrtitle D on B.nCRID = D.nCRID
where A2.dlastresolvedAT <= getdate() and A2.dlastresolvedAT > getdate()-1
and A2.nqueueid=5 group by C.tname |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-07-21 : 10:05:15
|
so does it work for you ?
KH
[spoiler]Time is always against us[/spoiler]
|
|
|
|
panthagani
Yak Posting Veteran
58 Posts |
Posted - 2009-07-21 : 11:01:36
|
| Unfortunately, it does not... |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-07-21 : 11:12:13
|
any error or anything ?
if not check your query JOIN and WHERE condition make sure it is correct.
or post your table structure with some sample data and the required result
see http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
KH
[spoiler]Time is always against us[/spoiler]
|
|
|
|
|
|
|
Join two queries with UNION ALL. Please advise!