Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Cindy-Atlanta
Starting Member
2 Posts |
 Posted - 2008-04-22 : 17:25:27
|
| Hi,I'm a novice with both Access and SQL, and am trying to write a conditional statement in Access to calculate fees for insurance claims that are based upon the location (the state) and the date the claim was initiated. For example, if the claim occurred in GA and the claim date was between 20040501 and 20060430 multiply the claim payment by .08, if the claim date was between 20020501 and 20040430 multiply the claim payment by .075, etc. The dates aren't formatted as "date" data types - they're numbers, as that's how the data is formatted in the source file I imported to Access.I initially tried this as an IF statement in Excel, and then an "IIF" statement in Access, but couldn't make it work. The criteria is shown below (none of the syntax is correct, but I'm just trying to show the conditional statements):Alabama =IF(Date of Loss>=20070501,0.006,IF(Date of Loss>=20060501<=20070430,0.005,IF(Date of Loss>=20040501<=20060430,0.007,IF(Date of Loss>=20020501<=20040430,0.009,0))))DC =IF(Date of Loss>=20070501,0.111,0)Georgia =IF(Date of Loss>=20060501<=20070430,0.088, IF(Date of Loss>=20040501<20060430,0.116,IF(Date of Loss>=20020501<=20040430,0.081,0)))Kansas =IF(Date of Loss>=20070501<=20080430,0.034, IF(Date of Loss>=20060501<=20070430,0.035,IF(Date of Loss>=20040501<=20060430,0.032,IF(Date of Loss>=20020501<=20040430,0.04,IF(Date of Loss>=20010501<=20020430,0.094,0.029)))))Idaho =IF(Date of Loss>=20040501<=20060430,0.033,IF(Date of Loss>=20060501,0.031,0))Minnesota =IF(Date of Loss>=20010501<=20020430,0.162,IF(Date of Loss>=20020501<=20030430,0.107,0))South Carolina =IF(Date of Loss>=20070501,0.245,IF(Date of Loss>=20060501<=20070430,0.194,IF(Date of Loss>=20040501<=20060430,0.23,IF(Date of Loss>=20020501<=20040430,0.158,0.145))))Wisconsin =IF(Date of Loss>=20070501,0.018,0)Thanks much for any assistance,Cindy |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-04-22 : 17:31:50
|
| Cindy --read this link:http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspxDon't store data like that in your SQL Code -- put it in tables. That's what they are there for! :) You can create a table that lists the states and date ranges and the percentages, and simply join to or SELECT FROM that table to get the results you need. Then maintaining this is just editing data in a table, and your SQL code is extremely short, efficient and easy to maintain. Never stuff data in your code -- store it in tables, where it belongs.- Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-22 : 17:53:31
|
And meanwhile you redesign your tablesSELECT CASE WHEN [Date of Loss] >= '20070501' THEN 0.006 WHEN [Date of Loss] >= '20060501' THEN 0.005 WHEN [Date of Loss] >= '20040501' THEN 0.007 WHEN [Date of Loss] >= '20020501' THEN 0.009 ELSE 0.000 END AS Alabama, CASE WHEN [Date of Loss] >= '20070501' THEN 0.111 ELSE 0.000 END AS DC, CASE WHEN [Date of Loss] >= '20060501' AND [Date of Loss] < '20070501' THEN 0.088 WHEN [Date of Loss] >= '20040501' THEN 0.116 WHEN [Date of Loss] >= '20020501' THEN 0.081 ELSE 0.000 END AS Georgia, CASE WHEN [Date of Loss] >= '20070501' AND [Date of Loss] < '20080501' THEN 0.034 WHEN [Date of Loss] >= '20060501' THEN 0.035 WHEN [Date of Loss] >= '20040501' THEN 0.032 WHEN [Date of Loss] >= '20020501' THEN 0.040 WHEN [Date of Loss] >= '20010501' THEN 0.094 ELSE 0.029 END AS Kansas, CASE WHEN [Date of Loss] >= '20060501' THEN 0.031 WHEN [Date of Loss] >= '20040501' THEN 0.033 ELSE 0.000 END AS Idaho, CASE WHEN [Date of Loss] >= '20020501' AND [Date of Loss] < '20030501' THEN 0.107 WHEN [Date of Loss] >= '20010501' THEN 0.162 ELSE 0.000 END AS Minnesota CASE WHEN [Date of Loss] >= '20070501' THEN 0.245 WHEN [Date of Loss] >= '20060501' THEN 0.194 WHEN [Date of Loss] >= '20040501' THEN 0.230 WHEN [Date of Loss] >= '20020501' THEN 0.158 ELSE 0.145 END AS South Carolina, CASE WHEN [Date of Loss] >= '20070501' THEN 0.018 ELSE 0.000 END AS WisconsinFROM Table1
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Cindy-Atlanta
Starting Member
2 Posts |
Posted - 2008-04-24 : 14:52:45
|
| Thanks so much for your help! I'm on my way..... |
|
|
|
|
|
|
|
|
Select Case