sql a lookup table

Hi,

Attached you will find a rate table used for benefits.  I need to retrieve the BN_EMPL_RATE value for an individual.   I would like the approach to find the age on this table.  There is a field called : BN_RATE_KEY03. This corresponds to the Age.  

So - for a 52 year old,  this individual would be between BN_RATE_KEY03 = 50 (0.54320) -and BN_RATE_KEY03 = 55(0.73120) WITH BN_RATE_KEY01 (N) -smoking and BN_RATE_KEY01 (F)-female ; The BN_EMPL_RATE VALUE =  (0.54320);

My Attempt: This is how I am trying to get the AGE
AND floor( ( CAST(CONVERT(VARCHAR(8) ,getdate() ,112) AS INT)- CAST(CONVERT(VARCHAR(8) ,EMPDEP2.BIRTHDATE,112) AS INT) ) / 10000 ) BETWEEN (SUBSTRING(BRD.BN_RATE_KEY03,19,2)) AND ( 4 + SUBSTRING(BRD.BN_RATE_KEY03,19,2))

Fails because I am not getting the 30 year olds.


What technique would you use to get the age.  I am attaching the rate table to show you the data.  

Any help would be greatly appreciated,

Lucia
rate_table.JPG
BrockAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
can I see the expected output in tabular format. may be in excel. Thanks
0
 
Pawan KumarDatabase ExpertCommented:
What is the output for the input you have attached.
0
 
BrockAuthor Commented:
Hi Pawan,

Here is the full sql:
SELECT
   H.EMPLID  
 , '  '
 , H.PLAN_TYPE  
 , COST.RATE_TYPE AS RATE  
 , BRD.BN_EMPL_RATE AS PREMIUM_BASE  
 ,H.BENEFIT_PLAN  
 ,NM.FIRST_NAME  
 ,NM.LAST_NAME  
 ,ISNULL(ADDR.ADDRESS1  
 ,'NO ADDRESS1')  
 ,ISNULL(ADDR.ADDRESS2  
 ,'NO ADDRESS2')  
 ,ISNULL(ADDR.CITY  
 ,'NO CITY')  
 ,ISNULL(ADDR.STATE  
 ,'NO STATE')  
 ,ISNULL(ADDR.POSTAL  
 ,'NO STATE')  
 ,NC_Premium_Rate = CASE BRD_TBL.RATE_UNIT WHEN 2 THEN ((H.FLAT_AMOUNT/1000)*BRD.BN_EMPL_RATE) WHEN 3 THEN ((H.FLAT_AMOUNT/100)*BRD.BN_EMPL_RATE) WHEN 1 THEN ((H.FLAT_AMOUNT)*BRD.BN_EMPL_RATE)
ELSE 0 END  
 ,'2017-11-01'  
 ,BNPLN.DESCR  
  FROM PS_LIFE_ADD_BEN H INNER JOIN PS_BEN_DEFN_OPTN N ON N.BENEFIT_PLAN = H.BENEFIT_PLAN INNER JOIN PS_BEN_DEFN_COST COST ON COST.OPTION_ID = N.OPTION_ID INNER JOIN PS_BN_RATE_TYPE TYPE2 ON
COST.RATE_TYPE = TYPE2.RATE_TYPE INNER JOIN PS_BN_RATE_DATA BRD ON BRD.RATE_TBL_ID = COST.RATE_TBL_ID INNER JOIN PS_BN_RATE_TBL BRD_TBL ON BRD_TBL.RATE_TBL_ID = BRD.RATE_TBL_ID INNER JOIN
PS_BEN_PROG_BENPLN BNPLN ON BNPLN.PLAN_TYPE = H.PLAN_TYPE INNER JOIN PS_JOB JOB ON JOB.EMPLID = H.EMPLID INNER JOIN PS_NAMES NM ON H.EMPLID = NM.EMPLID INNER JOIN PS_ADDRESSES ADDR ON ADDR.EMPLID
= H.EMPLID INNER JOIN PS_EMPLOYEES EMP2 ON H.EMPLID = EMP2.EMPLID
WHERE N.BENEFIT_PROGRAM = 'BEN'  
   AND H.PLAN_TYPE = '2C'  
   AND BRD.BN_RATE_KEY02 = 'N'  
   AND H.EMPL_RCD = JOB.EMPL_RCD  
   AND H.PLAN_TYPE = N.PLAN_TYPE  
   AND H.BENEFIT_PLAN = BNPLN.BENEFIT_PLAN  
   AND (BRD_TBL.RATE_TYPE = 1  
   AND H.FLAT_AMOUNT > 0)  
   AND H.COVERAGE_ELECT = 'E'  
   AND N.EFFDT = (  
 SELECT MAX(EFFDT)  
  FROM PS_BEN_DEFN_OPTN N2  
 WHERE N2.BENEFIT_PLAN = N.BENEFIT_PLAN  
   AND N2.EFFDT <= '2017-11-01'  )  
   AND COST.EFFDT = (  
 SELECT MAX(EFFDT)  
  FROM PS_BEN_DEFN_COST COST2  
 WHERE COST2.OPTION_ID = COST.OPTION_ID  
   AND COST2.EFFDT <= '2017-11-01'  )  
   AND BRD.EFFDT = (  
 SELECT MAX(EFFDT)  
  FROM PS_BN_RATE_DATA BRD3  
 WHERE BRD3.RATE_TBL_ID = BRD.RATE_TBL_ID  
   AND BRD3.EFFDT <= '2017-11-01' )  
   AND BRD_TBL.EFFDT = (  
 SELECT MAX(EFFDT)  
  FROM PS_BN_RATE_DATA BRD_TBL3  
 WHERE BRD_TBL.RATE_TBL_ID = BRD_TBL3.RATE_TBL_ID  
   AND BRD_TBL3.EFFDT <= '2017-11-01' )  
   AND JOB.EFFDT = (  
 SELECT MAX( D.EFFDT)  
  FROM PS_JOB D  
 WHERE D.EMPLID = JOB.EMPLID  
   AND D.EMPL_RCD = JOB.EMPL_RCD  

   AND D.EFFDT <= '2017-11-01' )  
   AND JOB.EFFSEQ = (  
 SELECT MAX(E.EFFSEQ)  
  FROM PS_JOB E  
 WHERE E.EMPLID = JOB.EMPLID  
   AND E.EMPL_RCD = JOB.EMPL_RCD  
   AND E.EFFDT = JOB.EFFDT )  
   AND NM.EFFDT = (  
 SELECT MAX(B.EFFDT)  
  FROM PS_NAMES B  
 WHERE B.EMPLID = NM.EMPLID  
   AND B.NAME_TYPE = NM.NAME_TYPE  
   AND B.EFFDT <= GETDATE())  
   AND NM.NAME_TYPE = 'PRI'  
   AND ADDR.EFFDT = (  
 SELECT MAX(F1.EFFDT)  
  FROM PS_ADDRESSES F1 WITH (NOLOCK)  
 WHERE F1.ADDRESS_TYPE = 'HOME'  
   AND F1.EMPLID = ADDR.EMPLID  
   AND F1.EFFDT <='2017-11-01')  
   AND H.COVERAGE_BEGIN_DT = (  
 SELECT MAX(HB.COVERAGE_BEGIN_DT)  
  FROM PS_LIFE_ADD_BEN HB  
 WHERE HB.PLAN_TYPE = H.PLAN_TYPE  
   AND HB.EMPLID = H.EMPLID  
   AND HB.COVERAGE_BEGIN_DT <='2017-11-01' )  
   AND NOT EXISTS (  
 SELECT 'X'  
  FROM PS_PERS_SMOKER SMK  
 WHERE SMK.EMPLID = H.EMPLID)  
   AND ( floor( CAST(CONVERT(VARCHAR(8) ,getdate() ,112) AS INT)- CAST(CONVERT(VARCHAR(8) ,EMP2.BIRTHDATE,112) AS INT) ) / 10000 ) BETWEEN (SUBSTRING(BRD.BN_RATE_KEY03,19,2)) AND ( 4 +
   SUBSTRING(BRD.BN_RATE_KEY03,19,2) )
   AND BRD.BN_RATE_KEY01 = EMP2.SEX
   AND H.EMPLID = '4069560'


Attached is the output;  

Thanks,
Lucia
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
BrockAuthor Commented:
0
 
BrockAuthor Commented:
Hey, Pawan.  Sorry I haven't responded.  Family member in hospital.  

So,  I just want to be able to able to get a case statement going so I capture the right rate for a person:
Rate.PNG
0
 
BrockAuthor Commented:
So I want to capture the Bn_Empl_Rate for the correct age. For instance, if a person is 20 then I want the Bn Empl rate of .1130 assuming he is male and smokes.  Don't worry about the later  2 characteristic as I get them okay.  The problem is that I need to query within the range.  So the person is 31, and I need to grab .19600.  

How do I set that part up in sql:  

Right now I do this :

 AND ( floor( CAST(CONVERT(VARCHAR(8) ,getdate() ,112) AS INT)- CAST(CONVERT(VARCHAR(8) ,EMP2.BIRTHDATE,112) AS INT) ) / 10000 ) BETWEEN (SUBSTRING(BRD.BN_RATE_KEY03,19,2)) AND ( 4 +
    SUBSTRING(BRD.BN_RATE_KEY03,19,2) )

Thanks, Lucy
0
 
BrockAuthor Commented:
Ps.  I may not respond quickly because I am not at work. Thanks, again.
0
 
BrockAuthor Commented:
Pawan,  I will model this in ms access and work on what I am trying to achieve. Thanks for reaching out. Lucy
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.