Hotel room Status generation in SQL

Question

I am having a table structure like this

id             int
checkinttime   datetime
checkouttime   datetime
roomid         int

I have recorded checkin and checkout time in this table. My question is how to generate a datewise report that generate whether room occupied or not in that date range like this.

room  3/1/2016 3/2/2016 3/3/2016 6/4/2016
r1      P         V       V        P
r2      v          p      p        p

where p means Occupied, and V means vacant. How to write SQL for this.?

Thanks in advance.

I think instead of making the date range as dynamic, use room id as columns and date tange as rows. — Utsav, Feb 27 '16 at 06:26
you can refer to this link http://stackoverflow.com/questions/2157282/generate-days-from-date-range?lq=1 — Wajih, Feb 27 '16 at 06:35
I'd suggest to 1) populate persistent table `calendar` 2) link `calendar` filtered by period range with `rooms` via cross join 3) `left join` checkin-checkout table 4) build pivoted table (with separate column per date) on client-side/frontend — Ivan Starostin, Feb 27 '16 at 08:57

shA.t · Answer 1 · 2016-02-27T10:26:17.623

2

A solution is to use a static query over rooms as columns like this:

;WITH dates(date) AS (
    SELECT @startDate
    UNION ALL
    SELECT DATEADD(DAY, 1, date)
    FROM dates
    WHERE date < @endDate
), occupied AS (
SELECT date, c.booking_id
FROM dates d
    LEFT JOIN  -- You can use `JOIN` only also to remove free dates
    CheckStatus c ON d.date BETWEEN c.checkin_time AND c.checkout_time
)
SELECT date
    , MIN(CASE WHEN id = 1 THEN 'P' ELSE 'V' END) AS 'r1'
    , MIN(CASE WHEN id = 2 THEN 'P' ELSE 'V' END) AS 'r2'
FROM occupied
GROUP BY date;

The result will be like this:

date       | r1 | r2 |
-----------+----+----+
2016-03-01 | P  | V  |
2016-03-02 | V  | P  |
2016-03-03 | V  | P  |
2016-03-04 | V  | P  |

edited Feb 27 '16 at 10:26

answered Feb 27 '16 at 08:36

shA.t

16,580
5
54
111

please write complete query with parameters startdate and enddate – siyadkk Feb 27 '16 at 08:59
with those parameters it can be easier ;). – shA.t Feb 27 '16 at 09:26
thanks Sha for your support. I uploaded image. Please check it. – siyadkk Feb 27 '16 at 09:43
what is daterange means here.? – siyadkk Feb 27 '16 at 10:05
Msg 208, Level 16, State 1, Line 1 Invalid object name 'dateRange'. – siyadkk Feb 27 '16 at 10:11
Msg 240, Level 16, State 1, Line 1 Types don't match between the anchor and the recursive part in column "date" of recursive query "dates". – siyadkk Feb 27 '16 at 10:32
What is type of `@startDate`? it should be `datetime` => Also `@endDate` ;). – shA.t Feb 27 '16 at 11:23
startDate and endDate are datetime parameters. is it will result all rooms? – siyadkk Feb 27 '16 at 11:27
this query is to generate report that shows all rooms status between a start date and end date. – siyadkk Feb 27 '16 at 11:28
this is static for two rooms you need to add `, MIN(CASE WHEN id = i THEN 'P' ELSE 'V' END) AS 'ri'` for each room – shA.t Feb 27 '16 at 11:40
is it possible for automatically select all rooms from table as I uploaded image in question. if possible please update query. I connected checkstatus table bookingid with bookingtable and roomname. – siyadkk Feb 27 '16 at 11:46
Then you need to use Dynamic SQL ;). – shA.t Feb 27 '16 at 12:47
its work, but how i can show The userName in the user table. (reservation table has userId). thanks in advance – Mehmet Jun 18 '19 at 12:04

Hotel room Status generation in SQL

1 Answers1