SQL Server result with one query

May 23, 2023

I have the example below and I am experimenting with joins to come up with a specific result

declare @stay_days table
(on_date    datetime)

declare @occupancy table
(on_date    datetime,
 type_id    numeric(10,0),
 phase      char(1))

declare @rate_room_type_details table
(type_id    numeric(10,0)   null)

insert @stay_days
select '2023-06-01'

insert @occupancy
select '2023-05-31',8,'D'

insert @occupancy
select '2023-06-01',8,'D'

insert @occupancy
select '2023-06-02',5,'D'

insert @occupancy
select '2023-06-03',8,'D'

insert @rate_room_type_details
select 1

insert @rate_room_type_details
select 2

insert @rate_room_type_details
select 3

insert @rate_room_type_details
select 4

insert @rate_room_type_details
select 5

insert @rate_room_type_details
select 6

insert @rate_room_type_details
select 7

insert @rate_room_type_details
select 8

I have tried this query

SELECT b.type_id,a.on_date,count(*)
FROM @occupancy a
RIGHT OUTER JOIN @stay_days c
ON a.on_date = c.on_date
RIGHT JOIN @rate_room_type_details b
ON a.type_id = b.type_id
AND a.phase IN ('I','D','A') 
GROUP BY b.type_id,a.on_date

but the result I get is

type_id on_date (No column name)
1       NULL       1
2       NULL       1
3       NULL       1
4       NULL       1
5       NULL       1
6       NULL       1
7       NULL       1
8       2023-06-01 1

which does not fill in the dates and th count is not zero.
The result I am after is the one below which displays all the type_IDs, has the common date instrad of the NULL and have count as 0 in all type_IDs except 8 which actually exists

type_id     on_date  (No column name)
    1       2023-06-01 0
    2       2023-06-01 0
    3       2023-06-01 0
    4       2023-06-01 0
    5       2023-06-01 0
    6       2023-06-01 0
    7       2023-06-01 0
    8       2023-06-01 1

Any help appreciated

>Solution :

Something like this perhaps:

SELECT b.type_id,c.on_date,count(a.on_date)
FROM @stay_days c
cross join @rate_room_type_details b
left join @occupancy a
    ON  a.on_date = c.on_date
    and a.type_id = b.type_id
    AND a.phase IN ('I','D','A') 
GROUP BY b.type_id,c.on_date

I switched around the joins a bit and change to CROSS JOIN because i guess @stay_days and @rate_room_type_details is your base and @occupancy is the details