Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Combine 3 Stored procedures to create one output

Posted on 2015-01-22
4
Medium Priority
?
93 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
[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
  • 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

721 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