Solved

Combine 3 Stored procedures to create one output

Posted on 2015-01-22
4
86 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

707 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

14 Experts available now in Live!

Get 1:1 Help Now