Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
RobinL
Starting Member
3 Posts |
 Posted - 2010-09-11 : 10:19:26
|
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:[URL=http://imgur.com/bls8j.jpg] [/URL]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 |
|
|
slimt_slimt
Aged Yak Warrior
746 Posts |
Posted - 2010-09-12 : 03:34:38
|
| Hi,i don't see all the connections among the tables. e.g.: tblChangesinSentencing with tblCustodialCosts. whereas tblFine can be implemented in single case sentence within Select List.let us know if there is any straightforward connections or it should be done based on some rules? |
 |
|
|
RobinL
Starting Member
3 Posts |
Posted - 2010-09-12 : 05:21:59
|
To attempt to clarify: To explain in words:tblChangesInSentences has information about the sentence type 'before' and the sentence type 'after' a policy decision. I want to retrieve the cost information of these sentences, and put all this information together. In the first row of tblChangesInSentencing, the 'before' sentence is shown in yellow. This is a custodial sentence with parameter 1. Since it is a custodial sentence, I want to look up the cost in the tblCustodialCost. A custodial sentence with parameter 1 costs £50,000 (highlighted in yellow in tblCustodialCost). I want this to be shown in the 'cost before' column in the qryResults table, again highlighted in yellow.You can see a similar process going on with the 'after' sentence in the first row, highlighted in blue. |
|
|
|
slimt_slimt
Aged Yak Warrior
746 Posts |
Posted - 2010-09-13 : 00:28:08
|
| i still a bit puzzled. :S |
|
|
|
DuncanP
Starting Member
12 Posts |
Posted - 2010-09-13 : 07:31:28
|
It sounds like you need a CASE statement with subqueries to return the right costs. So something like:SELECT tblChangesinSentencing.*, CASE WHEN tblChangesinSentencing.SentenceTypeBefore = 'Custody' THEN (SELECT Cost FROM tblCustodialCost WHERE tblCustodialCost.YearsCustody = tblChangesinSentencing.OldParameter) WHEN tblChangesinSentencing.SentenceTypeBefore = 'Community order' THEN (SELECT Cost FROM tblCommunityCost WHERE tblCommunityCost.CommunityType = tblChangesinSentencing.OldParameter) WHEN tblChangesinSentencing.SentenceTypeBefore = 'Fine' THEN (SELECT Cost FROM tblFineCost) END AS [Cost Before],... -- Similar CASE statement for Cost After, DifferenceFROM tblChangesinSentencingWHERE ... -- Any conditions I haven't tested it, but it should at least give you a starting point. It has the disadvantage that if you add a new sentence type you need to add another WHEN to the CASE statement, but that's the nature of the table structure.Duncan |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
|
|
RobinL
Starting Member
3 Posts |
Posted - 2010-09-13 : 16:34:49
|
quote:
Originally posted by DuncanP
It sounds like you need a CASE statement with subqueries to return the right costs. So something like:SELECT tblChangesinSentencing.*, CASE WHEN tblChangesinSentencing.SentenceTypeBefore = 'Custody' THEN (SELECT Cost FROM tblCustodialCost WHERE tblCustodialCost.YearsCustody = tblChangesinSentencing.OldParameter) WHEN tblChangesinSentencing.SentenceTypeBefore = 'Community order' THEN (SELECT Cost FROM tblCommunityCost WHERE tblCommunityCost.CommunityType = tblChangesinSentencing.OldParameter) WHEN tblChangesinSentencing.SentenceTypeBefore = 'Fine' THEN (SELECT Cost FROM tblFineCost) END AS [Cost Before],... -- Similar CASE statement for Cost After, DifferenceFROM tblChangesinSentencingWHERE ... -- Any conditions I haven't tested it, but it should at least give you a starting point. It has the disadvantage that if you add a new sentence type you need to add another WHEN to the CASE statement, but that's the nature of the table structure.Duncan
Thanks very much - I've used a variant on this idea to make it work.The way I did it was creating a combined 'cost of sentences' table, combining 'tblCustodialCost', 'tblCommunityCost' and 'tblFine'. I needed the case statement to deal with the fact the fine table was different to the others.Within this new table I created a unique identifier, which i could then join to the 'tblechangesinsentencing' table.Lamprey: The reason the data structure is messed up is because i'm trying to use a database for a purpose completely different to what it was designed for! Presumably to create a decent structure you'd recommend having all the cost data in a single table to begin with. I'm not so sure what a sensible structure for tlbChangesinSentencing is (should it be split into two tables?) |
|
|
|
|
|
|
|
|
'Conditional joining'