Reg bedrents calculation in HIS stored procedure

Question

Post reply

Reg bedrents calculation in HIS stored procedure

p.avinash689

SSC-Addicted

Points: 435
More actions
March 25, 2014 at 1:58 am

#291277

Hi All,
I have created a stored procedure for calculating the bed resnts in hospital information system.
I have struck with two problems after writing the below stored procedure.
one is the bed rents are calculated wrongly the result was it is taking one day extra charge.
the second one is i need to put 6 hours gross period for discharge ,y because is if the patient was stayed for 25 hours the stored procedure calculates 2 days rent thats why i need to put gross period upto 6 hours. please get me out from this problems.
please find the below stored procedure.
ALTER procedure [dbo].[CALCULATE_BEDRENTS](@patid numeric(10)) as
declare
@prnonumeric(12),@paytypevarchar(10),
@compcodevarchar(10),
@bedrentnumeric(8,2),@consfeenumeric(8,2),
@asconsfeenumeric(8,2),
@tariffcd1varchar(10),@tariffcd2varchar(10),
@tariffcd3varchar(10),
@bednovarchar(5),@startdatedatetime,
@enddatedatetime,
@doccodevarchar(10),
@pbedchgsnumeric(10,2),@pconschgsnumeric(10,2),
@daysnumeric(3),@days1numeric(3),
@hournumeric(4),
@bedtypevarchar(5),@wardcodevarchar(5),
@nextbedidvarchar(5),
@rbedidvarchar(5), @rbedtypevarchar(5),
@rbedrentnumeric(8,2),@rstartdatedatetime,
@renddatedatetime,
@pbedidvarchar(5),
@ipbedid varchar(5),@ipqty numeric(4),
@ipamount numeric(8,2), @iptotamt numeric(9,2),
@COUNTnumeric(3),
@doctcodevarchar(10),@docstartdtdatetime,
@docenddtdatetime,@docdeptcodevarchar(5),
@docdaysnumeric(4),@admitdtdatetime,
@finalbilledvarchar(3)
begin
delete from ip_bed_chgs where ipatientid = @patid and chgclass = 'BD'
select @prno = prno, @paytype = paytype,@compcode = organisationcode, @admitdt = admitdate,@finalbilled = finalbilled from ipadmission_details where ipatientid = @patid
declare beddetails cursor for
select bedid, startdate,enddate from ipbeds_details
where ipatientid = @patid and attender = 'PATIENT' order by startdate
open beddetails
fetch next from beddetails into @bedno,@startdate, @enddate
while @@fetch_status = 0
begin
select @bedtype=bedtype, @wardcode = wardcode from masterbed where bedno = @bedno
if @paytype = 'PAYING'
select @bedrent = bedamt, @consfee = consamt from MASTERBED_CHGSNEW1 where bedtype =@bedtype and tariffcd = 'MGH'
else if @paytype='CMP_CREDIT'
begin
select @tariffcd1 = tariffcd from mastercomptariffs where compcode = @compcode and priority = 1
select @tariffcd2 = tariffcd from mastercomptariffs where compcode = @compcode and priority = 2
select @tariffcd3 = tariffcd from mastercomptariffs where compcode = @compcode and priority = 3
if exists (select bedamt, consamt from MASTERBED_CHGSNEW1 where bedtype =@bedtype and tariffcd = @tariffcd1)
select @bedrent = bedamt, @consfee = consamt, @asconsfee = asconsamt from MASTERBED_CHGSNEW1 where bedtype =@bedtype and tariffcd = @tariffcd1
else if exists (select bedamt, consamt from MASTERBED_CHGSNEW1 where bedtype =@bedtype and tariffcd = @tariffcd2)
select @bedrent = bedamt, @consfee = consamt, @asconsfee = asconsamt from MASTERBED_CHGSNEW1 where bedtype =@bedtype and tariffcd = @tariffcd2
else if exists (select bedamt, consamt from MASTERBED_CHGSNEW1 where bedtype =@bedtype and tariffcd = @tariffcd3)
select @bedrent = bedamt, @consfee = consamt, @asconsfee = asconsamt from MASTERBED_CHGSNEW1 where bedtype =@bedtype and tariffcd = @tariffcd3
else
select @bedrent = bedamt, @consfee = consamt from MASTERBED_CHGSNEW1 where bedtype =@bedtype and tariffcd = 'MGH'
end
SET @DAYS = 0
SET @DAYS1 = 0
if @enddate is not null
----------------------------------end date is not null------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
begin
select @days = datediff(hour,startdate,enddate)/24, @hour = convert(varchar(2),enddate,108) from ipbeds_details where bedid = @bedno and ipatientid = @patid
--select @nextbedid = bedid from ipbeds_details where startdate = @enddate and ipatientid = @patid
SET @DAYS1 = @DAYS
--------------------------------checking the maximum bed in the same date--------------------------------------------------------------------------------------------
select @COUNT = COUNT(*) from ipbeds_details,masterbed,MASTERBED_CHGSNEW1 where bedid = bedno and masterbed.bedtype = MASTERBED_CHGSNEW1.bedtype and tariffcd = 'MGH' and ipatientid = @patid and bedid <> @bedno AND CONVERT(VARCHAR(10),STARTDATE,21) = CONVERT(VARCHAR(10),@enddate,21)
IF @COUNT > 0
BEGIN
declare maxbedtype cursor for
select bedid,STARTDATE, ENDDATE,masterbed.BEDTYPE,bedamt from ipbeds_details,masterbed,MASTERBED_CHGSNEW1 where bedid = bedno and masterbed.bedtype = MASTERBED_CHGSNEW1.bedtype and tariffcd = 'MGH'
and ipatientid = @patid and bedid <> @bedno
AND CONVERT(VARCHAR(10),STARTDATE,21) = CONVERT(VARCHAR(10),@enddate,21) and attender = 'PATIENT'
open maxbedtype
fetch next from maxbedtype into @rbedid, @rstartdate, @renddate,@rbedtype,@rbedrent
while @@fetch_status = 0
begin
SELECT @bedno as BEDNO,@BEDRENT AS BEDRENT
SELECT @RBEDID AS RBEDID,@RBEDRENT AS RBEDRENT
SELECT @DAYS AS DAYS
SELECT @DAYS1 AS DAYS1
if @bedrent > @rbedrent
BEGIN
select @pbedid = bedid from ipbeds_details where ipatientid = @patid and enddate = @rstartdate
select @ipbedid = bedid, @ipqty = qty, @ipamount = amount, @iptotamt = totamt from ip_bed_chgs where bedid = @pbedid and ipatientid = @patid
set @ipqty = isnull(@ipqty,0)
set @ipamount = isnull(@ipamount,0)
set @iptotamt = isnull(@iptotamt,0)
select @ipbedid AS IPBEDID, @ipqty AS IPQTY,@ipamount AS IPAMOUNT, @iptotamt AS IPTOTAMT
if @ipqty > 0
begin
SELECT @RBEDRENT AS RBEDRENT,@IPAMOUNT AS IPAMOUNT
if @rbedrent > @ipamount
begin
set @ipqty = @ipqty - 1
select @ipqty AS IPQTY
if @ipqty > 0
update ip_bed_chgs set totamt = @ipqty*@ipamount
else
delete from ip_bed_chgs where ipatientid = @patid and bedid = @ipbedid
end
end
select @days1 = @days1 + 1
select @days1 AS DAYS1
END
--------------------------------checking the maximum bed in the same date--------------------------------------------------------------------------------------------
select @DAYS1 AS DAYS1
SELECT @prno AS PRNO,@patid AS PATID,@bedno AS BEDNO,@days1 AS DAYS1,@bedrent AS BEDRENT,@days1*@bedrent AS TOTBEDRENT
fetch next from maxbedtype into @rbedid, @rstartdate, @renddate,@rbedtype,@rbedrent
end
close maxbedtype
deallocate maxbedtype
END
--if @days1 >= 0
--begin
----if @finalbilled = 'YES'
----begin
--if @hour >= 2
--set @days1 = @days + 1
----end
--end
SELECT @prno AS PRNO,@patid AS PATID,@bedno AS BEDNO,@days1 AS DAYS1,@bedrent AS BEDRENT,@days1*@bedrent AS TOTBEDRENT
if @days1 > 0
insert into ip_bed_chgs(prno,ipatientid,bedid,bedtype,wardcd,chgclass,qty,amount,totamt)
values(@prno,@patid,@bedno,@bedtype,@wardcode,'BD',@days1,@bedrent,@days1*@bedrent)
end
----------------------------------end date is not null------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
else
----------------------------------end date is null------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
begin
select @days = datediff(HOUR,startdate,getdate())/24, @hour = convert(varchar(2),getdate(),108) from ipbeds_details where bedid = @bedno and ipatientid = @patid
SET @DAYS1 = @DAYS
select @days1
--------------------------------checking the maximum bed in the same date--------------------------------------------------------------------------------------------
select @count = count(*) from ipbeds_details,masterbed,MASTERBED_CHGSNEW1 where bedid = bedno and masterbed.bedtype = MASTERBED_CHGSNEW1.bedtype and tariffcd = 'MGH' and ipatientid = @patid and bedid <> @bedno AND CONVERT(VARCHAR(10),enddate,21) = CONVERT(VARCHAR(10),@startdate,21)
if @count > 0
begin
declare maxbedtype cursor for
select bedid,STARTDATE, ENDDATE,masterbed.BEDTYPE,bedamt from ipbeds_details,masterbed,MASTERBED_CHGSNEW1 where bedid = bedno and masterbed.bedtype = MASTERBED_CHGSNEW1.bedtype and tariffcd = 'MGH'
and ipatientid = @patid and bedid <> @bedno
AND CONVERT(VARCHAR(10),enddate,21) = CONVERT(VARCHAR(10),@startdate,21) and attender = 'PATIENT'
print 'STEP 1'
select @bedno, @patid, @enddate
open maxbedtype
fetch next from maxbedtype into @rbedid, @rstartdate, @renddate,@rbedtype,@rbedrent
select @rbedid, @rstartdate, @renddate,@rbedtype,@rbedrent
while @@fetch_status = 0
begin
SELECT @bedno as BEDNO,@BEDRENT AS BEDRENT
SELECT @RBEDID AS RBEDID,@RBEDRENT AS RBEDRENT
SELECT @DAYS AS DAYS
SELECT @DAYS1 AS DAYS1
if @bedrent > @rbedrent
BEGIN
select @pbedid = bedid from ipbeds_details where ipatientid = @patid and enddate = @rstartdate
select @ipbedid = bedid, @ipqty = qty, @ipamount = amount, @iptotamt = totamt from ip_bed_chgs where bedid = @pbedid and ipatientid = @patid
set @ipqty = isnull(@ipqty,0)
set @ipamount = isnull(@ipamount,0)
set @iptotamt = isnull(@iptotamt,0)
select @ipbedid AS IPBEDID, @ipqty AS IPQTY,@ipamount AS IPAMOUNT, @iptotamt AS IPTOTAMT
if @ipqty > 0
begin
SELECT @RBEDRENT AS RBEDRENT,@IPAMOUNT AS IPAMOUNT
if @rbedrent > @ipamount
begin
set @ipqty = @ipqty - 1
select @ipqty AS IPQTY
if @ipqty > 0
update ip_bed_chgs set totamt = @ipqty*@ipamount
else
delete from ip_bed_chgs where ipatientid = @patid and bedid = @ipbedid
end
end
select @days1 = @days1 + 1
select @days1 AS DAYS1
END
else
begin
select @pbedid = bedid from ipbeds_details where ipatientid = @patid and startdate = @rstartdate
select @ipbedid = bedid, @ipqty = qty, @ipamount = amount, @iptotamt = totamt from ip_bed_chgs where bedid = @pbedid and ipatientid = @patid
set @ipqty = isnull(@ipqty,0)
set @ipamount = isnull(@ipamount,0)
set @iptotamt = isnull(@iptotamt,0)
select @ipbedid AS IPBEDID, @ipqty AS IPQTY,@ipamount AS IPAMOUNT, @iptotamt AS IPTOTAMT
if @ipqty > 0
begin
SELECT @bedrent as bedrent, @RBEDRENT AS RBEDRENT,@IPAMOUNT AS IPAMOUNT
if @bedrent > @ipamount
begin
set @ipqty = @ipqty - 1
select @ipqty AS IPQTY
if @ipqty > 0
update ip_bed_chgs set totamt = @ipqty*@ipamount
else
delete from ip_bed_chgs where ipatientid = @patid and bedid = @ipbedid
end
else
begin
set @days1 = @days1 - 1
end
end
select @days1 AS DAYS1
end
--------------------------------checking the maximum bed in the same date--------------------------------------------------------------------------------------------
select @DAYS1 AS DAYS1
SELECT @prno AS PRNO,@patid AS PATID,@bedno AS BEDNO,@days1 AS DAYS1,@bedrent AS BEDRENT,@days1*@bedrent AS TOTBEDRENT
fetch next from maxbedtype into @rbedid, @rstartdate, @renddate,@rbedtype,@rbedrent
end
close maxbedtype
deallocate maxbedtype
end
select @days1 as days1
select @hour as hour
select @finalbilled as finalbill
if @days1 >= 0
begin
--if @finalbilled = 'YES'
--begin
if @hour >= 2
set @days1 = @days + 1
--end
end
SELECT @DAYS1 AS DAYS1
SELECT @HOUR AS HOUR
IF @DAYS1 > 0
insert into ip_bed_chgs(prno,ipatientid,bedid,bedtype,wardcd,chgclass,qty,amount,totamt) values(@prno,@patid,@bedno,@bedtype,@wardcode,'BD',@days1,@bedrent,@days1*@bedrent)
select @prno,@patid,@bedno,'BD',@days1,@bedrent,@days1*@bedrent
END
----------------------------------end date is null------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--insert into ip_bed_chgs(prno,ipatientid,bedid,chgclass,qty,amount,totamt)
--values(@prno,@patid,@bedno,'BD',@days1,@bedrent,@days1*@bedrent)
--SELECT @prno,@patid,@bedno,@days1,@bedrent,@days1*@bedrent
fetch next from beddetails into @bedno,@startdate, @enddate
end
close beddetails
deallocate beddetails
--SELECT @prno,@patid,@bedno,@days1,@bedrent,@days1*@bedrent
exec calculate_cons @patid
end
--EXEC CALCULATE_BEDRENTS 200303159
SELECT * FROM IP_BED_CHGS WHERE IPATIENTID = 200303159
SELECT * FROM IPBEDS_DETAILS WHERE IPATIENTID = 200303159
and calculate_cons procedure was
ALTER procedure [dbo].[calculate_cons](@patid numeric(10)) as
declare
@bedtypevarchar(5),
@qtynumeric(4),
@tariffcd1varchar(10),
@tariffcd2varchar(10),
@tariffcd3varchar(10),
@compcodevarchar(10),
@consfeenumeric(8,2),
@asconsfeenumeric(8,2),
@prnonumeric(12),
@bedidvarchar(5),
@paytypevarchar(10),
@docfeenumeric(10,2),
@asdocfeenumeric(10,2),
@admitdtdatetime,
@wardcdvarchar(5),
@finalbilledvarchar(3)
begin
delete from ip_bed_chgs where ipatientid = @patid and chgclass in ('CN','AC')
select @prno = prno, @paytype = paytype,@compcode = organisationcode, @admitdt = admitdate,@finalbilled = finalbilled from ipadmission_details where ipatientid = @patid
declare consultations cursor for
select bedid, wardcd, bedtype, qty from ip_bed_chgs where ipatientid = @patid and chgclass = 'BD'
open consultations
fetch next from consultations into @bedid, @wardcd, @bedtype, @qty
while @@fetch_status = 0
begin
if @paytype = 'PAYING'
select @consfee = consamt, @asconsfee = asconsamt from MASTERBED_CHGSNEW1 where bedtype =@bedtype and tariffcd = 'MGH'
else if @paytype='CMP_CREDIT'
begin
select @tariffcd1 = tariffcd from mastercomptariffs where compcode = @compcode and priority = 1
select @tariffcd2 = tariffcd from mastercomptariffs where compcode = @compcode and priority = 2
select @tariffcd3 = tariffcd from mastercomptariffs where compcode = @compcode and priority = 3
if exists (select consamt , asconsamt from MASTERBED_CHGSNEW1 where bedtype =@bedtype and tariffcd = @tariffcd1)
select @consfee = consamt, @asconsfee = asconsamt from MASTERBED_CHGSNEW1 where bedtype =@bedtype and tariffcd = @tariffcd1
else if exists (select consamt , asconsamt from masterbed_chgs where bedtype =@bedtype and tariffcd = @tariffcd2)
select @consfee = consamt, @asconsfee = asconsamt from MASTERBED_CHGSNEW1 where bedtype =@bedtype and tariffcd = @tariffcd2
else if exists (select consamt , asconsamt from masterbed_chgs where bedtype =@bedtype and tariffcd = @tariffcd3)
select @consfee = consamt, @asconsfee = asconsamt from MASTERBED_CHGSNEW1 where bedtype =@bedtype and tariffcd = @tariffcd3
else
select @consfee = consamt , @asconsfee = asconsamt from MASTERBED_CHGSNEW1 where bedtype =@bedtype and tariffcd = 'MGH'
end
set @docfee = 0
set @asdocfee = 0
--select @days = datediff(day,getdate(),@admitdt) + 1
set @docfee = @consfee*@qty
set @asdocfee = @asconsfee * @qty
select @docfee, @asconsfee
insert into ip_bed_chgs(prno, ipatientid, bedid, bedtype, wardcd, chgclass, qty, amount, totamt)
values(@prno, @patid, @bedid, @bedtype, @wardcd, 'CN',@qty, @consfee, @docfee)
insert into ip_bed_chgs(prno, ipatientid, bedid, bedtype, wardcd, chgclass, qty, amount, totamt)
values(@prno, @patid, @bedid, @bedtype, @wardcd, 'AC',@qty, @asconsfee, @asdocfee)
select @prno, @patid, @bedid, @bedtype, @wardcd, 'CN',@qty, @consfee, @docfee
select @prno, @patid, @bedid, @bedtype, @wardcd, 'AC',@qty, @asconsfee, @asdocfee
fetch next from consultations into @bedid, @wardcd, @bedtype, @qty
end
close consultations
deallocate consultations
end
--select * from ip_bed_chgs where ipatientid = 100300073 and chgclass in ('CR','AC','CN')
--delete from ip_bed_chgs where ipatientid = 100300073 and chgclass in ('CR','AC','CN')
--exec calculate_cons 200303109
--select bedid, wardcd, bedtype, qty from ip_bed_chgs where ipatientid = 100300015
Thanks & Regards,
Avinash P

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

Instead of going through the many lines of code. You can achieve by using DATEADD function e.g.

SELECT GETDATE() -- This will give current date

SELECT DATEADD("HOUR", -6,GETDATE()) --This will reduce time by 6 hrs.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

This is the finest example I’ve seen in a long time of how not to code against SQL Server.

Picking out single rows from one table at a time and performing operations on them is absolute beginner coding error #1. Performance will be abysmal and the opportunity for error in the code is shockingly high – as you are well aware. Throw this awful rubbish away and rewrite it as set-based code. It will be easier to test and modify, will run faster, and take far less code.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

p.avinash689 SSC-Addicted Points: 435 More actions · Answer 3

Yup i tried to rewrite this from another way, but i got many errors than this code. and i will check with dateadd function and reply you soon. And i want know more simplie ways to close it pls help me.

Thanks & Regards,

Avinash P