Solved

Need to write a query which combines two select statements

Posted on 2014-04-28
16
213 Views
Last Modified: 2014-04-29
My requirement is here, please have a look.

--For Hospitals
Select TOP 30 SubSystemName, SUM(SQS_Amt) …. WHERE Provider_Type = ‘Hospital’ group by Subsystemname ORDER BY SUM (SQS_AMT)
We need to do something similar for physicians, but it needs to based on the Top 30 Objecting Systems
The logic would be Select SubSystemName, SUM(SQS_AMT) … WHERE PROVIDER_TYPE = ‘Physician’ and SubSystemName in (Select TOP 30 SubSystemName, SUM(SQS_Amt) …. WHERE Provider_Type = ‘Hospital’ …  ORDER BY SUM (SQS_AMT)
Basically – I want to identify the Top Objecting Systems by summing $ and descending, and then I want to find the related physicians.  Can you try to write that logic?

I am trying to write the logic for this, can anyone help me out
0
Comment
Question by:Aparanjith
  • 10
  • 6
16 Comments
 
LVL 22

Expert Comment

by:plusone3055
ID: 40027435
your logic is fine

all your missing is a UNION
to join the queries together
0
 

Author Comment

by:Aparanjith
ID: 40027444
I am trying this, this is giving me results, but I am not sure that whether I am right or wrong. Can you look into it and tell me, I used where exists in the logic

Select TOP 30 SubSystemName
, SUM(Act_SQS_Amt)Sum_SQS_AMT
from dbo.FINALProviderTracking
 WHERE  Exists
 (Select TOP 30 SubSystemName
, SUM(Act_SQS_Amt)Sum_SQS_AMT
from dbo.FINALProviderTracking
 WHERE Prov_Type = 'Hospital'
 group by SubSystemName
 ORDER BY SUM (Act_SQS_AMT)
 )
 and Prov_Type = 'Physician'
 group by SubSystemName
 ORDER BY SUM (Act_SQS_AMT)

Please let me know whether I am right or wrong?
0
 
LVL 22

Expert Comment

by:plusone3055
ID: 40027448
Select TOP 30 SubSystemName, SUM(SQS_Amt) …. WHERE Provider_Type = ‘Hospital’ group by Subsystemname ORDER BY SUM (SQS_AMT)
We need to do something similar for physicians, but it needs to based on the Top 30 Objecting Systems
The logic would be Select SubSystemName, SUM(SQS_AMT) … WHERE PROVIDER_TYPE = ‘Physician’ and SubSystemName in (Select TOP 30 SubSystemName, SUM(SQS_Amt) …. WHERE Provider_Type = ‘Hospital’ …  ORDER BY SUM (SQS_AMT)

SELECT TOP 30 SubsystemName
                          ,SUM(SQS_Amt)
UNION
SELECT TOP 30  SubsystemName
                          ,SUM(SQS_Amt)
 WHERE             Provider_Type  = 'Hospital
OR                     Provider_Type = 'Physician'
GROUP BY       SubsystemName
ORDER BY        SUM (SQS_AMT)
0
 

Author Comment

by:Aparanjith
ID: 40027485
No man, you got it wrong, i am concentrating the on second query which is pulling the data based on first one. That is,


Fierst query is for Hospitals: Select TOP 30 SubSystemName, SUM(SQS_Amt) ….from ABC
 WHERE Provider_Type = ‘Hospital’
group by Subsystemname ORDER BY SUM (SQS_AMT)

Based on this we should pull second query data which is for physicians:
Select SubSystemName, SUM(SQS_AMT) … from ABC
WHERE PROVIDER_TYPE = ‘Physician’
and SubSystemName in (Select TOP 30 SubSystemName, SUM(SQS_Amt) …. WHERE Provider_Type = ‘Hospital’ …  ORDER BY SUM (SQS_AMT)

I am confused how to write the sub query where subsystemname should be data which exists in First query. As i have written the code in previous post, it is giving results but i am not sure whether it is right or not?
0
 
LVL 22

Expert Comment

by:plusone3055
ID: 40027494
My sincerest apologies i tried to edit that comment when i re-read the post the second time...

I was trying to clean  my glasses :)
and first thought.. Nevermind. Entirely my fault :)
0
 
LVL 22

Expert Comment

by:plusone3055
ID: 40027521
Looks like your logic is correct.

 for better reading I would make the first query a view  (V_Hospital)
and then write the 2nd query like so

Select SubSystemName, SUM(SQS_AMT) … from ABC
WHERE PROVIDER_TYPE = ‘Physician’
and SubSystemName in (V_Hospital.SubsystemName)
that way it's jsut pulling those TOP 30 SubsystemNames from the view :)
0
 

Author Comment

by:Aparanjith
ID: 40027550
I used this as you said, but i am getting this error do not why?

Select TOP 30 Systemname,
 SubSystemName
, SUM(Act_SQS_Amt)Sum_SQS_AMT
from dbo.FINALProviderTracking
 WHERE Prov_Type = 'Physician'
 and SubSystemName in (dbo.Hospital_V)
 group by SubSystemName,Systemname
 ORDER BY SUM (Act_SQS_AMT)


Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "pact.dbo.Hospital_V" could not be bound.
0
 
LVL 22

Expert Comment

by:plusone3055
ID: 40027572
Select TOP 30 Systemname,
 SubSystemName
, SUM(Act_SQS_Amt)Sum_SQS_AMT
from dbo.FINALProviderTracking
 WHERE Prov_Type = 'Physician'
 and SubSystemName in (dbo.Hospital_V.SubsystemName)
 group by SubSystemName,Systemname
 ORDER BY SUM (Act_SQS_AMT)

you have to create the view then
you have to identify what column you are getting from the view  in your query
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Aparanjith
ID: 40027575
Sorry i did used column too, but the same error

Select TOP 30 Systemname,
 SubSystemName
, SUM(Act_SQS_Amt)Sum_SQS_AMT
from dbo.FINALProviderTracking
 WHERE Prov_Type = 'Physician'
 and SubSystemName in ([dbo].[Hospital_V].SubSystemName)
 group by SubSystemName,Systemname
 ORDER BY Sum_SQS_AMT


 Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "dbo.Hospital_V.SubSystemName" could not be bound.
0
 
LVL 22

Expert Comment

by:plusone3055
ID: 40027589
Select TOP 30 Systemname,
 SubSystemName
, SUM(Act_SQS_Amt)Sum_SQS_AMT
from dbo.FINALProviderTracking
 WHERE Prov_Type = 'Physician'
 and SubSystemName in (V_Hostpital.SubSystemName)
 group by SubSystemName,Systemname
 ORDER BY Sum_SQS_AMT
0
 
LVL 22

Expert Comment

by:plusone3055
ID: 40027592
have you created the view in that table yet ?
0
 

Author Comment

by:Aparanjith
ID: 40027597
Yes I did created the view.
0
 
LVL 22

Expert Comment

by:plusone3055
ID: 40027606
GO
CREATE VIEW V_Hospital
AS
Select TOP 30 SubSystemName
                      , SUM(SQS_Amt) …. WHERE Provider_Type = ‘Hospital’ group by Subsystemname ORDER BY SUM (SQS_AMT)
GO

Select TOP 30 Systemname,
 SubSystemName
, SUM(Act_SQS_Amt)Sum_SQS_AMT
from dbo.FINALProviderTracking
 WHERE Prov_Type = 'Physician'
 and SubSystemName in ( SELECT DISTINCT V_Hostpital.SubSystemName FROM V_Hospital)
 group by SubSystemName,Systemname
 ORDER BY Sum_SQS_AMT
0
 
LVL 22

Expert Comment

by:plusone3055
ID: 40027608
there you go :)
0
 

Author Comment

by:Aparanjith
ID: 40027640
No man I think view is not working for me, may be some rights issue, cant we right the sub query with in the query where we do not need to create View.

The problem is we need to write the sub query such that if we are using top 40 or top 50 rows later, we has to again create a view, which is not good idea, so i want something which can be written in one query. may be by using sub query??
0
 
LVL 22

Accepted Solution

by:
plusone3055 earned 500 total points
ID: 40027649
okay then...


Select TOP 30 Systemname,
 SubSystemName
, SUM(Act_SQS_Amt)Sum_SQS_AMT
from dbo.FINALProviderTracking
 WHERE Prov_Type = 'Physician'
 and SubSystemName in ( Select TOP 30 SubSystemName WHERE Provider_Type = ‘Hospital group by Subsystemname)
 group by SubSystemName,Systemname
 ORDER BY Sum_SQS_AMT


remember in your subquery you can only select the column you are looking for the SUM is not necessary
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

758 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

20 Experts available now in Live!

Get 1:1 Help Now