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

x
?
Solved

SP to find and replace data...

Posted on 2011-03-03
3
Medium Priority
?
274 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:epicazo
[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
3 Comments
 
LVL 23

Accepted Solution

by:
wdosanjos earned 2000 total points
ID: 35032403
Check if this makes sense to you.  Also let me know what should be the input parameters if any.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE YourProcedureName
AS
BEGIN
	SET NOCOUNT ON;

	declare @qty int

	begin transaction

	-----------------------------------------------------------------------------------------------
	-- 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 @qty = COUNT(*)
	FROM  dbo.Rpt_Admission24
	GROUP BY RptYearMonth
	HAVING (RptYearMonth = DATENAME(year, GETDATE()) + '-' + RIGHT('0' + CAST(DATEPART(month, DATEADD(m, - 1, GETDATE())) AS VarChar), 2))

	if @qty = 0 begin
		-----------------------------------------------------------------------------------------------
		-- 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] 
	end else begin
		-----------------------------------------------------------------------------------------------
		-- 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))

		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

	commit
END
GO

Open in new window

0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35032458
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

0
 

Author Closing Comment

by:epicazo
ID: 35061504
Sorry for da delay.   Thank u experts!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

704 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