Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Combine 3 Stored procedures to create one output

Posted on 2015-01-22
4
Medium Priority
?
94 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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

885 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