Solved

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

Posted on 2012-04-11
5
257 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
  • 3
  • 2
5 Comments
 
LVL 9

Expert Comment

by:armchair_scouse
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
Can you convert my query with cte
0
 

Author Closing Comment

by:searchsanjaysharma
Comment Utility
ok
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now