title: Selecting a value from a discrete probability distribution if another column = 1?
body: <p>I am for all intents and purposes a complete newbie when it comes to SQL and I am attempting to perform a certain task but I have no idea how to go about it. </p>
  
  <p>I have a table (lets call it <strong>A</strong>) where I have a column 'Claims Made', which has a binary variable (0/1) and a corresponding column 'Claim Amount' column. If 'Claims Made' is 1, I wished to pull a value for 'Claim Amount' from another table (lets call it <strong>B</strong>) where one has determined a discrete probability distribution for said claim amount (where the probability is in column x, and the amount in column y). I wished to pull the values in such a way that the distribution of the values in 'Claim Amount' in <strong>A</strong>, matches with the probabilities given in table <strong>B</strong>. </p>
  
  <p>My apologies for not providing any pseudo code but I have no idea how to go about this task. </p>
  
  <p>In essence for <strong>A</strong>  I have the columns:</p>
  
  <pre><code>+-------------+-------+--------+
  | Observation | Claim | Amount |
  +-------------+-------+--------+
  | 1           | 1     |        |
  | 2           | 1     |        |
  | 3           | 0     |        |
  | 4           | 0     |        |
  | ...         | ...   |        |
  | 99          | 1     |        |
  </code></pre>
  
  <p>and for <strong>B</strong> I have the columns:</p>
  
  <pre><code>+---------------------+
  | | Prob  | Amount |  |
  +---------------------+
  | |0.2407 | 2750   |  |
  | |0.1543 | 1250   |  |
  | |0.1296 | 3250   |  |
  | |0.1420 |12500   |  |
  | |0.0494 | 3250   |  |
  | |0.0309 | 72500  |  |
  | |0.1358 | 2250   |  |
  | |0.0247 | 4500   |  |
  | |0.0617 | 5500   |  |
  | |0.0309 | 1750   |  |
  +---------------------+
  </code></pre>
  
  <p>Ideally the distribution of values in <strong>A</strong> should match the probabilities in <strong>B</strong>. I.e. for 10000 observations (where claim = 1), 2407 cells for Amount should be 2750. So in <strong>A</strong> I should have:</p>
  
  <pre><code>+-------------+-------+--------+
  | Observation | Claim | Amount |
  +-------------+-------+--------+
  | 1           | 1     |2750    |
  | 2           | 1     |1250    |
  | 3           | 0     |0       |
  | 4           | 0     |        |
  | ...         | ...   |        |
  | 99          | 1     |3250    |
  </code></pre>
  
  <p>for example.</p>
  
  <p>I am using SQLserver if that helps at all.</p>
  
  <p>Many Thanks.</p>