SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Stored Procedure for Updating a Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

taj
Starting Member

India
39 Posts

Posted - 07/10/2013 :  07:37:48  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 07/10/2013 :  08:44:34  Show Profile  Reply with Quote
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
Go to Top of Page

taj
Starting Member

India
39 Posts

Posted - 07/10/2013 :  09:04:24  Show Profile  Reply with Quote
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,


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/10/2013 :  09:13:10  Show Profile  Reply with Quote

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 


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000