Calculate price of room based on date

A list of the price is already set in the WordPress table.

I am trying to get the price of the room based on the date selected for booking.

$query_charge = "select
        SUM(DATEDIFF(
            case when '2020-06-16' > `charge_from` then '2020-06-16' else `charge_from` end,
            case when '2020-06-28' < `charge_to` then '2020-06-28' else `charge_to` end) * `room_adv_cost`)
       from
       `$table`
       where
       `room_id` = '8289' AND
       `charge_to` >= '2020-06-16' and
       `charge_from` <= '2020-06-28'
      ";

        $val = $wpdb->get_var( $wpdb->prepare($query_charge) );

The query returns the sum in negative value in PHPMyAdmin in my local system which is not the problem.
When I try the same query in WordPress the result is either 0 or null.

How can I get a proper result?
Thank you.