Need to write a query which combines two select statements

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
AparanjithAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
plusone3055Connect With a Mentor Commented:
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
 
plusone3055Commented:
your logic is fine

all your missing is a UNION
to join the queries together
0
 
AparanjithAuthor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
plusone3055Commented:
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
 
AparanjithAuthor Commented:
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
 
plusone3055Commented:
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
 
plusone3055Commented:
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
 
AparanjithAuthor Commented:
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
 
plusone3055Commented:
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
 
AparanjithAuthor Commented:
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
 
plusone3055Commented:
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
 
plusone3055Commented:
have you created the view in that table yet ?
0
 
AparanjithAuthor Commented:
Yes I did created the view.
0
 
plusone3055Commented:
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
 
plusone3055Commented:
there you go :)
0
 
AparanjithAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.