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?
Olga
IP Logged
