title: How to join transactional data with customer data tables and perform case-based operations in SQL
body: <p>I'm trying to perform a query between two different tables and come up with a case by case scenario, coming up with a list of records of calls for a specific month.</p>
  
  <p>Here are my tables:</p>
  
  <p><strong>Customer table:</strong></p>
  
  <pre><code>+----+----------------+------------+
  | id |      name      |   number   |
  +----+----------------+------------+
  |  1 | John Doe       | 8973221232 |
  |  2 | American Dad   | 7165531212 |
  |  3 | Michael Clean  | 8884731234 |
  |  4 | Samuel Gatsby  | 9197543321 |
  |  5 | Mike Chat      | 8794029819 |
  +----+----------------+------------+
  </code></pre>
  
  <p><strong>Transaction data:</strong></p>
  
  <pre><code>+----------+------------+------------+----------+---------------------+
  | trans_id |  incoming  |  outgoing  | duration |      date_time      |
  +----------+------------+------------+----------+---------------------+
  |        1 | 8973221232 | 9197543321 |       64 | 2018-03-09 01:08:09 |
  |        2 | 3729920490 | 7651113929 |      276 | 2018-07-20 05:53:10 |
  |        3 | 8884731234 | 8973221232 |      382 | 2018-05-02 13:12:13 |
  |        4 | 8973221232 | 9234759208 |      127 | 2018-07-07 15:32:30 |
  |        5 | 7165531212 | 9197543321 |      852 | 2018-08-02 07:40:23 |
  |        6 | 8884731234 | 9833823023 |      774 | 2018-07-03 14:27:52 |
  |        7 | 8273820928 | 2374987349 |      120 | 2018-07-06 05:27:44 |
  |        8 | 8973221232 | 9197543321 |       79 | 2018-07-30 12:51:55 |
  |        9 | 7165531212 | 7651113929 |      392 | 2018-05-22 02:27:38 |
  |       10 | 5423541524 | 7165531212 |      100 | 2018-07-21 22:12:20 |
  |       11 | 9197543321 | 2983479820 |      377 | 2018-07-20 17:46:36 |
  |       12 | 8973221232 | 7651113929 |      234 | 2018-07-09 03:32:53 |
  |       13 | 7165531212 | 2309483932 |       88 | 2018-07-16 16:22:21 |
  |       14 | 8973221232 | 8884731234 |       90 | 2018-09-03 13:10:00 |
  |       15 | 3820838290 | 2093482348 |      238 | 2018-04-12 21:59:01 |
  +----------+------------+------------+----------+---------------------+
  </code></pre>
  
  <p><strong>What am I trying to accomplish?</strong></p>
  
  <p>I'm trying to compile a list of "costs" for each of the customers that made calls on July 2018. The costs are based on:</p>
  
  <p>1) If the customer received a call (incoming), the cost of the call is equal to the duration;</p>
  
  <p>2) if the customer made a call (outgoing), the cost of the call is 100 if the call is 30 or less in duration. If it exceeds 30 duration, then the cost is 100 plus 5 * duration of the exceeded period.</p>
  
  <p>If the customer didn't make any calls during that month he shouldn't be on the list.</p>
  
  <p><strong>Examples:</strong></p>
  
  <p>1) Customer American Dad has 3 incoming calls and 1 outgoing call, however only trans_id 10 and 13 are for the month of July. He should be paying a total of 538:</p>
  
  <ul>
  <li><p>for trans_id 10 = 450 (100 for the first 30s + 5 * 70 for the remaining)</p></li>
  <li><p>for trans_id 13 = 88</p></li>
  </ul>
  
  <p>2) Customer Samuel Gatsby has 1 incoming call and 3 outgoing calls, however only trans_id 8 and 11 are for the month of July. He should be paying a total of 722:</p>
  
  <ul>
  <li><p>for trans_id 8 = 345 (100 for the first 30s + 5 * 49 for the remaining)</p></li>
  <li><p>for trans_id 11 = 377</p></li>
  </ul>
  
  <p>Considering only these two examples, the output would be:</p>
  
  <pre><code>+----+----------------+------------+------------+
  | id |      name      |   number   |  billable  |
  +----+----------------+------------+------------+
  |  2 | American Dad   | 7165531212 |        538 |
  |  4 | Samuel Gatsby  | 9197543321 |        722 |
  +----+----------------+------------+------------+
  </code></pre>
  
  <p>Note: Mike Chat shouldn't be on the list as he didn't make or receive any calls for that specific month.</p>
  
  <p><strong>What have I tried so far?</strong></p>
  
  <p>I've been playing cat and mouse with this one, I'm using the number as uniqueID, already attempted both a full outer join and combining where incoming or outgoing is not null then applying rules by case, tried doing a left join and applying cases, but I'm circling around and I can't get to a final list. Whenever I get incoming or outgoing, I'm either not able to apply the case or not able to come with both together. Really appreciate the help!</p>
  
  <pre><code>select customer_name.name, customer_name.number, bill = (CASE
                                                           WHEN customer_name.number = transaction_data.incoming then 'sum bill'
                                                           else 'multiply and add'
                                                           end)
  from customer_name
  left join transaction_data on customer_name.number = transaction_data.incoming or customer_name.name = transaction_data.outgoing
  where strftime('%Y-%m', transaction_data.date_time) = '2018-07'
  </code></pre>
  
  <p>Note: I'm using sqlite to try it out online but the database is on SQL Server 2012, so I know that I can use a date format much easier, that way, but I'd like to keep as close to T-SQL as possible.</p>
  
  <p>Also tried creating a case to determine whether it's incoming call or outgoing, but I'm only getting incoming as a result, even though trans_id 10 is outgoing:</p>
  
  <pre><code>select name, number, duration, case 
      when customer_name.number = transaction_data.incoming then 'incoming'
      when customer_name.number = transaction_data.outgoing then 'outgoing'
  END direction
  from customer_name
  left join transaction_data on customer_name.number = transaction_data.incoming or customer_name.name = transaction_data.outgoing
  where strftime('%Y-%m', transaction_data.date_time) = '2018-07'
  </code></pre>