Solved

Combine 3 Stored procedures to create one output

Posted on 2015-01-22
4
87 Views
Last Modified: 2015-01-28
I have the following 3 stored procedures.  Each summarizes table data into Current Month, Current Year and Prior Year totals based on the same 3 user inputs; Month, Year and Phys.   There are 3 common fields between each SP:

PCP
Surg
SURGNAME


The Phys variable is tied to the PCP field.

I want to combine them so I get one line for each of the two row levels: PCP and SURGNAME.


The resulting output should be:

PCP        SURGNAME        CMO     CYR      PYR


Here are the 3 SP's.

USE [livedb]
GO
/****** Object:  StoredProcedure [dbo].[GetPCP_Summary_CMO]    Script Date: 01/22/2015 12:19:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Glen Powers
-- Create date: January 22, 2015
-- Description:	SDC Surgeries  as PCP physician
-- =============================================
ALTER PROCEDURE [dbo].[GetPCP_Summary_CMO] 
	-- Add the parameters for the stored procedure here
	(@MONTH int, 
	@YEAR int,
	@PHYS varchar(10))
	As
SELECT  
      [PCP]
     
    ,Surg
      
      ,[SURGNAME]
      
      ,sum([SDC]) as SCMO
      
  FROM [livedb].[dbo].[tbl_PhysRefRegistrations]
  where MO=@MONTH and YR =@YEAR and PCP = @PHYS 
  group by [PCP]
     
      ,Surg
      
      ,[SURGNAME]
      
      having  sum([SDC]) <> 0
      
  order by SURGNAME
  

USE [livedb]
GO
/****** Object:  StoredProcedure [dbo].[GetPCP_Summary_CYR]    Script Date: 01/22/2015 12:23:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Glen Powers
-- Create date: January 22, 2015
-- Description:	SDC Surgeries  as PCP physician
-- =============================================
ALTER PROCEDURE [dbo].[GetPCP_Summary_CYR] 
	-- Add the parameters for the stored procedure here
	(@MONTH int, 
	@YEAR int,
	@PHYS varchar(10))
	As
SELECT  
      [PCP]
     
    ,Surg
      
      ,[SURGNAME]
      
      ,sum([SDC]) as SCYR
      
  FROM [livedb].[dbo].[tbl_PhysRefRegistrations]
  where MO<=@MONTH and YR =@YEAR and PCP = @PHYS 
  group by [PCP]
     
      ,Surg
      
      ,[SURGNAME]
      
      having  sum([SDC]) <> 0
      
  order by SURGNAME
  
  
USE [livedb]
GO
/****** Object:  StoredProcedure [dbo].[GetPCP_Summary_PYR]    Script Date: 01/22/2015 12:23:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Glen Powers
-- Create date: January 22, 2015
-- Description:	SDC Surgeries  as PCP physician
-- =============================================
ALTER PROCEDURE [dbo].[GetPCP_Summary_PYR] 
	-- Add the parameters for the stored procedure here
	(@MONTH int, 
	@YEAR int,
	@PHYS varchar(10))
	As
SELECT  
      [PCP]
     
    ,Surg
      
      ,[SURGNAME]
      
      ,sum([SDC]) as SPYR
      
  FROM [livedb].[dbo].[tbl_PhysRefRegistrations]
  where MO<=@MONTH and YR =@YEAR-1 and PCP = @PHYS 
  group by [PCP]
     
      ,Surg
      
      ,[SURGNAME]
      
      having  sum([SDC]) <> 0
      
  order by SURGNAME
  
  

Open in new window


Thanks

Glen
0
Comment
Question by:GPSPOW
  • 2
4 Comments
 

Author Comment

by:GPSPOW
ID: 40565098
I did some more work on the above procedures and was able to combine them to produce the desired output.

However, I would like some help passing the variables supplied by the user.  Currently I have hard coded the variables for each SP.  What is the correct syntax to prompt the user for input?

USE [livedb]
GO
/****** Object:  StoredProcedure [dbo].[GetPCP_Summary_ALL]    Script Date: 01/22/2015 13:58:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Glen Powers
-- Create date: January 22, 2015
-- Description:	SDC Surgeries  as PCP physician
-- =============================================
ALTER PROCEDURE [dbo].[GetPCP_Summary_ALL] 
	-- Add the parameters for the stored procedure here
	as
	Declare
	@MONTH int; 
	Declare @YEAR int;
	declare @PHYS varchar(10);
	
	
	
	
	create table #CMO(
	PCP varchar (10),
	Surg varchar(10),
	SURGNAME varchar (74),
	SCMO int);
	
	create table #CYR(
	PCP varchar (10),
	Surg varchar(10),
	SURGNAME varchar (74),
	SCYR int);
	
	create table #PYR(
	PCP varchar (10),
	Surg varchar(10),
	SURGNAME varchar (74),
	SPYR int);
	
	
	
	insert into #CMO
	exec GetPCP_Summary_CMO
	@MONTH=12,
	@YEAR=2014,
	@PHYS='HADSO'
	
	insert into #CYR
	exec GetPCP_Summary_CYR
	@MONTH=12,
	@YEAR=2014,
	@PHYS='HADSO'
	
	insert into #PYR
	exec GetPCP_Summary_PYR
	@MONTH=12,
	@YEAR=2014,
	@PHYS='HADSO'
	
	
	Select C1.PCP, C1.SURGNAME, C1.SCMO as CMO, C2.SCYR as CYR, P1.SPYR as PYR
	 into #PCP_Temp
	from #CMO as C1
			inner join #CYR as C2 on C1.Surg = C2.Surg
			inner join #PYR as P1 on C1.Surg = P1.Surg
			
	Select * from #PCP_Temp

Open in new window


thanks

Glen
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 40565165
This single query should give you the results you want:

 SELECT
      [PCP]    
      ,Surg
      ,sum(CASE WHEN YR = @YEAR AND MO = @MONTH THEN [SDC] ELSE 0 END) as SCMO
      ,sum(CASE WHEN YR = @YEAR AND MO <= @MONTH THEN [SDC] ELSE 0 END) as SCYR
      ,sum(CASE WHEN YR = @YEAR - 1 AND MO <=@MONTH THEN [SDC] ELSE 0 END) as SPYR
 FROM [livedb].[dbo].[tbl_PhysRefRegistrations]
 WHERE
     YR IN (@YEAR, @YEAR - 1) AND
     PCP = @PHYS
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 40569833
just a couple of extensions to scotts code to get back to your original scenario...

and do you want zeros  or nulls if a particular calculations doesn't apply?

(did you really want to exclude the rows that don't exist in all 3 aggregates (as your coded sp does?)

select [pcp],surgname,scmo,scyr,spyr
from (
SELECT
      @phys as [PCP]     
      ,Surg,surgname
      ,sum(CASE WHEN YR = @YEAR AND MO = @MONTH THEN [SDC] ELSE 0 END) as SCMO
      ,sum(CASE WHEN YR = @YEAR AND MO <= @MONTH THEN [SDC] ELSE 0 END) as SCYR
      ,sum(CASE WHEN YR = @YEAR - 1 AND MO <=@MONTH THEN [SDC] ELSE 0 END) as SPYR
 FROM [livedb].[dbo].[tbl_PhysRefRegistrations]
 WHERE
     YR IN (@YEAR, @YEAR - 1) AND
     PCP = @PHYS
group by surg,surgname
having sum(CASE WHEN YR = @YEAR AND MO = @MONTH THEN [SDC] ELSE 0 END) > 0
      or sum(CASE WHEN YR = @YEAR AND MO <= @MONTH THEN [SDC] ELSE 0 END) > 0
      or sum(CASE WHEN YR = @YEAR - 1 AND MO <=@MONTH THEN [SDC] ELSE 0 END)  >0) as x
order by 2 

Open in new window

0
 

Author Closing Comment

by:GPSPOW
ID: 40576451
thanks

Glen
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

920 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

16 Experts available now in Live!

Get 1:1 Help Now