SQL how to implement if and else by checking column value

Saedawke Published at Dev

Saedawke

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

PaulFrancis

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 !

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-03-31

Comments

0 comments

How to assign element from a list to a dataframe column after checking if a column value contains a string that is an element in the list? (Python)

TOP Ranking

Article

SQL how to implement if and else by checking column value

SQL how to implement if and else by checking column value

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

pump.io port in URL

How to import an asset in swift using Bundle.main.path() in a react-native native module

Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)

Compiler error CS0246 (type or namespace not found) on using Ninject in ASP.NET vNext

BigQuery - concatenate ignoring NULL

Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

ggplotly no applicable method for 'plotly_build' applied to an object of class "NULL" if statements

ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3

How to remove the extra space from right in a webview?

Change dd-mm-yyyy date format of dataframe date column to yyyy-mm-dd

Jquery different data trapped from direct mousedown event and simulation via $(this).trigger('mousedown');

maven-jaxb2-plugin cannot generate classes due to two declarations cause a collision in ObjectFactory class

java.lang.NullPointerException: Cannot read the array length because "<local3>" is null

How to use merge windows unallocated space into Ubuntu using GParted?

flutter: dropdown item programmatically unselect problem

Pandas - check if dataframe has negative value in any column

Nuget add packages gives access denied errors

Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

Generate random UUIDv4 with Elm

Client secret not provided in request error with Keycloak