case when returning duplicates-CodePudding

thanks for helping in advance

REQUEST: vacance the noel: True, if all there are available in periods between 19/12/2022 and 02/01/2023 (included) False, otherwise

I have a table named houses with houses informations and I have a table, periods which contains the periods in which the houses are available [periods table with the start and the end of the period][1]

I have to check if the house is available yes our no on the period in between '2022-12-19' AND '2023-01-02'(included)

this is my code and it returns duplicates sometimes, in that case the one is true the other is false

And I want to display true whenever the house has at least one period that is between '2022-12-19' AND '2023-01-02'(included)

This is the result [the house 885 appears twice with one row true and the other false][2]

This has to be the result [expected result][3]


CASE
WHEN (periods.start_at BETWEEN '2022-12-18' AND '2023-01-03') AND (periods.end_at BETWEEN '2022-12-21' AND '2023-01-03')
THEN TRUE 
ELSE FALSE
END AS vancances_noel,


FROM houses
LEFT JOIN destinations on houses.destination_id=destinations.id
LEFT JOIN periods on houses.id=periods.house_id```


  [1]: https://i.stack.imgur.com/S75ym.png
  [2]: https://i.stack.imgur.com/HRLKO.png
  [3]: https://i.stack.imgur.com/HU1aS.png

CodePudding user response：

You may want to try this:

CASE WHEN (cast(periods.start_at as date) BETWEEN '2022/12/18' AND '2023/01/03') AND (cast(periods.end_at as date) BETWEEN '2022/12/21' AND '2023/01/03')

There may be some confusion as to whether periods.start_at or periods.end_at are date values or whether the strings used in the BETWEEN operator are date values.

CodePudding user response：

sql query will display n times ( n nomber periods) and each line has status of period , so to show which avaible only your sql should be:

    select houses.id, periods.start_at,periods.end_at FROM houses
LEFT JOIN destinations on houses.destination_id=destinations.id
LEFT JOIN periods on houses.id=periods.house_id
where (periods.start_at BETWEEN '2022-12-18' AND '2023-01-03') AND (periods.end_at BETWEEN '2022-12-21' AND '2023-01-03')