Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Getting wrong amount due to date divider Expand / Collapse
Author
Message
Posted Thursday, January 10, 2013 7:36 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:13 PM
Points: 697, Visits: 1,721
Hi,

Having a hard time figuring this out.

I have a query that is used for benefit deductions. We recently added a new deduction code which is fine but calculating the 'Monthly_ER_AMT' amount incorrectly. I know it's incorrect because it is using the 52 weeks and should be using 24.

Here's is the first query:

select fullname
,empno
,empstatus
,termdate
,coid
,eeid
,org2
,eeddedcode
,deddedtype
,eedbenstartdate
,eedbenstopdate
,case
when deddedtype in ('STD','LTD')
THEN DBO.WSI_F_VEBA_PREPAY_HAWC_STD_LTD_BENAMT
(COID
,EEID
,ORG2
,EEDDEDCODE
,dbo.WSI_F_GET_FIRST_DAY_OF_MONTH
(getdate())
,dbo.WSI_F_GET_LAST_DAY_OF_MONTH
(getdate()))
when deddedtype in ('OPT','OPS','OPC')
THEN DBO.WSI_F_VEBA_PREPAY_HAWC_OPTLIFE_BENAMT
(COID
,EEID
,ORG2
,EEDDEDCODE
,dbo.WSI_F_GET_FIRST_DAY_OF_MONTH
(getdate())
,dbo.WSI_F_GET_LAST_DAY_OF_MONTH
(getdate()))
when deddedtype in ('GTL')
THEN CASE EEDBENAMT
WHEN 0
THEN ISNULL(DEDEEBENAMT,0)
END
end AS BEN_AMT
,dbo.WSI_F_VEBA_PREPAY_HAWC_EE_AMT
(coid
,eeid
,org2
,eeddedcode
,dbo.WSI_F_GET_FIRST_DAY_OF_MONTH
(getdate())
,dbo.WSI_F_GET_LAST_DAY_OF_MONTH
(getdate())) as Monthly_EE_AMT
,dbo.WSI_F_VEBA_PREPAY_HAWC_ER_AMT
(coid
,eeid
,org2
,eeddedcode
,dbo.WSI_F_GET_FIRST_DAY_OF_MONTH
(getdate())
,dbo.WSI_F_GET_LAST_DAY_OF_MONTH
(getdate())) as Monthly_ER_AMT
from wsi_v_personnel_data PD
inner join empded ED
on eeid = eedeeid
inner join dedcode DC
on eeddedcode = dc.deddedcode
INNER JOIN wsi_t_custom_PREMGROUPCODES PG
ON ORG2 = PREMORG2CODE
inner join wsi_t_custom_benefit_rates BR
on (eeddedcode = dedbencode AND
PG.PREMGROUPCODE = BR.PREMGROUPCODE)
where org2 = 'WSIDOJ'
and (eedbenstartdate <= dbo.WSI_F_GET_LAST_DAY_OF_MONTH(getdate())
AND (EEDBENSTOPDATE IS NULL OR EEDBENSTOPDATE >= dbo.WSI_F_GET_FIRST_DAY_OF_MONTH(getdate())))
and (EFFdate <= dbo.WSI_F_GET_LAST_DAY_OF_MONTH(getdate())
AND (EXPDATE IS NULL OR EXPDATE >= dbo.WSI_F_GET_FIRST_DAY_OF_MONTH(getdate())))
AND eeddedcode = 'FDJ10'
ORDER BY FULLNAME, CALC_ORDER, EEDDEDCODE

This is the [WSI_F_VEBA_PREPAY_HAWC_ER_AMT] function that gets the amount:

--THIS FUNCTION WORKS WITH ONE DEDCODE PER PREMGROUPCODE
--IT'S CODED TO HANDLE MULTIPLE DEDCODES PER PREMGROUPCODE, BUT IS UNTESTED AS OF 1PM 02/02/2011 - MH
-- THIS SCENARIO NEEDS TO BE TESTED
SELECT @COID = '7T6C1'
,@EEID = '8DBOAA000080'
,@ORG2 = 'TSIFHU'
,@DEDCODE = 'F9411'
,@STARTDATE = '04/01/2011'
,@STOPDATE = '04/01/2011'

-- GET THE BATCH PERIOD END DATE TO COMPARE AGAINST CUSTOM RATE TABLE EFFDATES
--*/
SELECT @PAY_FREQ = EC.EECPAYPERIOD
,@PAY_FREQ_DIVIDER = CASE EC.EECPAYPERIOD
WHEN 'W' THEN 52
WHEN 'S' THEN 24
WHEN 'B' THEN 26
WHEN 'M' THEN 12
ELSE 26
END
,@ANN_SALARY = EC.EECANNSALARY
,@FT_PT = EC.EECFULLTIMEORPARTTIME
,@REG_TMP = EC.EECEETYPE
,@SAL_HRLY = CASE
WHEN EC.EECSALARYORHOURLY = 'H' AND
((EC.EECUNIONLOCAL IS NULL OR EC.EECUNIONLOCAL = '') AND (EC.EECUNIONNATIONAL IS NULL OR EC.EECUNIONNATIONAL = ''))
THEN 'N'
WHEN EC.EECSALARYORHOURLY = 'H' AND
((EC.EECUNIONLOCAL IS NOT NULL AND EC.EECUNIONLOCAL <> '') OR (EC.EECUNIONNATIONAL IS NOT NULL AND EC.EECUNIONNATIONAL <> ''))
THEN 'U'
WHEN EC.EECSALARYORHOURLY = 'S'
THEN 'S'
WHEN EC.EECSALARYORHOURLY = 'A'
THEN 'A'
END
,@LOC_UNION = EC.EECUNIONLOCAL
,@NAT_UNION = EC.EECUNIONNATIONAL
FROM EMPCOMP EC WITH (NOLOCK)
WHERE EC.EECCOID = @COID
AND EC.EECEEID = @EEID

--USED ONLY TO FIGURE RSICK AMOUNT FOR THOSE THAT HAVE THE RSICK DEDCODE ON THEIR PANEL
SELECT @RSICKAMT = (((@ANN_SALARY/2080)*40)/@PAY_FREQ_DIVIDER)
FROM EMPDED WITH (NOLOCK)
WHERE EEDEEID = @EEID
AND EEDSTARTDATE <= @STOPDATE
AND ((EEDSTOPDATE IS NULL
OR EEDSTOPDATE < '01/01/1999')
OR EEDSTOPDATE >= @STARTDATE)
AND EEDDEDCODE IN (SELECT RSICKDEDCODE
FROM WSI_T_CUSTOM_RSICK_DEDCODES WITH (NOLOCK))

IF @RSICKAMT IS NULL
SELECT @RSICKAMT = 0

SET @RUNNING_TOT = 0
SET @TOT_HW_AMT = 0
SET @REG_HW_AMT = 0

SELECT @REG_HW_AMT = CASE @PAY_FREQ
WHEN 'M'
THEN 160 * R.HW_RATE
WHEN 'B'
THEN 80 * R.HW_RATE
WHEN 'W'
THEN 40 * R.HW_RATE
ELSE 0
END
,@REG_HW_HRS = CASE @PAY_FREQ
WHEN 'M'
THEN 160
WHEN 'B'
THEN 80
WHEN 'W'
THEN 40
ELSE 0
END
,@HW_RATE = R.HW_RATE
FROM WSI_T_CUSTOM_HW_RATES R WITH (NOLOCK)
WHERE R.ORG2CODE = @ORG2
AND (R.JOBCODE = 'ALL'
AND (FT_OR_PT = 'F'
OR FT_OR_PT = 'A')
AND (EE_TYPE = 'R'
OR EE_TYPE = 'A')
AND (SALARYHOURLYUNION = 'H'
OR SALARYHOURLYUNION = 'A'))
GROUP BY R.HOURS_CAP
,R.HW_RATE
SELECT @REG_HW_AMT = ISNULL(@REG_HW_AMT,0)

IF (@REG_HW_AMT - @RSICKAMT) < 0
SELECT @RSICKAMT = 0

SELECT @START_HW_AMT = @REG_HW_AMT - @RSICKAMT
SELECT @TOT_HW_AMT = @START_HW_AMT
--- PREMIUM CALCULATIONS
DECLARE @GROUPED_PREMS TABLE (PRIMARY_KEY INT IDENTITY(1,1) NOT NULL
,PREMGRPCODE VARCHAR( 16)
,DEDCODE VARCHAR( 25)
,CALC_ORDER SMALLINT
,EE_PREM DECIMAL(9,4)
,EEC_PREM DECIMAL(9,4)
,EES_PREM DECIMAL(9,4)
,FAM_PREM DECIMAL(9,4))
INSERT INTO @GROUPED_PREMS
SELECT BR.PREMGROUPCODE
,BR.DEDBENCODE
,BR.CALC_ORDER
,BR.EE_PREM
,BR.EEC_PREM
,BR.EES_PREM
,BR.FAM_PREM
FROM WSI_T_CUSTOM_BENEFIT_RATES BR WITH (NOLOCK)
INNER JOIN WSI_T_CUSTOM_PREMGROUPCODES PGC WITH (NOLOCK)
ON BR.PREMGROUPCODE = PGC.PREMGROUPCODE
WHERE DEDBENCODE IN (SELECT EEDDEDCODE
FROM EMPDED WITH (NOLOCK)
WHERE EEDEEID = @EEID
AND EEDSTARTDATE <= @STOPDATE
AND ((EEDSTOPDATE IS NULL OR EEDSTOPDATE < '01/01/1999') OR EEDSTOPDATE >= @STARTDATE)
-- AND DBTBENOPTION <> 'Z'
)
AND PGC.PREMORG2CODE = @ORG2
AND EFFDATE <= @STOPDATE
AND ((EXPDATE IS NULL OR EXPDATE < '01/01/1999') OR EXPDATE >= @STARTDATE)
ORDER BY CALC_ORDER

--LOOPS THRU THE DEDCODES IN THE BENEFIT RATES TABLE
DECLARE @PG_ROW_COUNTER SMALLINT
, @PG_LOOP_COUNTER SMALLINT
, @INDIV_EE_PREM DECIMAL(9,4)
, @INDIV_EEC_PREM DECIMAL(9,4)
, @INDIV_EES_PREM DECIMAL(9,4)
, @INDIV_FAM_PREM DECIMAL(9,4)
, @STD_CALCED_PREM DECIMAL(9,4)
, @LTD_CALCED_PREM DECIMAL(9,4)
, @OPL_CALCED_PREM DECIMAL(9,4)
SELECT @PG_LOOP_COUNTER = COUNT(*) FROM @GROUPED_PREMS
SELECT @PG_LOOP_COUNTER = ISNULL(@PG_LOOP_COUNTER, 0)
SET @PG_ROW_COUNTER = 1
IF @PG_LOOP_COUNTER = 0
SELECT @ER_AMT = 0
ELSE
BEGIN --PREMGROUP LOOP
WHILE @PG_LOOP_COUNTER > 0 AND @PG_ROW_COUNTER <= @PG_LOOP_COUNTER
BEGIN
--SELECT THE DEDCODE TO CALCULATE
SELECT @TMP_DEDCODE = DEDCODE
,@INDIV_EE_PREM = EE_PREM
,@INDIV_EEC_PREM = EEC_PREM
,@INDIV_EES_PREM = EES_PREM
,@INDIV_FAM_PREM = FAM_PREM
,@PREMCODE = PREMGRPCODE --ONLY NEEDED FOR TROUBLESHOOTING
FROM @GROUPED_PREMS
WHERE PRIMARY_KEY = @PG_ROW_COUNTER
-- GETS DEDTYPE AND BENOPTION FOR DEDCODE THATS LOOPING
SELECT @DEDTYPE = DC.DEDDEDTYPE
,@BENOPTION = ED.EEDBENOPTION
FROM DEDCODE DC WITH (NOLOCK) INNER JOIN EMPDED ED WITH (NOLOCK) ON
(DC.DEDDEDCODE = ED.EEDDEDCODE)
WHERE DC.DEDDEDCODE = @TMP_DEDCODE
AND ED.EEDEEID = @EEID
AND DC.DEDDEDEFFSTARTDATE <= @STOPDATE
AND ((DC.DEDDEDEFFSTOPDATE IS NULL OR DC.DEDDEDEFFSTOPDATE < '01/01/1990')
OR DC.DEDDEDEFFSTOPDATE >= @STARTDATE)
IF @BENOPTION IS NULL
SELECT @BENOPTION = 'EE'
SET @STD_CALCED_PREM = 0
SET @LTD_CALCED_PREM = 0
SET @OPL_CALCED_PREM = 0
IF @DEDTYPE = 'STD'
SELECT @STD_CALCED_PREM = DBO.WSI_F_VEBA_PREPAY_HAWC_STD_LTD
(@COID
,@EEID
,@ORG2
,@TMP_DEDCODE
,@STARTDATE
,@STOPDATE)
ELSE
SELECT @STD_CALCED_PREM = 0
IF @DEDTYPE = 'LTD'
SELECT @LTD_CALCED_PREM = DBO.WSI_F_VEBA_PREPAY_HAWC_STD_LTD
(@COID
,@EEID
,@ORG2
,@TMP_DEDCODE
,@STARTDATE
,@STOPDATE)
ELSE
SELECT @LTD_CALCED_PREM = 0
IF @DEDTYPE LIKE 'OP%'
SELECT @OPL_CALCED_PREM = DBO.WSI_F_VEBA_PREPAY_HAWC_OPTLIFE
(@COID
,@EEID
,@ORG2
,@TMP_DEDCODE
,@STARTDATE
,@STOPDATE)
ELSE
SELECT @OPL_CALCED_PREM = 0
-------------------------------------------------------------------------------------
/*
SELECT 'PRE_CALC VALUES - ROW#: ' + CAST(@PG_ROW_COUNTER AS CHAR(1))
, @PG_LOOP_COUNTER AS PG_LOOP_COUNTER
, @TMP_DEDCODE AS ROW_DEDCODE
, @DEDCODE AS IN_DEDCODE
, DBO.WSI_F_GET_EMP_FULLNAME(@EEID)
AS FULLNAME
, @BENOPTION AS BENOPTION
, @PREMCODE AS PREMCODE
, @PAY_FREQ AS PAYFREQ
, @ANN_SALARY AS ANN_SALARY
, @REG_HW_AMT AS REG_HW_AMT
, @REG_HW_HRS AS REG_HW_HRS
, @HW_RATE AS HW_RATE
, @RSICKAMT AS RSICKAMT
, @INDIV_EE_PREM AS INDIV_EE_PREM
, @INDIV_EES_PREM AS INDIV_EES_PREM
, @INDIV_EEC_PREM AS INDIV_EEC_PREM
, @INDIV_FAM_PREM AS INDIV_FAM_PREM
, @STARTDATE AS STARTDATE
, @STOPDATE AS STOPDATE
, @STD_CALCED_PREM AS STD_PREM
, @LTD_CALCED_PREM AS LTD_PREM
, @OPL_CALCED_PREM AS OPL_PREM

--*/

IF @DEDTYPE = 'STD'
SELECT @PREM_AMT = @STD_CALCED_PREM
ELSE IF @DEDTYPE = 'LTD'
SELECT @PREM_AMT = @LTD_CALCED_PREM
ELSE IF @DEDTYPE LIKE 'OP%'
SELECT @PREM_AMT = @OPL_CALCED_PREM
ELSE
BEGIN
IF @BENOPTION = 'EE'
SELECT @PREM_AMT = @INDIV_EE_PREM
ELSE IF @BENOPTION = 'EEC'
SELECT @PREM_AMT = @INDIV_EEC_PREM
ELSE IF @BENOPTION = 'EES'
SELECT @PREM_AMT = @INDIV_EES_PREM
ELSE IF @BENOPTION = 'FAM'
SELECT @PREM_AMT = @INDIV_FAM_PREM
END
-------------------------------------------------------------------------------------
IF @TOT_HW_AMT <= @PREM_AMT
SELECT @ER_AMT = @TOT_HW_AMT
ELSE
SELECT @ER_AMT = @PREM_AMT

SELECT @RUNNING_TOT = @RUNNING_TOT + @PREM_AMT

IF @BENOPTION = 'Z' --<=====WHEN 'Z', BENEFIT WAS WAIVED
(
SELECT @ER_AMT = 0
,@PREM_AMT = 0
)
IF @PREM_AMT > @TOT_HW_AMT
SELECT @TOT_HW_AMT = 0
ELSE
SELECT @TOT_HW_AMT = @START_HW_AMT - @RUNNING_TOT
/*
SELECT 'POST_CALC VALUES - ROW#: ' + CAST(@PG_ROW_COUNTER AS CHAR(1))
, @TMP_DEDCODE AS TMP_DEDCODE
, @DEDCODE AS IN_DEDCODE
, @HW_RATE AS HW_RATE
, @TOT_HW_AMT AS TOT_HW_AMT
, @BENOPTION AS BENOPTION
, @PREM_AMT AS PREM_AMT
, @ER_AMT AS OUTPUT_ER_AMT
, @RUNNING_TOT AS RUNNING_TOT --HOLDS THE TOTAL HW AMOUNT THRU OUT THE LOOP.
--*/
--ADVANCES THE COUNTER FOR THE @@GROUPED_PREMS TEMP TABLE WHILE LOOP
SELECT @PG_ROW_COUNTER = @PG_ROW_COUNTER + 1
IF @TMP_DEDCODE = @DEDCODE
BEGIN
BREAK
END
END --WHILE LOOP FOR PREM GROUP DEDCODES
END --PREMGROUP LOOPCOUNTER > 0
IF @ER_AMT IS NULL OR @ER_AMT <= 0
SELECT @ER_AMT = 0
IF @ER_AMT <> 0
IF @DEDTYPE NOT LIKE 'OP%'
SELECT @ER_AMT = @ER_AMT * @PAY_FREQ_DIVIDER /12
/*
SELECT @ER_AMT AS ER_AMT
--*/
RETURN @ER_AMT
END ---FUNCTION




For the life of me I can't see how to make it calc the amount to use 24 weeks instead of 52.




Post #1405449
Posted Thursday, January 10, 2013 7:43 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 22,474, Visits: 30,146
Code snippet:


SELECT @PAY_FREQ = EC.EECPAYPERIOD
,@PAY_FREQ_DIVIDER = CASE EC.EECPAYPERIOD
WHEN 'W' THEN 52
WHEN 'S' THEN 24
WHEN 'B' THEN 26
WHEN 'M' THEN 12
ELSE 26
END
,@ANN_SALARY = EC.EECANNSALARY
,@FT_PT = EC.EECFULLTIMEORPARTTIME
,@REG_TMP = EC.EECEETYPE
,@SAL_HRLY = CASE
WHEN EC.EECSALARYORHOURLY = 'H' AND
((EC.EECUNIONLOCAL IS NULL OR EC.EECUNIONLOCAL = '') AND (EC.EECUNIONNATIONAL IS NULL OR EC.EECUNIONNATIONAL = ''))
THEN 'N'
WHEN EC.EECSALARYORHOURLY = 'H' AND
((EC.EECUNIONLOCAL IS NOT NULL AND EC.EECUNIONLOCAL <> '') OR (EC.EECUNIONNATIONAL IS NOT NULL AND EC.EECUNIONNATIONAL <> ''))
THEN 'U'
WHEN EC.EECSALARYORHOURLY = 'S'
THEN 'S'
WHEN EC.EECSALARYORHOURLY = 'A'
THEN 'A'
END
,@LOC_UNION = EC.EECUNIONLOCAL
,@NAT_UNION = EC.EECUNIONNATIONAL
FROM EMPCOMP EC WITH (NOLOCK)
WHERE EC.EECCOID = @COID
AND EC.EECEEID = @EEID


What is the value of this column, EC.EECPAYPERIOD, in EMPCOMP for the parameters @COID and @EEID passed to this function where the calculations are incorrect.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1405460
Posted Thursday, January 10, 2013 9:44 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:13 PM
Points: 697, Visits: 1,721
What is the value of this column, EC.EECPAYPERIOD, in EMPCOMP for the parameters @COID and @EEID passed to this function where the calculations are incorrect.


The value of EC.EECPAYPERIOD is 'B'

which I know calcs at 26

So I tried updating one of the records to 'S' but when I did that it did a strange thing put the value in 'Monthly_EE_AMT'.

The intersting part is that it was the correct amount now when using 'S'- but wrong field..



Post #1405529
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse