title: Joining specific days of data to a daily time series (Teradata SQL)
body: <p>I had a hard time summarizing what I am looking to do in the title but the example I have should make sense. I am trying to write a more efficient query in Teradata that will be used in Tableau. I can get it done with the brute strength an ignorance approach but after a while I run out of spool space so I need to make it more efficient.</p>
  
  <p>Let's say I have two tables, a customer table with customer attributes and a daily balance table (it is more complex than that but this is the important part). I want to write a query that returns the daily balance per customer along with other columns that are a specific days balance for that customer regardless of date field in the final table.</p>
  
  <p>Example:</p>
  
  <p>Customer Table</p>
  
  <pre><code>CustID | CustState | CustType | ...
  001    | NY        | A        | ...
  002    | CA        | B        | ...    
  003    | NC        | C        | ...
  </code></pre>
  
  <p>Balance Table</p>
  
  <pre><code>CustID | Date      | Balance
  001    |04/01/2018 | 100
  001    |04/02/2018 | 105
  001    |04/03/2018 | 110
  002    |04/01/2018 | 5000
  002    |04/02/2018 | 15000
  002    |04/03/2018 | 25
  </code></pre>
  
  <p>Final Query Results</p>
  
  <pre><code>CustID | CustState | Date      | Balance | Balance42 | Balance43
  001    | NY        |04/01/2018 | 100     | 105       | 110
  001    | NY        |04/02/2018 | 105     | 105       | 110
  001    | NY        |04/03/2018 | 110     | 105       | 110
  002    | CA        |04/01/2018 | 5000    | 1500      | 25
  002    | CA        |04/02/2018 | 15000   | 1500      | 25
  002    | CA        |04/03/2018 | 25      | 1500      | 25
  </code></pre>
  
  <p>As you can see the first four columns are straight forward, the last two represent the balance from 4/2/2018 and 4/3/2018 respectively. I am currently doing this like the following where I use multiple joins/subqueries to get the specific balances:</p>
  
  <pre><code>select a.CustID
    , a.CustState
    , b.Date
    , sum(b.Balance) as Balance
    , c.Balance as Balance42
    , d.Balance as Balance43
  
  from Customer a
  
  inner join Balance b on a.CustID=b.CustID
  
  inner join (
    select aa.CustID
      , sum(bb.Balance) as Balance
    from Customer aa
    inner join Balance bb on aa.CustID=bb.CustID
    where aa.CustType in ('A','B')
      and bb.Date=DATE '2018-04-02
    group by aa.CustID
  ) c on a.CustID=c.CustID 
  
  inner join (
    select aa.CustID
      , sum(bb.Balance) as Balance
    from Customer aa
    inner join Balance bb on aa.CustID=bb.CustID
    where aa.CustType in ('A','B')
      and bb.Date=DATE '2018-04-03
    group by aa.CustID
  ) d on a.CustID=c.CustID 
  
  where a.CustType in ('A','B')
  
  group by a.CustID
    , a.CustState
    , b.Date
    , c.Balance
    , d.Balance
  </code></pre>
  
  <p>Is there a way that I can do this with only one join/subquery to be more efficient? I start to run out of spool space when I add too many joins/subqueries but I have a specific business use for why I am trying to get the final results structure.</p>