?
Solved

How to merge these two queries where the reference table is same.

Posted on 2012-04-11
5
Medium Priority
?
277 Views
Last Modified: 2012-06-22
I write two queries to get the 2 datasets equilvalent from two tables which are joined with geo table. How to get it as one set. so that we can get the one set.


Select X.State,X.District,X.Source,X.Res,Y.Cum_res From

(select geotable.state,geotable.district,geotable.source,sum(isnull(mstchvs.flagr,0)) as  res
from geotable
left join mstchvs
ON geotable.state=mstchvs.state and geotable.district=mstchvs.district and geotable.source=mstchvs.source
and flagr=1 and dateadd(dd,0,DATEDIFF(dd,0,rfeeddate)) >= CONVERT(DATETIME,@fd) and
dateadd(dd,0,DATEDIFF(dd,0,rfeeddate)) <= CONVERT(DATETIME, @td)
--rfeeddate>='2012-3-1' and rfeeddate<='2012-3-31'
group by geotable.state,geotable.district,geotable.source
) X
inner join
(
select geotable.state,geotable.district,geotable.source,sum(isnull(mstchvs.flagr,0)) as cum_res
from geotable
left join mstchvs
ON geotable.state=mstchvs.state and geotable.district=mstchvs.district and geotable.source=mstchvs.source
and flagr=1 and dateadd(dd,0,DATEDIFF(dd,0,rfeeddate)) <= CONVERT(DATETIME, @td)
group by geotable.state,geotable.district,geotable.source
) Y
on X.state = Y.state and X.district = Y.district and X.source = Y.source



Select X.State,X.District,X.Source,X.Res,Y.Cum_res From

(select geotable.state,geotable.district,geotable.source,sum(isnull(mstchvs.flagpre,0)) as  res
from geotable
left join mstchvs
ON geotable.state=mstchvs.state and geotable.district=mstchvs.district and geotable.source=mstchvs.source
and flagpre=1 and dateadd(dd,0,DATEDIFF(dd,0,prefeeddate)) >= CONVERT(DATETIME,@fd) and
dateadd(dd,0,DATEDIFF(dd,0,prefeeddate)) <= CONVERT(DATETIME, @td)
--prefeeddate>='2012-3-1' and prefeeddate<='2012-3-31'
group by geotable.state,geotable.district,geotable.source
) X
inner join
(
select geotable.state,geotable.district,geotable.source,sum(isnull(mstchvs.flagpre,0)) as cum_res
from geotable
left join mstchvs
ON geotable.state=mstchvs.state and geotable.district=mstchvs.district and geotable.source=mstchvs.source
and flagpre=1 and dateadd(dd,0,DATEDIFF(dd,0,prefeeddate)) <= CONVERT(DATETIME, @td)
group by geotable.state,geotable.district,geotable.source
) Y
on X.state = Y.state and X.district = Y.district and X.source = Y.source
0
Comment
Question by:searchsanjaysharma
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 9

Expert Comment

by:armchair_scouse
ID: 37832065
UNION query perhaps?  If your output fields are the same in the two queries (and they are the same data types), you should be able to use a UNION.

http://www.techonthenet.com/sql/union.php
0
 

Author Comment

by:searchsanjaysharma
ID: 37832328
I dont have to fetch common rows.
I want to use CTE.
here geotable is the common table that contain 48 districts. For these districts values are entered in these two tables.
So the queries shows the result reparately with the base table as geo table.
I wanto merge that,
0
 
LVL 9

Accepted Solution

by:
armchair_scouse earned 1000 total points
ID: 37832884
OK, wanting to use CTE wasn't clear from your original question, so UNION isn't for you!  I don't have any practical experience of using CTEs although I appreciate the concept as I have used table variables before.  If you want to use multiple CTEs (one for each query) in one select statement, then consider the following links:

http://www.4guysfromrolla.com/webtech/071906-1.shtml
http://blog.sqlauthority.com/2009/08/08/sql-server-multiple-cte-in-one-select-statement-query/ (see response comment from Tejas Shah as well as main post)

Hope this helps or at least provides a pointer to where you wish to go!
0
 

Author Comment

by:searchsanjaysharma
ID: 37833170
Can you convert my query with cte
0
 

Author Closing Comment

by:searchsanjaysharma
ID: 37837483
ok
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question