Hello,
I need to cap off a few equations below at certain values:
Code:
SELECT Personal.employeenumber, Company.AnnualSalary as [Salary], personal.dateofbirth AS DOB, FORMAT((#01/24/2016#-[DOB])/365.2, 0) AS Age, FORMAT(ben.annualsalary*2, "Currency") AS [Basic Life], "" as [Basic Life Cost], FORMAT(ben.annualsalary*2, "Currency") AS [Basic AD&D], "" as [Basic AD&D Cost], FORMAT(vb.EVOL, "Currency") AS [Supp Life], format(vb.svol, "Currency") AS [Spouse Life], FORMAT(((vb.dvol*0.039/1000)*12)/26, "Currency") AS [Child Supp Life], FORMAT(((vb.VADD*0.04/1000)*12)/26, "Currency") AS [Supp AD&D], FORMAT(((vb.vadds*0.04/1000)*12)/26, "Currency") AS [Spouse AD&D], format(((vb.vaddc*.04/1000)*12)/26, "Currency") AS [Child AD&D], format(ben.annualsalary/12, "Currency") AS LTD, "" as [LTD Cost], FORMAT(ben.annualsalary/52*0.7, "Currency") AS STD, "" as [STD Cost]
FROM ((Personal
LEFT JOIN Ben ON Personal.Name = Ben.Name)
LEFT JOIN VB ON Personal.name = vb.name)
LEFT JOIN Company ON Personal.name = Company.name
WHERE (personal.homecompanycode = 'EVRB' AND personal.FTPT = 'F' AND personal.employmentstatuscode <> 'T' AND employeetypecode = 'REG')
OR (personal.homecompanycode = 'EBPS' AND personal.FTPT = 'F' AND personal.employmentstatuscode <> 'T' AND employeetypecode = 'REG');
I need to cap Basic Life and Basic AD&D at 350000, LTD at 20833.33, and STD at 2000... but I have no concept of how to do this!
Can you please help?