Problem with a query


Here is my table and the expected result.

I tried this query, but the output for checkout shows as 3 instead of 1 for AB company, which is not my expected result. The problem is at only at the checkout column.

select person, sum(case when [checkin date] = '20180503' and description != 'Room Change' Then 1 else 0 end) as checkin, sum(case when [checkout date] = '20180503' Then 1 else 0 end) as Checkout, sum(case when [checkin date] =< '20180503' and [checkout date] > '20180503' Then 1 else 0 end) as POB
from table
group by organization

please provide your sample data you habe embedded as an image as follows

create table #mydata(organization int, ...etc with all the columns and data type

insert into #mydata
select 43, ..., "data" union
slect 54, .... etc with all the data poeces

Also, the expected result is by Organization. But your query is by person.

Your predicate on the description may not be correct:

description != 'Room Move'

In your table the only description is ROOM CHANGE