Tough one to many question

[fredp1]

CR8.5, SQl Server 2k.

I'm trying to put a report together that shows Variances.
There is a Variance Table and Linked to it is a ItemAgreement Table via left outer join on Item
The ItemAgreement has Item and AgreementType in it. A Item can have many agreement types.
Agreement TYpe is also a parameter.
I only want to use the AgreementType table as a 'Filter'. i.e. It need to show Variances for all items that have a specifc agreementtype.

Sample data
Variance table
Key Item date Amount
1 a 01/07/04 10
2 b 01/08/04 12
3 b 01/07/04 8
4 c 01/07/04 9

ItemAgreementTable
Item Agreementtype
a 966
a 006
a 966
b 966
b 006

Senario 1
AgreementTYpe = '*' (Any Item, not limited by the ItemAgreementTable) and date=01/07/04
Results wanted
Key
1
3
4

Senario 2
AgreementType ='966'(Only Items with a Agreement Type of '966') and date=01/07/04
Items to filter on is 'a' and 'b'
Results wanted
key
1
3

I have complete flexability in designing the tables/views/sp in the db.
I am struggling with a few issues.
I do not what to create duplicate records for the variance table as I'll be subtotaling later on in the report when I start to group.
I need to return distict Items from the AgreementType table, but I need to also pass the Agreement TYpe parameter. (otherwise a View with a select distict would have been easy!).
Also if '*' is used for agreementtype, then I do not want the ItemAgreement table to filter the variance data.

Does anyone have any suggestions on how to handle this problem thats also fast?
Is a stored proc the way to go?

Thanks

 

[fredp1]

What I ended up doing is a Stored Proc becase of 2 resons.
1) I realised what I need was one query to do a inner join to the ItemAgreement table and a second query not to link to the ItemAgreement.
Using a parameter on the SP allowed me to achieve this.

2) The secod reason, is passing a parameter to a subquery using a inner join. I couldn't see any way of doing that in 8.5

If anyone has any thoughts/suggestions i'll be glad to hear it.

Thanks for listening.

Fred