-edit- I've solved my problem by using a union to combine the cost tables into one. The trick was using a CASE statement to deal with the fine table! Thanks for looking!





Hello all,

I am fairly new to SQL. I'm facing a problem that I've identified a few 'messy' solutions to, but I'm sure there must be an elegant one.

I'm an economist, using a database to attempt to work out the cost of a policy that will change the types of sentences people are awarded in the criminal justice system.

I have a table with ~100,000 rows showing projected sentencing outcomes before and after a new policy is introduced. I want to use this information, combined with information on the costs of implementing various sentences, to calculate the cost before and after the implementation of the policy.

Hopefully this image will explain much more clearly my problem:
http://imgur.com/bls8j.jpg

The main bit I am struggling with is: How to deal with the fact that the meaning of the 'parameter' field depends on what's in the 'sentence type' field. (e.g. in the case of custody, it's meaning is 'number of years of custody', in the case of community order it means 'type of community order', and in the case of fines, it is irrelevant)

I'd be really grateful if anyone could help in suggesting the simplest way to write a query to produce the desired result.

Thanks very much in advance,

Robin