CODEWITHSUNDEEP
sqlms-access
Saedawke
Saedawke

Reputation: 471

SQL how to implement if and else by checking column value

The table below contains customer reservations. Customers come and make one record in this table, and the last day this table will be updated its checkout_date field by putting that current time.

The Table Reservations Table

Now I need to extract all customers spending nights.

The Query

SELECT reservations.customerid, reservations.roomno, rooms.rate, 
reservations.checkin_date, reservations.billed_nights, reservations.status,
DateDiff("d",reservations.checkin_date,Date())+Abs(DateDiff("s",#12/30/1899
14:30:0#,Time())>0) AS Due_nights FROM reservations, rooms  WHERE
 reservations.roomno=rooms.roomno;

What I need is, if customer has checkout status, due nights will be calculated checkin_date subtracting by checkout date instead current date, also if customer has checkout date no need to add extra absolute value from 14:30.

My current query view is below, also my computer time is 14:39 so it adds 1 to every query.

current query Views

Upvotes: 1

Views: 63

Answers (1)

PaulFrancis
PaulFrancis

Reputation: 5809

Since you want to calculate the Due nights upto the checkout date, and if they are still checked in use current date. I would suggest you to use an Immediate If.

The condition to check would be the status of the room. If it is checkout, then use the checkout_date, else use the Now(), something like.

SELECT 
    reservations.customerid, 
    reservations.roomno, 
    rooms.rate, 
    reservations.checkin_date, 
    reservations.billed_nights, 
    reservations.status,
    DateDiff("d", checkin_date, IIF(status = 'checkout', checkout_date, Now())) As DueNights
FROM 
    reservations 
    INNER JOIN      
    rooms  
    ON reservations.roomno = rooms.roomno;

As you might have noticed, I used a JOIN. This is more efficient than merging the two tables with common identifier. Hope this helps !

Upvotes: 1

Related Questions