CODEWITHSUNDEEP
mysqlsql
user1753675
user1753675

Reputation: 145

Calculating lost monthly income

I have 3 tables:

Hotel {hotelID, hotelName, city}
Room {hotelID, roomNo, price, type}
Booking {hotelID, roomNo, startDate, endDate}

I want to find the lost income for the current month (i.e. count lost income per each day in the current month and then compute the total)

How do i go about in doing this?

QUICK EDIT: only need to look up income for hotelName = 'Radisson'

Upvotes: 2

Views: 330

Answers (2)

mson
mson

Reputation: 7824

this is the start of your query. you were too lazy to put up data, so i'm too lazy to go lookup mysql form of query...

after looking at per room data - you can consolidate into a single sum if you like. this is how you write queries to make sure you are getting each step correct

select
  ht.hotelid
  ,ht.hotelname
  ,ht.city
  ,rm.roomno
  ,rm.price
  ,rm.type
  ,30 as days -- use your datediff function for month instead of literal 30
  ,30 * rm.price as potential -- use your datediff function instead of literal 30
  ,sum(datediff(day, startdate, enddate)) * rm.price as earned
  --, potential - earned as lost (use the 2 prior columns to calc)      
from hotel ht
join room rm on ht.hotelid = rm.hotelid
left join booking bk on bk.hotelid = rm.hotelid 
   and bk.roomno =bk.roomno

Upvotes: 0

Eggplant
Eggplant

Reputation: 2013

Should work:

SELECT COALESCE(SUM(r.price), 0) 
    FROM Room AS r INNER JOIN Booking AS b ON r.hotelID = b.hotelID AND r.roomNo = b.roomNo 
    INNER JOIN Hotel AS h ON r.hotelID = h.hotelID 
    WHERE (month(b.startDate) > month(current_date()) OR month(b.endDate) < month(current_date()))
    AND h.hotelName = 'Radisson';

Upvotes: 2

Related Questions