February 1, 2013 at 9:59 pm
Hi, I have a script from which I have to remove the cursor:
DECLARE @AsOnDate SMALLDATETIME
DECLARE @FlagProduction AS BIT
DECLARE @tblAcAging AS TABLE
(
fLocation VARCHAR(3),
fCategory VARCHAR(15),
fAccount INT,
fName VARCHAR(30),
fOffice VARCHAR(2),
fPhone VARCHAR(14),
fType TINYINT, -- 1 - Private, 2 - Contract , 3 - Insurance, 0 - Unapplied Payments, 4 - Production
fOver180 MONEY,
fOver120 MONEY,
fOver90 MONEY,
fOver60 MONEY,
fOver30 MONEY,
fCurrent MONEY,
fBalance MONEY,
fLastPay MONEY,
fLastDate SMALLDATETIME,
fCheck VARCHAR(10),
fTotal MONEY,
fFlag VARCHAR(1), --* means sent for collection
fCredit MONEY
)
SET @FlagProduction = 1
SET @AsOnDate = CONVERT(SMALLDATETIME,'2012/NOV/30')
IF @FlagProduction = 1
BEGIN
DECLARE @mFlocation VARCHAR(3)
DECLARE @mFaccount INT
DECLARE @mFPatient INT
DECLARE @mFcategory VARCHAR(25)
DECLARE @mFtxnumber INT
DECLARE @mFtxsuffix TINYINT
DECLARE @mFtxdate SMALLDATETIME
DECLARE @mForthogen TINYINT
DECLARE @mfDelete TINYINT
DECLARE @mePvt MONEY
DECLARE @meIns MONEY
DECLARE @mePayPvt MONEY
DECLARE @mePvtCR MONEY
DECLARE @mePvtDB MONEY
DECLARE @mePayIns MONEY
DECLARE @meInsCR MONEY
DECLARE @meInsDB MONEY
DECLARE @meOff VARCHAR(1)
DECLARE @meLast VARCHAR(25)
DECLARE @meFirst VARCHAR(25)
DECLARE @mePhone VARCHAR(25)
DECLARE @meColl BIT
DECLARE TempCursor CURSOR FOR
SELECT
Account.Flocation,
Account.Faccount,
TxPayPlan.FPatient,
TxPayPlan.Fcategory,
TxPayPlan.Ftxnumber,
TxPayPlan.Ftxsuffix,
TxPayPlan.Ftxdate,
Txpayplan.Forthogen,
TxPayPlan.fDelete,
ePvt = Case When (TxPayPlan.fContractNo = 0) Then Fpvtdue Else 0 End,
eIns = TxPayPlan.Finsdue,
ePayPvt = Sum(Case When ((Payment.fPayType <=50)
AND (Payment.finsurance <> 1)
AND (PayDtl.fContractNo = 0)
AND (Txpayplan.Finscode = PayDtl.FinsKode)
AND (TxPayPlan.FPatient = PayDtl.FPatient)
AND (PayDtl.FDelete <> 1) And Payment.fDelete <> 1
--AND (@rOrthoGen IN (Payment.fOrthoGen,10))
AND (Payment.fpaydate <= @AsOnDate ))
Then
PayDtl.fAmount
Else
0
End),
ePvtCR = Sum(Case When ((Payment.fpaytype > 50)
AND (Payment.fpaytype <= 60)
AND (Payment.finsurance <> 1)
AND (PayDtl.fContractNo = 0)
AND (Txpayplan.Finscode = PayDtl.FinsKode)
AND (TxPAyPlan.FPatient = PayDtl.FPatient)
AND (PayDtl.FDelete <> 1) And Payment.fDelete <> 1
--AND (@rOrthoGen IN (Payment.fOrthoGen,10))
AND (Payment.fpaydate <= @AsOnDate ))
Then
PayDtl.fAmount
Else
0
End),
ePvtDB = Sum(Case When ((Payment.fpaytype > 60)
AND (Payment.finsurance <>1)
AND (PayDtl.fContractNo = 0)
AND (Txpayplan.Finscode = PayDtl.FinsKode)
AND (TxPAyPlan.FPatient = PayDtl.FPatient)
AND (PayDtl.FDelete <> 1) And Payment.fDelete <> 1
--AND (@rOrthoGen IN (Payment.fOrthoGen,10))
AND (Payment.fpaydate <= @AsOnDate ))
Then
PayDtl.fAmount
Else
0
End),
ePayIns = Sum(Case When ((payment.fpaytype <= 50)
AND (Payment.finsurance = 1)
AND (Txpayplan.Finscode = PayDtl.FinsKode)
AND (TxPAyPlan.FPatient = PayDtl.FPatient)
AND (PayDtl.FDelete <> 1) And Payment.fDelete <> 1
--AND (@rOrthoGen IN (Payment.fOrthoGen,10))
AND (Payment.fpaydate <= @AsOnDate ))
Then
PayDtl.fAmount
Else
0
End),
eInsCR = Sum(Case When ((payment.fpaytype > 50)
AND (Payment.fpaytype <= 60)
AND (Payment.finsurance = 1)
AND (Txpayplan.Finscode = PayDtl.FinsKode)
AND (TxPAyPlan.FPatient = PayDtl.FPatient)
AND (PayDtl.FDelete <> 1) And Payment.fDelete <> 1
--AND (@rOrthoGen IN (Payment.fOrthoGen,10))
AND (Payment.fpaydate <= @AsOnDate ))
Then
PayDtl.fAmount
Else
0
End),
eInsDB = Sum(Case When ((Payment.fpaytype > 60)
AND (Payment.finsurance = 1)
AND (Txpayplan.Finscode = PayDtl.FinsKode)
AND (TxPAyPlan.FPatient = PayDtl.FPatient)
AND (PayDtl.FDelete <> 1) And Payment.fDelete <> 1
--AND (@rOrthoGen IN (Payment.fOrthoGen,10))
AND (Payment.fpaydate <= @AsOnDate ))
Then
PayDtl.fAmount
Else
0
End),
eOff = TxPayPlan.Foffice,
eLast = Account.Flastname,
eFirst = Account.Ffirstname,
ePhone = Account.Fhomephone,
eColl = Account.fCollSend
FROM
(
(Account
INNER JOIN TxPayPlan
ON(Account.Flocation = TxPayPlan.Flocation) AND
(Account.Faccount = TxPayPlan.Faccount)
)
LEFT JOIN PayDtl
ON(TxPayPlan.Flocation = PayDtl.fLocation) AND
(TxPayPlan.Faccount = PayDtl.fAccount) AND
(TxPayPlan.Ftxnumber = PayDtl.fTxNumber)
)
LEFT JOIN Payment
ON(PayDtl.fLocation = Payment.fLocation) AND
(PayDtl.fPayBatchNo = Payment.fPayBatchNo)
WHERE
--TxPayPlan.flocation = @rLocation AND
--@rOffice IN (TxPayPlan.foffice,'')
--AND @rCategory IN (TxPayPlan.fcategory,'')
--AND @rOrthoGen IN (TxPayPlan.fOrthoGen,10)
--AND
TxPayPlan.fDelete <> 1
GROUP BY
Account.Flocation,
Account.Faccount,
TxPayPlan.FPatient,
TxPayPlan.Fcategory,
TxPayPlan.Ftxnumber,
TxPayPlan.Ftxsuffix,
TxPayPlan.Ftxdate,
TxPayPlan.Forthogen,
TxPayPlan.fDelete,
TxPayPlan.fContractNo,
TxPayPlan.fInsdue,
TxPayPlan.fPvtDue,
TxPayPlan.fOffice,
Account.fLastName,
Account.fFirstName,
Account.fHomePhone,
Account.fCollSend
ORDER BY
Account.Faccount;
OPEN TempCursor
FETCH NEXT FROM TempCursor INTO @mFlocation,@mFaccount,@mFPatient,@mFcategory,@mFtxnumber,@mFtxsuffix,@mFtxdate,@mForthogen,@mfDelete,@mePvt,@meIns,@mePayPvt,@mePvtCR,@mePvtDB,@mePayIns,@meInsCR,@meInsDB,@meOff,@meLast,@meFirst,@mePhone,@meColl ;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @PrivateAmount AS MONEY
DECLARE @Insuranceamount AS MONEY
DECLARE @Days INT
SET @PrivateAmount = (ISNULL(@mePvt,0) + ISNULL(@mepvtdb,0)) - (ISNULL(@mepaypvt,0) + ISNULL(@mepvtcr,0))
SET @Insuranceamount = (ISNULL(@meins,0) + ISNULL(@meinsDB,0)) - (ISNULL(@mepayins,0) + ISNULL(@meinsCR,0))
IF @PrivateAmount = 0 AND @Insuranceamount = 0 GOTO SkipTxPayPlan
SET @Days = DATEDIFF(d,@mFtxdate, @AsOnDate) +1
IF @Days < 0 GOTO SkipTxPayPlan
IF @PrivateAmount = 0 GOTO NextInsurance
IF NOT EXISTS( SELECT * FROM @tblAcAging WHERE fLocation = @mFlocation AND fAccount = @mFaccount AND fCategory = @mFcategory AND fType = 1 )
BEGIN
INSERT INTO @tblAcAging ( fLocation,fAccount,fCategory,fType, fName, fOffice, fPhone , fOver180, fOver120,fOver90, fOver60, fOver30,fCurrent,fFlag )
VALUES (@mFlocation,@mFaccount, @mFcategory, 1, ISNULL(@meLast,'')+' '+ISNULL(@meFirst,''),ISNULL(@meOff,''),ISNULL(@mePhone,''),0,0,0,0,0,0,CASE WHEN @meColl = 1 THEN '*' ELSE '' END )
END
DECLARE @_Over180 AS MONEY
DECLARE @_Over120 AS MONEY
DECLARE @_Over90 AS MONEY
DECLARE @_Over60 AS MONEY
DECLARE @_Over30 AS MONEY
DECLARE @_Current AS MONEY
SET @_Over180 = 0
SET @_Over120 = 0
SET @_Over90 = 0
SET @_Over60 = 0
SET @_Over30 = 0
SET @_Current = 0
IF @Days >= 180 SET @_Over180 = @PrivateAmount
IF @Days >= 120 AND @Days < 180 SET @_Over120 = @PrivateAmount
IF @Days >= 90 and @Days < 120 SET @_Over90 = @PrivateAmount
IF @Days >= 60 AND @Days < 90 SET @_Over60 = @PrivateAmount
IF @Days >= 30 AND @Days < 60 SET @_Over30 = @PrivateAmount
IF @Days >= 0 AND @Days < 30 SET @_Current = @PrivateAmount
UPDATE @tblAcAging
SET fOver180 = fOver180+ @_Over180 ,
fOver120 = fOver120 + @_Over120 ,
fOver90 = fOver90 + @_Over90 ,
fOver60 = fOver60 + @_Over60 ,
fOver30 = fOver30 + @_Over30 ,
fCurrent = fCurrent + @_Current,
fBalance = fCurrent + @_Current + fOver30 + @_Over30 + fOver60 + @_Over60 + fOver90 + @_Over90 + fOver120 + @_Over120 + fOver180+ @_Over180
WHERE fLocation = @mFlocation AND fAccount = @mFaccount AND fCategory = @mFcategory AND fType = 1;
NextInsurance:
IF @Insuranceamount = 0 GOTO SkipTxPayPlan
IF NOT EXISTS( SELECT * FROM @tblAcAging WHERE fLocation = @mFlocation AND fAccount = @mFaccount AND fCategory = @mFcategory AND fType = 3 )
BEGIN
INSERT INTO @tblAcAging ( fLocation,fAccount,fCategory,fType, fName, fOffice, fPhone , fOver180, fOver120,fOver90, fOver60, fOver30,fCurrent,fFlag )
VALUES (@mFlocation,@mFaccount, @mFcategory, 3, ISNULL(@meLast,'')+' '+ISNULL(@meFirst,''),ISNULL(@meOff,''),ISNULL(@mePhone,''),0,0,0,0,0,0,CASE WHEN @meColl = 1 THEN '*' ELSE '' END )
END
SET @_Over180 = 0
SET @_Over120 = 0
SET @_Over90 = 0
SET @_Over60 = 0
SET @_Over30 = 0
SET @_Current = 0
IF @Days >= 180 SET @_Over180 = @Insuranceamount
IF @Days >= 120 and @Days < 180 SET @_Over120 = @Insuranceamount
IF @Days >= 90 and @Days < 120 SET @_Over90 = @Insuranceamount
IF @Days >= 60 AND @Days < 90 SET @_Over60 = @Insuranceamount
IF @Days >= 30 and @days < 60 SET @_Over30 = @Insuranceamount
IF @Days >= 0 and @Days < 30 SET @_Current = @Insuranceamount
UPDATE @tblAcAging
SET fOver180 = fOver180+ @_Over180 ,
fOver120 = fOver120 + @_Over120 ,
fOver90 = fOver90 + @_Over90 ,
fOver60 = fOver60 + @_Over60 ,
fOver30 = fOver30 + @_Over30 ,
fCurrent = fCurrent + @_Current,
fBalance = fCurrent + @_Current + fOver30 + @_Over30 + fOver60 + @_Over60 + fOver90 + @_Over90 + fOver120 + @_Over120 + fOver180+ @_Over180
WHERE fLocation = @mFlocation AND fAccount = @mFaccount AND fCategory = @mFcategory AND fType = 3;
SkipTxPayPlan:
FETCH NEXT FROM TempCursor INTO @mFlocation,@mFaccount,@mFPatient,@mFcategory,@mFtxnumber,@mFtxsuffix,@mFtxdate,@mForthogen,@mfDelete,@mePvt,@meIns,@mePayPvt,@mePvtCR,@mePvtDB,@mePayIns,@meInsCR,@meInsDB,@meOff,@meLast,@meFirst,@mePhone,@meColl ;
END
CLOSE TempCursor
DEALLOCATE TempCursor
END
SELECT * FROM @tblAcAging t where t.fAccount = 966
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 4, 2013 at 8:13 am
kapil_kk (2/1/2013)
Hi, I have a script from which I have to remove the cursor:
I agree you should get rid of that cursor.
You have been around here long enough to know that you can't just slap up a monster mess of code and expect somebody to do this for you. You didn't provide ANY definitions other than a horribly formatted code snippet that is almost 500 lines long.
Let's start by formatting this into something legible.
DECLARE @AsOnDate SMALLDATETIME
DECLARE @FlagProduction AS BIT
DECLARE @tblAcAging AS TABLE (
fLocation VARCHAR(3)
,fCategory VARCHAR(15)
,fAccount INT
,fName VARCHAR(30)
,fOffice VARCHAR(2)
,fPhone VARCHAR(14)
,fType TINYINT
,-- 1 - Private, 2 - Contract , 3 - Insurance, 0 - Unapplied Payments, 4 - Production
fOver180 MONEY
,fOver120 MONEY
,fOver90 MONEY
,fOver60 MONEY
,fOver30 MONEY
,fCurrent MONEY
,fBalance MONEY
,fLastPay MONEY
,fLastDate SMALLDATETIME
,fCheck VARCHAR(10)
,fTotal MONEY
,fFlag VARCHAR(1)
,--* means sent for collection
fCredit MONEY
)
SET @FlagProduction = 1
SET @AsOnDate = CONVERT(SMALLDATETIME, '2012/NOV/30')
IF @FlagProduction = 1
BEGIN
DECLARE @mFlocation VARCHAR(3)
DECLARE @mFaccount INT
DECLARE @mFPatient INT
DECLARE @mFcategory VARCHAR(25)
DECLARE @mFtxnumber INT
DECLARE @mFtxsuffix TINYINT
DECLARE @mFtxdate SMALLDATETIME
DECLARE @mForthogen TINYINT
DECLARE @mfDelete TINYINT
DECLARE @mePvt MONEY
DECLARE @meIns MONEY
DECLARE @mePayPvt MONEY
DECLARE @mePvtCR MONEY
DECLARE @mePvtDB MONEY
DECLARE @mePayIns MONEY
DECLARE @meInsCR MONEY
DECLARE @meInsDB MONEY
DECLARE @meOff VARCHAR(1)
DECLARE @meLast VARCHAR(25)
DECLARE @meFirst VARCHAR(25)
DECLARE @mePhone VARCHAR(25)
DECLARE @meColl BIT
DECLARE TempCursor CURSOR
FOR
SELECT Account.Flocation
,Account.Faccount
,TxPayPlan.FPatient
,TxPayPlan.Fcategory
,TxPayPlan.Ftxnumber
,TxPayPlan.Ftxsuffix
,TxPayPlan.Ftxdate
,Txpayplan.Forthogen
,TxPayPlan.fDelete
,ePvt = CASE
WHEN (TxPayPlan.fContractNo = 0)
THEN Fpvtdue
ELSE 0
END
,eIns = TxPayPlan.Finsdue
,ePayPvt = Sum(CASE
WHEN (
(Payment.fPayType <= 50)
AND (Payment.finsurance <> 1)
AND (PayDtl.fContractNo = 0)
AND (Txpayplan.Finscode = PayDtl.FinsKode)
AND (TxPayPlan.FPatient = PayDtl.FPatient)
AND (PayDtl.FDelete <> 1)
AND Payment.fDelete <> 1
--AND (@rOrthoGen IN (Payment.fOrthoGen,10))
AND (Payment.fpaydate <= @AsOnDate)
)
THEN PayDtl.fAmount
ELSE 0
END)
,ePvtCR = Sum(CASE
WHEN (
(Payment.fpaytype > 50)
AND (Payment.fpaytype <= 60)
AND (Payment.finsurance <> 1)
AND (PayDtl.fContractNo = 0)
AND (Txpayplan.Finscode = PayDtl.FinsKode)
AND (TxPAyPlan.FPatient = PayDtl.FPatient)
AND (PayDtl.FDelete <> 1)
AND Payment.fDelete <> 1
--AND (@rOrthoGen IN (Payment.fOrthoGen,10))
AND (Payment.fpaydate <= @AsOnDate)
)
THEN PayDtl.fAmount
ELSE 0
END)
,ePvtDB = Sum(CASE
WHEN (
(Payment.fpaytype > 60)
AND (Payment.finsurance <> 1)
AND (PayDtl.fContractNo = 0)
AND (Txpayplan.Finscode = PayDtl.FinsKode)
AND (TxPAyPlan.FPatient = PayDtl.FPatient)
AND (PayDtl.FDelete <> 1)
AND Payment.fDelete <> 1
--AND (@rOrthoGen IN (Payment.fOrthoGen,10))
AND (Payment.fpaydate <= @AsOnDate)
)
THEN PayDtl.fAmount
ELSE 0
END)
,ePayIns = Sum(CASE
WHEN (
(payment.fpaytype <= 50)
AND (Payment.finsurance = 1)
AND (Txpayplan.Finscode = PayDtl.FinsKode)
AND (TxPAyPlan.FPatient = PayDtl.FPatient)
AND (PayDtl.FDelete <> 1)
AND Payment.fDelete <> 1
--AND (@rOrthoGen IN (Payment.fOrthoGen,10))
AND (Payment.fpaydate <= @AsOnDate)
)
THEN PayDtl.fAmount
ELSE 0
END)
,eInsCR = Sum(CASE
WHEN (
(payment.fpaytype > 50)
AND (Payment.fpaytype <= 60)
AND (Payment.finsurance = 1)
AND (Txpayplan.Finscode = PayDtl.FinsKode)
AND (TxPAyPlan.FPatient = PayDtl.FPatient)
AND (PayDtl.FDelete <> 1)
AND Payment.fDelete <> 1
--AND (@rOrthoGen IN (Payment.fOrthoGen,10))
AND (Payment.fpaydate <= @AsOnDate)
)
THEN PayDtl.fAmount
ELSE 0
END)
,eInsDB = Sum(CASE
WHEN (
(Payment.fpaytype > 60)
AND (Payment.finsurance = 1)
AND (Txpayplan.Finscode = PayDtl.FinsKode)
AND (TxPAyPlan.FPatient = PayDtl.FPatient)
AND (PayDtl.FDelete <> 1)
AND Payment.fDelete <> 1
--AND (@rOrthoGen IN (Payment.fOrthoGen,10))
AND (Payment.fpaydate <= @AsOnDate)
)
THEN PayDtl.fAmount
ELSE 0
END)
,eOff = TxPayPlan.Foffice
,eLast = Account.Flastname
,eFirst = Account.Ffirstname
,ePhone = Account.Fhomephone
,eColl = Account.fCollSend
FROM (
(
Account INNER JOIN TxPayPlan ON (Account.Flocation = TxPayPlan.Flocation)
AND (Account.Faccount = TxPayPlan.Faccount)
) LEFT JOIN PayDtl ON (TxPayPlan.Flocation = PayDtl.fLocation)
AND (TxPayPlan.Faccount = PayDtl.fAccount)
AND (TxPayPlan.Ftxnumber = PayDtl.fTxNumber)
)
LEFT JOIN Payment ON (PayDtl.fLocation = Payment.fLocation)
AND (PayDtl.fPayBatchNo = Payment.fPayBatchNo)
WHERE
--TxPayPlan.flocation = @rLocation AND
--@rOffice IN (TxPayPlan.foffice,'')
--AND @rCategory IN (TxPayPlan.fcategory,'')
--AND @rOrthoGen IN (TxPayPlan.fOrthoGen,10)
--AND
TxPayPlan.fDelete <> 1
GROUP BY Account.Flocation
,Account.Faccount
,TxPayPlan.FPatient
,TxPayPlan.Fcategory
,TxPayPlan.Ftxnumber
,TxPayPlan.Ftxsuffix
,TxPayPlan.Ftxdate
,TxPayPlan.Forthogen
,TxPayPlan.fDelete
,TxPayPlan.fContractNo
,TxPayPlan.fInsdue
,TxPayPlan.fPvtDue
,TxPayPlan.fOffice
,Account.fLastName
,Account.fFirstName
,Account.fHomePhone
,Account.fCollSend
ORDER BY Account.Faccount;
OPEN TempCursor
FETCH NEXT
FROM TempCursor
INTO @mFlocation
,@mFaccount
,@mFPatient
,@mFcategory
,@mFtxnumber
,@mFtxsuffix
,@mFtxdate
,@mForthogen
,@mfDelete
,@mePvt
,@meIns
,@mePayPvt
,@mePvtCR
,@mePvtDB
,@mePayIns
,@meInsCR
,@meInsDB
,@meOff
,@meLast
,@meFirst
,@mePhone
,@meColl;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @PrivateAmount AS MONEY
DECLARE @Insuranceamount AS MONEY
DECLARE @Days INT
SET @PrivateAmount = (ISNULL(@mePvt, 0) + ISNULL(@mepvtdb, 0)) - (ISNULL(@mepaypvt, 0) + ISNULL(@mepvtcr, 0))
SET @Insuranceamount = (ISNULL(@meins, 0) + ISNULL(@meinsDB, 0)) - (ISNULL(@mepayins, 0) + ISNULL(@meinsCR, 0))
IF @PrivateAmount = 0
AND @Insuranceamount = 0
GOTO SkipTxPayPlan
SET @Days = DATEDIFF(d, @mFtxdate, @AsOnDate) + 1
IF @Days < 0
GOTO SkipTxPayPlan
IF @PrivateAmount = 0
GOTO NextInsurance
IF NOT EXISTS (
SELECT *
FROM @tblAcAging
WHERE fLocation = @mFlocation
AND fAccount = @mFaccount
AND fCategory = @mFcategory
AND fType = 1
)
BEGIN
INSERT INTO @tblAcAging (
fLocation
,fAccount
,fCategory
,fType
,fName
,fOffice
,fPhone
,fOver180
,fOver120
,fOver90
,fOver60
,fOver30
,fCurrent
,fFlag
)
VALUES (
@mFlocation
,@mFaccount
,@mFcategory
,1
,ISNULL(@meLast, '') + ' ' + ISNULL(@meFirst, '')
,ISNULL(@meOff, '')
,ISNULL(@mePhone, '')
,0
,0
,0
,0
,0
,0
,CASE
WHEN @meColl = 1
THEN '*'
ELSE ''
END
)
END
DECLARE @_Over180 AS MONEY
DECLARE @_Over120 AS MONEY
DECLARE @_Over90 AS MONEY
DECLARE @_Over60 AS MONEY
DECLARE @_Over30 AS MONEY
DECLARE @_Current AS MONEY
SET @_Over180 = 0
SET @_Over120 = 0
SET @_Over90 = 0
SET @_Over60 = 0
SET @_Over30 = 0
SET @_Current = 0
IF @Days >= 180
SET @_Over180 = @PrivateAmount
IF @Days >= 120
AND @Days < 180
SET @_Over120 = @PrivateAmount
IF @Days >= 90
AND @Days < 120
SET @_Over90 = @PrivateAmount
IF @Days >= 60
AND @Days < 90
SET @_Over60 = @PrivateAmount
IF @Days >= 30
AND @Days < 60
SET @_Over30 = @PrivateAmount
IF @Days >= 0
AND @Days < 30
SET @_Current = @PrivateAmount
UPDATE @tblAcAging
SET fOver180 = fOver180 + @_Over180
,fOver120 = fOver120 + @_Over120
,fOver90 = fOver90 + @_Over90
,fOver60 = fOver60 + @_Over60
,fOver30 = fOver30 + @_Over30
,fCurrent = fCurrent + @_Current
,fBalance = fCurrent + @_Current + fOver30 + @_Over30 + fOver60 + @_Over60 + fOver90 + @_Over90 + fOver120 + @_Over120 + fOver180 + @_Over180
WHERE fLocation = @mFlocation
AND fAccount = @mFaccount
AND fCategory = @mFcategory
AND fType = 1;
NextInsurance:
IF @Insuranceamount = 0
GOTO SkipTxPayPlan
IF NOT EXISTS (
SELECT *
FROM @tblAcAging
WHERE fLocation = @mFlocation
AND fAccount = @mFaccount
AND fCategory = @mFcategory
AND fType = 3
)
BEGIN
INSERT INTO @tblAcAging (
fLocation
,fAccount
,fCategory
,fType
,fName
,fOffice
,fPhone
,fOver180
,fOver120
,fOver90
,fOver60
,fOver30
,fCurrent
,fFlag
)
VALUES (
@mFlocation
,@mFaccount
,@mFcategory
,3
,ISNULL(@meLast, '') + ' ' + ISNULL(@meFirst, '')
,ISNULL(@meOff, '')
,ISNULL(@mePhone, '')
,0
,0
,0
,0
,0
,0
,CASE
WHEN @meColl = 1
THEN '*'
ELSE ''
END
)
END
SET @_Over180 = 0
SET @_Over120 = 0
SET @_Over90 = 0
SET @_Over60 = 0
SET @_Over30 = 0
SET @_Current = 0
IF @Days >= 180
SET @_Over180 = @Insuranceamount
IF @Days >= 120
AND @Days < 180
SET @_Over120 = @Insuranceamount
IF @Days >= 90
AND @Days < 120
SET @_Over90 = @Insuranceamount
IF @Days >= 60
AND @Days < 90
SET @_Over60 = @Insuranceamount
IF @Days >= 30
AND @days < 60
SET @_Over30 = @Insuranceamount
IF @Days >= 0
AND @Days < 30
SET @_Current = @Insuranceamount
UPDATE @tblAcAging
SET fOver180 = fOver180 + @_Over180
,fOver120 = fOver120 + @_Over120
,fOver90 = fOver90 + @_Over90
,fOver60 = fOver60 + @_Over60
,fOver30 = fOver30 + @_Over30
,fCurrent = fCurrent + @_Current
,fBalance = fCurrent + @_Current + fOver30 + @_Over30 + fOver60 + @_Over60 + fOver90 + @_Over90 + fOver120 + @_Over120 + fOver180 + @_Over180
WHERE fLocation = @mFlocation
AND fAccount = @mFaccount
AND fCategory = @mFcategory
AND fType = 3;
SkipTxPayPlan:
FETCH NEXT
FROM TempCursor
INTO @mFlocation
,@mFaccount
,@mFPatient
,@mFcategory
,@mFtxnumber
,@mFtxsuffix
,@mFtxdate
,@mForthogen
,@mfDelete
,@mePvt
,@meIns
,@mePayPvt
,@mePvtCR
,@mePvtDB
,@mePayIns
,@meInsCR
,@meInsDB
,@meOff
,@meLast
,@meFirst
,@mePhone
,@meColl;
END
CLOSE TempCursor
DEALLOCATE TempCursor
END
SELECT *
FROM @tblAcAging t
WHERE t.fAccount = 966
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply