title: How to self join on date calculation and other fields in Redshift
body: <p>Got a query that gets bookings data grouped by date, operator, source, route and I want to self join it so that I have the same data against it but joined on the previous year's date (add 1 day to align the weekday).</p>
  
  <p>For example, if we had</p>
  
  <pre><code>date      |num|
  ----------|---|
  2017-01-01|300|
  2017-01-02|301|
  2017-01-03|302|
  2017-01-04|303|
  2017-01-05|304|
  2017-01-06|305|
  2017-01-07|306|
  2017-01-08|307|
  2017-01-09|308|
  2017-01-10|309|
  ...
  2018-01-01|501|
  2018-01-02|502|
  2018-01-03|503|
  2018-01-04|504|
  2018-01-05|505|
  2018-01-06|506|
  2018-01-07|507|
  2018-01-08|508|
  2018-01-09|509|
  2018-01-10|510|
  ...
  </code></pre>
  
  <p>Then I would want result to be</p>
  
  <pre><code>date      |num| date      |num|
  ----------|---| ----------|---|
  2017-01-01|300| null      |null|
  2017-01-02|301| null      |null|
  2017-01-03|302| null      |null|
  2017-01-04|303| null      |null|
  2017-01-05|304| null      |null|
  2017-01-06|305| null      |null|
  2017-01-07|306| null      |null|
  2017-01-08|307| null      |null|
  2017-01-09|308| null      |null|
  2017-01-10|309| null      |null|
  ...
  2018-01-01|501| 2017-01-02|301|
  2018-01-02|502| 2017-01-03|302|
  2018-01-03|503| 2017-01-04|303|
  2018-01-04|504| 2017-01-05|304|
  2018-01-05|505| 2017-01-06|305|
  2018-01-06|506| 2017-01-07|306|
  2018-01-07|507| 2017-01-08|307|
  2018-01-08|508| 2017-01-09|308|
  2018-01-09|509| 2017-01-10|309|
  2018-01-10|510| 2017-01-11|...|
  </code></pre>
  
  <p>What I've got so far</p>
  
  <pre><code>select
  
  a.date          as date1
  ,a.source       as source1
  ,a.operator     as operator1
  ,a.route        as route1
  ,a.sum          as bookings1
  
  ,b.date         as date2
  ,b.source       as source2
  ,b.operator     as operator2
  ,b.route        as route2
  ,b.sum          as bookings2
  
  from
  
  (select 
  date
  ,source
  ,operator
  ,route
  ,sum(record_count)
  from booking
  where transaction_date &gt;= dateadd(year, -2, current_date)
  group by 1,2,3,4) a
  
  full outer join
  
  (select 
  date
  ,source
  ,operator
  ,route
  ,sum(record_count)
  from booking
  where date between dateadd(year, -3, current_date) and dateadd(year, -1, current_date)
  group by 1,2,3,4) b
  
  on
  
  b.date                          = dateadd(day, 1, dateadd(year, -1, a.date))
  and a.source                    = b.source
  and a.outbound_operator_name    = b.outbound_operator_name
  and a.outbound_route_name       = b.outbound_route_name
  
  ;
  </code></pre>
  
  <p>The problem with the above query is that it show's the correct 2018 results but not the correct 2017 results. I presume there's something wrong with either the join type or something within the on clause e.g. the date calculation attempt...</p>
  
  <p>Any help would be much appreciated</p>