Combine 3 Stored procedures to create one output

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

Improve company productivity with a Business Account.Sign Up

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
GPSPOWAuthor Commented:
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
 
LowfatspreadCommented:
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
 
GPSPOWAuthor Commented:
thanks

Glen
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.