> I'm typing this into SQL from word so I know the issue isnt with the Microsft word inverted commas
Wrong. Your phrase ...
’2013-06-01’ AND ‘2013-06-30’
... has three right-sided single quotes, one left-sided single quote. It needs to be:
'2013-06-01' AND '2013-06-30'.
Forget Microsoft Word as a query preparation tool, indeed forget it as a coding tool. Use a programmer's text editor like NotePad++ or TextPad.
> Also on this I need to calculate total revenue in the given period.
select
transaction_no,
rent_date,
date_returned,
due_date,
code,
overdue_fee,
rental_fee,
sum( datediff(date_returned,due_date) * overdue_fee ) as late_fee,
sum( datediff(date_returned,due_date) * overdue_fee ) as late_fee + rental_fee as total
from rental_details
join price_code using (code)
where date_returned between '2014-06-01' and '2014-06-30' -- NOTE: _NOT_ WORD QUOTES
or rent_date between '2014-06-01’ and '2014-06-30’;
group by transaction_no, rent_date,date_returned, due_date,code,overdue_fee,rental_fee
with rollup;