asked on

SP to find and replace data...

On SQL 2000.... Experts, I am terrible in creating SP's... I need help in converting the process below into a SP....

-----------------------------------------------------------------------------------------------
-- STEP1:  THIS CREATES TEMP TABLE 'X_TEMP_Rpt_Admission20110302'
-----------------------------------------------------------------------------------------------
exec USP_Rpt_Admission20110302 '
SELECT HPP.ccGrpName, HPP.ccYRMM, MDP.PDSPCL, MDP.PDNAME AS DoctorName, 1 as cntr
FROM  dbo.VW_HPPATMFL HPP
INNER JOIN dbo.VW_MasterDoctorPt MDP ON HPP.PPGRPno = MDP.PJGRP# AND HPP.PPPATno = MDP.PJPAT#
WHERE (HPP.PPTYPE = ''IP'') 
AND (HPP.PPMTCD <> ''D'') 
AND (mdp.PJAFTP = ''2'') 
AND (HPP.ccYRMM BETWEEN DATENAME(year, DATEADD(m, - 24, CONVERT (varchar, GETDATE(), 112))) + ''-'' + RIGHT (''0'' + CAST(DATEPART(month, DATEADD(m, - 0, CONVERT (varchar, GETDATE(), 112))) AS VarChar), 2) and DATENAME(year, DATEADD(m, - 1, CONVERT (varchar, GETDATE(), 112))) + ''-'' + RIGHT (''0'' + CAST(DATEPART(month, DATEADD(m, - 1, CONVERT (varchar, GETDATE(), 112))) AS VarChar), 2))
AND (MDP.PDSPCL <> ''nur'') 
AND (HPP.ccGrpName = ''ELA'')
',
'ccYRMM',
'count(cntr)[]',
'PDSPCL,DoctorName'
-----------------------------------------------------------------------------------------------
-- STEP2:   I Need to find if records exist on Reporting Table using this criteria or similar...
-----------------------------------------------------------------------------------------------
SELECT COUNT(*) AS qty
FROM  dbo.Rpt_Admission24
GROUP BY RptYearMonth
HAVING (RptYearMonth = DATENAME(year, GETDATE()) + '-' + RIGHT('0' + CAST(DATEPART(month, DATEADD(m, - 1, GETDATE())) AS VarChar), 2))
-----------------------------------------------------------------------------------------------
-- STEP3: if NO critera exists... all i want to do is update with statement below...
-----------------------------------------------------------------------------------------------
insert into Rpt_Admission24 ([RptYearMonth]
      ,[PDSPCL]
      ,[DoctorName]
      ,[Month1]
      ,[Month2]
      ,[Month3]
      ,[Month4]
      ,[Month5]
      ,[Month6]
      ,[Month7]
      ,[Month8]
      ,[Month9]
      ,[Month10]
      ,[Month11]
      ,[Month12]
      ,[Month13]
      ,[Month14]
      ,[Month15]
      ,[Month16]
      ,[Month17]
      ,[Month18]
      ,[Month19]
      ,[Month20]
      ,[Month21]
      ,[Month22]
      ,[Month23]
      ,[Month24])
SELECT *
  FROM [ReportDB].[dbo].[X_TEMP_Rpt_Admission20110302] 
-----------------------------------------------------------------------------------------------
-- If records exist, I want to delete data then update
-----------------------------------------------------------------------------------------------
delete from Rpt_admission24
where (RptYearMonth = DATENAME(year, GETDATE()) + '-' + RIGHT('0' + CAST(DATEPART(month, DATEADD(m, - 1, GETDATE())) AS VarChar), 2))
GO

insert into Rpt_Admission24 ([RptYearMonth]
      ,[PDSPCL]
      ,[DoctorName]
      ,[Month1]
      ,[Month2]
      ,[Month3]
      ,[Month4]
      ,[Month5]
      ,[Month6]
      ,[Month7]
      ,[Month8]
      ,[Month9]
      ,[Month10]
      ,[Month11]
      ,[Month12]
      ,[Month13]
      ,[Month14]
      ,[Month15]
      ,[Month16]
      ,[Month17]
      ,[Month18]
      ,[Month19]
      ,[Month20]
      ,[Month21]
      ,[Month22]
      ,[Month23]
      ,[Month24])
SELECT *
  FROM [ReportDB].[dbo].[X_TEMP_Rpt_Admission20110302] 
GO

Open in new window

ASKER CERTIFIED SOLUTION

wdosanjos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ephraim Wangoya

TRY THIS

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE procName 
AS
BEGIN
  EXEC USP_Rpt_Admission20110302 '
		SELECT HPP.ccGrpName, HPP.ccYRMM, MDP.PDSPCL, MDP.PDNAME AS DoctorName, 1 as cntr
		FROM  dbo.VW_HPPATMFL HPP
		INNER JOIN dbo.VW_MasterDoctorPt MDP ON HPP.PPGRPno = MDP.PJGRP# AND HPP.PPPATno = MDP.PJPAT#
		WHERE (HPP.PPTYPE = ''IP'') 
		AND (HPP.PPMTCD <> ''D'') 
		AND (mdp.PJAFTP = ''2'') 
		AND (HPP.ccYRMM BETWEEN DATENAME(year, DATEADD(m, - 24, CONVERT (varchar, GETDATE(), 112))) + ''-'' + RIGHT (''0'' + CAST(DATEPART(month, DATEADD(m, - 0, CONVERT (varchar, GETDATE(), 112))) AS VarChar), 2) and DATENAME(year, DATEADD(m, - 1, CONVERT (varchar, GETDATE(), 112))) + ''-'' + RIGHT (''0'' + CAST(DATEPART(month, DATEADD(m, - 1, CONVERT (varchar, GETDATE(), 112))) AS VarChar), 2))
		AND (MDP.PDSPCL <> ''nur'') 
		AND (HPP.ccGrpName = ''ELA'')
		',
		'ccYRMM',
		'count(cntr)[]',
		'PDSPCL,DoctorName'
	
	IF EXISTS(SELECT COUNT(*) AS qty
		FROM  dbo.Rpt_Admission24
		GROUP BY RptYearMonth
		HAVING (RptYearMonth = DATENAME(year, GETDATE()) + '-' + RIGHT('0' + CAST(DATEPART(month, DATEADD(m, - 1, GETDATE())) AS VarChar), 2)))
	BEGIN
		DELETE FROM Rpt_admission24
		WHERE (RptYearMonth = DATENAME(year, GETDATE()) + '-' + RIGHT('0' + CAST(DATEPART(month, DATEADD(m, - 1, GETDATE())) AS VarChar), 2))

		INSERT INTO Rpt_Admission24 ([RptYearMonth]
			  ,[PDSPCL]
			  ,[DoctorName]
			  ,[Month1]
			  ,[Month2]
			  ,[Month3]
			  ,[Month4]
			  ,[Month5]
			  ,[Month6]
			  ,[Month7]
			  ,[Month8]
			  ,[Month9]
			  ,[Month10]
			  ,[Month11]
			  ,[Month12]
			  ,[Month13]
			  ,[Month14]
			  ,[Month15]
			  ,[Month16]
			  ,[Month17]
			  ,[Month18]
			  ,[Month19]
			  ,[Month20]
			  ,[Month21]
			  ,[Month22]
			  ,[Month23]
			  ,[Month24])
		SELECT *
		  FROM [ReportDB].[dbo].[X_TEMP_Rpt_Admission20110302] 
	END
	ELSE
	BEGIN
	  INSERT INTO Rpt_Admission24 ([RptYearMonth]
			  ,[PDSPCL]
			  ,[DoctorName]
			  ,[Month1]
			  ,[Month2]
			  ,[Month3]
			  ,[Month4]
			  ,[Month5]
			  ,[Month6]
			  ,[Month7]
			  ,[Month8]
			  ,[Month9]
			  ,[Month10]
			  ,[Month11]
			  ,[Month12]
			  ,[Month13]
			  ,[Month14]
			  ,[Month15]
			  ,[Month16]
			  ,[Month17]
			  ,[Month18]
			  ,[Month19]
			  ,[Month20]
			  ,[Month21]
			  ,[Month22]
			  ,[Month23]
			  ,[Month24])
		SELECT *
		  FROM [ReportDB].[dbo].[X_TEMP_Rpt_Admission20110302] 
	END
END
GO

Open in new window

epicazo

ASKER

Sorry for da delay. Thank u experts!