MySQL Forums
Forum List  »  Newbie

Re: calculating prices
Posted by: Peter Brawley
Date: December 07, 2014 11:36AM

> 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;

Options: ReplyQuote


Subject
Written By
Posted
December 07, 2014 04:40AM
December 07, 2014 04:41AM
December 07, 2014 06:28AM
Re: calculating prices
December 07, 2014 11:36AM
December 08, 2014 06:42AM
December 07, 2014 11:25AM
December 07, 2014 11:24AM
December 08, 2014 06:51AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.