?
Solved

Need to write a query which combines two select statements

Posted on 2014-04-28
16
Medium Priority
?
223 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
[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
  • 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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
 

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 1500 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

649 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