Results 1 to 1 of 1
  1. #1
    Join Date
    Sep 2010

    Unanswered: 'Conditional joining'

    -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:

    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,

    Last edited by RobinL; 09-13-10 at 17:40.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts