Problem with a query

1

Hi,

Here is my table and the expected result.

Query%20error
Query error.png754×363 6.79 KB

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

2

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
3

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

4

Your predicate on the description may not be correct:

description != 'Room Move'

In your table the only description is ROOM CHANGE