How to aggregate booking numbers from two different years for comparison in a single table
<p>I'm writing a report that can monitor booking numbers for an event this coming June, but which also compares the booking numbers from the same event held last year, on a day-by-day basis. </p>

<p>The report needs to aggregate the number of bookings by date, it needs to do the same for last year's event but adjust the booking date so it can be compared to this year, and I'm looking for a table that puts these figures side by side in a table.</p>

<p>So,</p>

<p>This code gives me the running booking totals for this year:</p>

<pre><code>--2016 Bookings
SELECT CAST(ep.CREATIONDATE AS DATE) AS 'Booking Date', 
COUNT(ep.CREATIONDATE) as '2016 Saturday Bookings'
FROM EVENTPLACE as ep
LEFT OUTER JOIN EVENTMODULE as em ON em.EVENTMODULENO=ep.EVENTMODULENO
WHERE em.DESCRIPTION = 'Open Day'
AND em.STARTDATE = '2016.11.06'
GROUP BY CAST(ep.CREATIONDATE AS DATE)
</code></pre>

<p>And this next piece of code gives me the running total for last year's event bookings, adjusted by 364 days so that I can compare booking numbers against the comparative same day leading up to the event (accounts for the event being held on 13th June last year, compared to 11th June this year):</p>

<pre><code>--2015 Bookings
SELECT CAST(DATEADD (d,364,ep.CREATIONDATE) AS DATE) AS 'Booking Date',
COUNT(ep.CREATIONDATE) as '2015 Saturday Bookings'
FROM EVENTPLACE as ep
LEFT OUTER JOIN EVENTMODULE as em ON em.EVENTMODULENO=ep.EVENTMODULENO
WHERE em.DESCRIPTION = 'Open Day'
AND em.STARTDATE = '2015.13.06'
GROUP BY CAST(DATEADD (d,364,ep.CREATIONDATE) AS DATE)
</code></pre>

<p>Basically, how do I 'glue' this code together so that I get a table with a 'Booking Date' column on the left with a running list of 'harmonised' 2016 dates, a column with the 2016 booking totals alongside these dates, and a column for the comparative 2015 booking totals also alongside these dates?</p>

<p>Greatly appreciate any pointers.</p>

<p>Thanks
Jon</p>