SQL max and null

Question

I am trying to get the null value in the USAGE_START_DATE column.

So far, what is i got is the unique max records of those with value in the USAGE START DATE column, and could not find any the records with nulls in the USAGE START DATE.

I already tried ISNULL, COALESCE, NVL, NZ.

I got 2 tables: linked by Reservation_id.

ReservationID   Usage Start Date
1                01/01/2001 00:00:00
1                02/01/2001 00:00:00
1                03/03/2001 00:00:00
2                NULL
2                NULL

So far with my current code: Here is what I got:

ReservationID    Usage Start DAte
1                03/03/2001 00:00:00

Here is what I want:

ReservationID     Usage Start Date
1                 03/03/2001 00:00:00
2

Thank you!!!

To simplify my code:

SELECT distinct RENTAL_DETAILS_VW.RESERVATION_ID,  
RENTAL_DETAILS_VW.USAGE_START_DATE,
FROM BYNXFLEET_BI.RENTAL_DETAILS_VW, WILLOW2K.RESERVATIONS  
WHERE RENTAL_DETAILS_VW.USAGE_START_DATE = (
select max(case when ors2.USAGE_START_DATE is null 
    then {ts ' 2009-01-01 00:00:00 ' } 
    else ors2.USAGE_START_DATE END) 
FROM RENTAL_DETAILS_VW ors2 
where ors2.RESERVATION_ID=RESERVATIONS.RESERVATION_ID)

Answer 1

You're not going to get any rows where USAGE_START_DATE is NULL because you're filtering them out - the WHERE clause at the end sees that only rows that have the max date for a matching RESERVATION_ID are included. Are you trying to include NULL USAGE_START_DATE rows, even if they don't have a matching reservation?

UPDATE: you don't need the DISTINCT, and it may negatively affect your results. Also, since you want a "Blank" instead of NULL, you'll need to use ISNULL. You'll need a LEFT JOIN, and I've also added some table aliases to make it a little easier to read. Here's what I think you want:

SELECT rd.RESERVATION_ID, 
       ISNULL(MAX(rd.USAGE_START_DATE), '') as START_DATE,
  FROM BYNXFLEET_BI.RENTAL_DETAILS_VW rd
  LEFT
  JOIN WILLOW2K.RESERVATIONS r
    ON rd.RESERVATION_ID = r.RESERVATION_ID

Answer 2

Thanks a lot for your time!!!

I got 2 tables: linked by Reservation_id.

ReservationID   Usage Start Date
1                01/01/2001 00:00:00
1                02/01/2001 00:00:00
1                03/03/2001 00:00:00
2                NULL
2                NULL

So far with my current code: Here is what I got:

ReservationID    Usage Start DAte
1                03/03/2001 00:00:00

Here is what I want:

ReservationID     Usage Start Date
1                 03/03/2001 00:00:00
2

Thank you!!!

To simplify my code:

SELECT distinct RENTAL_DETAILS_VW.RESERVATION_ID,  
RENTAL_DETAILS_VW.USAGE_START_DATE,
FROM BYNXFLEET_BI.RENTAL_DETAILS_VW, WILLOW2K.RESERVATIONS  
WHERE RENTAL_DETAILS_VW.USAGE_START_DATE = (
select max(case when ors2.USAGE_START_DATE is null 
    then {ts ' 2009-01-01 00:00:00 ' } 
    else ors2.USAGE_START_DATE END) 
FROM RENTAL_DETAILS_VW ors2 
where ors2.RESERVATION_ID=RESERVATIONS.RESERVATION_ID)