Help required with SP

Question

Help required with SP

Charlottecb

Hall of Fame

Points: 3932
More actions
July 17, 2006 at 4:23 am

#115776

Help required with following SP Please…

I pass as parameters a series of criteria. (intJobID, intSpecies, intAge, intHeight, dtDate, intWidth, intLocality, intDistrict.) Using this I want to return a list of corresponding job rates with the best match first following by the worst match last.

DB Schema

tblJobRate
intRateID (Key)
intJobID (FK)
dtValidFrom (May be blank – so treat as 1900-01-01)
dtValidTo (May be blank – so treat as 2999-12-31)
monRate

tblJobRateParameterGroup
intGroupID (Key)
intRateID (FK)
intPickListHeaderID (FK)

tblJobRateParameter
intParameterID (Key)
intGroupID (FK)
intPickListItemID(FK)

tblPickListItem
intPickListItemID (Key)
intPickListItemType (FK – Type eg size, species, age)
strDescription

Scenario.
Job Rates are created with parameters against them as well as dates (which may be blank in which case they are valid – only invalid if passed in date is outwith range).

I want a list of rates with the best match first.
The parameters may be job id, species, size, height, width, age, locality, district etc. The SP will pass in these as parameters. These may be blank in which case any restrictions of that type may be ignored. Likewise, only some or none of the criteria may be in force – if this is how they have been set up by the user, in which case the rate should be returned.

Without doing a union select on every possible combination, can someone help me please – I am going mad !!!
Many Thanks
CCB.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65142 More actions · Answer 1

Please clarify 'best match' and 'worst match'.

Is best where all parameters match (if so does blank/null param mean best?) and worst where no match. What about in between and/or different combinations match?

Far away is close at hand in the images of elsewhere.
Anon.

Charlottecb Hall of Fame Points: 3932 More actions · Answer 2

Hi,

You are correct with your assumptions. It doesn't matter the order of the matches, only the amount, and null counts as a match.

Thanks

CCB

noeld SSC Guru Points: 96590 More actions · Answer 3

Can you post some sample data... to help us a bit

* Noel

David Burrows SSC Guru Points: 65142 More actions · Answer 4

Also

Where is the table which intPickListItemType (FK) points to?

What does strDescription contain? Is it relevant?

How and what to, do you translate intSpecies, intAge, intHeight, intWidth, intLocality, intDistrict to?

Far away is close at hand in the images of elsewhere.
Anon.