Ketema wrote:
i have a record set like below:
num_prods|num_open_issues|num_provisioned|num_canceled
1|0|1|0
2|0|0|2
3|0|1|1 *
2|0|1|1
1|0|0|1
2|0|0|0 *
3|3|0|0
3|0|0|3
3|1|0|2
3|2|0|1
2|0|2|0
Of the list above only row 3 and row 6 should be returned.
Plain english definition:
With a result set like above eliminate all rows that should not show
up on the provision List. Provision List Definition: All rows that
have products that need provisioning. Provisioning means its NOT
canceled and it does NOT have an open issue.
If I understand this correctly, we start with:
where num_cancelled < num_prods and num_open_issues < num_prods
Some facts:
num_open_issues + num_provisioned + num_canceled will never be more
than num_prods.
no individual column will ever be more than num_prods.
Then in addition to this, we also only retrieve records where:
num_open_issues + num_provisioned + num_canceled < num_prods
and
num_open_issues < num_prods (already there above, no need to have it twice)
and
num_provisioned < num_prods
and
num_canceled < num_prods (already there above, no need to have it twice)
giving the query:
select * from table
where num_open_issues < num_prods
and num_provisioned < num_prods
and num_canceled < num_prods
and (num_open_issues + num_provisioned + num_canceled) < num_prods;
With (I think) the result of:
records 1,11 fail as num_provisioned is not < num_prods
records 2,8 fail as num_cancelled is not < num_prods
record 3 passes all constraints
records 4,5,9, 10 fail as num_open_issues + num_provisioned +
num_canceled is not < num_prods
record 6 passes all constraints
record 7 fails as num_open_issues is not < num_prods
Is this what you were after?
Brent Wood
