Stored Procedure for Updating a Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Procedure for Updating a Table

Author

Topic

taj
Starting Member

39 Posts

Posted - 2013-07-10 : 07:37:48

Hi all,

I have a table Patient_Registration with columns Patient_ID,Registration_Date,RENEWAL_DATE_D

After billing This table will get updated every time , when there is any new patient registered or any old patient got renewed there account.

For New patient registration the service code is 1.
and for renewed patient the service code is 293.

CREATE PROCEDURE [dbo].[SP_UPDATE_PATIENT_REGISTRATION_RENEW_DATE]
@PID BIGINT
AS
BEGIN
UPDATE PATIENT_REGISTRATION SET RENEWAL_DATE_D = (SELECT top 1 BD.BILL_DATE_D
FROM BILL_DETAILS BD
JOIN BILL_MASTER BM ON BM.BILL_NUMBER_V = BD.BILL_NUMBER_V
JOIN PATIENT_PROFILE PP ON PP.PATIENT_ID_N = BM.PATIENT_ID_N
WHERE SERVICE_CODE_N = 1
AND BD.BILL_SETTLED_C = 'Y'
AND BM.PATIENT_ID_N=@PID
)
UPDATE PATIENT_REGISTRATION SET RENEWAL_DATE_D = (SELECT top 1 BD.BILL_DATE_D
FROM BILL_DETAILS BD
JOIN BILL_MASTER BM ON BM.BILL_NUMBER_V = BD.BILL_NUMBER_V
JOIN PATIENT_PROFILE PP ON PP.PATIENT_ID_N = BM.PATIENT_ID_N
WHERE SERVICE_CODE_N = 293
AND BD.BILL_SETTLED_C = 'Y'
AND BM.PATIENT_ID_N=@PID
)
where PATIENT_ID_N=@PID
END

Can someone help me in editing the above query and fixing this requirement.

Thank You

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-10 : 08:44:34

Sorry your requirement is not clear.
what are you trying to set as value for the field?
Can you show some sample data and explain?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

taj
Starting Member

39 Posts

Posted - 2013-07-10 : 09:04:24

i have to write a SP,Which updates the PATIENT_REGISTRATION table RENEWAL_DATE_D column with latest BILL_DATE_D column from BILL_DETAILS.

I have to pass 2 main condition in the query.
1)For new patient the service code = 1 and the RENEWAL_DATE_D column should get updated with latest BILL_DATE_D column from BILL_DETAILS.
2)For old patient the service code=293 and the RENEWAL_DATE_D column should get updated with latest BILL_DATE_D column from BILL_DETAILS.

I have posted my complete query what i have written but i'm unable to fix the requirement. Please help me out in fixing this.

Thank You,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-10 : 09:13:10

[code]
CREATE PROCEDURE [dbo].[SP_UPDATE_PATIENT_REGISTRATION_RENEW_DATE]
@PID BIGINT
AS
BEGIN
UPDATE pr
SET RENEWAL_DATE_D = BILL_DATE_D
FROM PATIENT_REGISTRATION pr
CROSS APPLY(SELECT MAX(BD.BILL_DATE_D) AS BILL_DATE_D
FROM BILL_DETAILS BD
JOIN BILL_MASTER BM ON BM.BILL_NUMBER_V = BD.BILL_NUMBER_V
JOIN PATIENT_PROFILE PP ON PP.PATIENT_ID_N = BM.PATIENT_ID_N
WHERE SERVICE_CODE_N = 1
AND BD.BILL_SETTLED_C = 'Y'
AND BM.PATIENT_ID_N= pr.PATIENT_ID_N
) t1
WHERE pr.PATIENT_ID_N = @PID

UPDATE pr
SET RENEWAL_DATE_D =
FROM PATIENT_REGISTRATION pr
CROSS APPLY(SELECT MAX(BD.BILL_DATE_D) AS BILL_DATE_D
FROM BILL_DETAILS BD
JOIN BILL_MASTER BM ON BM.BILL_NUMBER_V = BD.BILL_NUMBER_V
JOIN PATIENT_PROFILE PP ON PP.PATIENT_ID_N = BM.PATIENT_ID_N
WHERE SERVICE_CODE_N = 293
AND BD.BILL_SETTLED_C = 'Y'
AND BM.PATIENT_ID_N = pr.PATIENT_ID_N
) t1
WHERE pr.PATIENT_ID_N = @pID
END
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources