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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
BrockAuthor Commented:
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Pawan KumarDatabase ExpertCommented:
can I see the expected output in tabular format. may be in excel. Thanks
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.