SQL proc populates table1 and table2 that have the following insurance policy information:
| table1 |
|
|
|
| policy |
insured name |
policy eff date |
policy exp date |
| AAA111111100 |
Tom G |
01/01/2008 |
06/30/2008 |
| AAA111111101 |
John D |
07/01/2008 |
12/31/2008 |
| AAA111111102 |
Kim T |
01/01/2009 |
06/30/2009 |
| AAA111111103 |
Jesse M |
07/01/2009 |
12/31/2009 |
| BBB222222200 |
Olga S |
01/01/2010 |
06/30/2010 |
| BBB222222201 |
Alex F |
07/01/2010 |
12/31/2010 |
|
|
|
|
| table 2 |
|
|
|
| policy |
premium |
losses |
|
| AAA111111100 |
$120.00 |
$300.00 |
|
| AAA111111101 |
$140.00 |
$0.00 |
|
| AAA111111102 |
$150.00 |
$0.00 |
|
| AAA111111103 |
$135.00 |
$200.00 |
|
| BBB222222200 |
$120.00 |
$0.00 |
|
| BBB222222201 |
$133.00 |
$0.00 |
|
We have the 'main' CR that displays info from table1 and Subreport that displays info from table2. Input parameter is policy number . When we open CR, there will be a prompt 'Enter policy number'. For example, I enter 'AAA111111103'. The main report will display the data for this policy:
| policy |
insured name |
policy eff date |
policy exp date |
| AAA111111103 |
Jesse M |
07/01/2009 |
12/31/2009 |
and the subreport should display the data from table 2 for all policies that have 2 right digits less than the input policy. In this case, it will be policies
| policy |
premium |
losses |
| AAA111111100 |
$120.00 |
$300.00 |
| AAA111111101 |
$140.00 |
$0.00 |
| AAA111111102 |
$150.00 |
$0.00 |
In other words, the subreport should display financial data for 3 previous policies.
I can pass the input parameter ('AAA111111103') to the subreport, but in this case it will show financial information just for
| policy |
premium |
losses |
| AAA111111103 |
$135.00 |
$200.00 |
Any ideas how to display just 3 previous policies?
Thank you,
OlgaS
IP Logged
