Compare between two rows in a table

Transact-SQL
1

Hi,

I have a table similar to this,

create table #test
(
Organisation varchar(255),
MNO varchar(255),
[check-in date] date,
[check-out date] date,
[Room No] Varchar(255)
)

insert into dbo.#test
values ('MNO','A','20180503','20180507','5')
insert into dbo.#test
values ('MNO','B','20180505','20180513','3-a')
insert into dbo.#test
values ('MNO','A','20180507','20180604','7')
insert into dbo.#test
values ('MNO','A','20180604','20180615','9')
insert into dbo.#test
values ('MNO','B','20180604','20180615','3-a')
insert into dbo.#test
values ('MNO','C','20180604','20180615','2')
insert into dbo.#test
values ('CQA','A','21080601','20180613','11')
insert into dbo.#test
values ('CQA','A','20180603','20180614','12')
insert into dbo.#test
values ('CQA','B','20180602','20180604','14')
insert into dbo.#test
values ('CQA','B','20180604','20180614','13')

select organisation,
sum(case when [check-in date] = '20180604' Then 1 else 0 end) as checkin,
sum(case when [check-out date] = '20180604' Then 1 else 0 end) as [check-out] from #test
group by organisation

Actually, I'm expecting the output as below. I don't want any person who just changed the room to appear in the check-in or check-out column.

I believe it is something related to comparison between two rows and get the desired result. I've no idea how to start with.

result.

Thanks for your assistance on this.

2

From your data, how do you know someone just changed rooms and didn't checkout, then later the same day, check in again?

3

Usually, we found out by seeing the date if someone "check-out" on the same date and "check-in" on the same date, then we consider room move. Based on this Logic, I would like to make the query.

In fact, there in one more column which indicates "Room Move" but due to some bugs, it is not always correct and we can't follow that.

4

then just add to the case:
when [check-in date] = '20180604' and [check-in date] <> [check-out date]

etc.

5

Thanks @gbritton,
It didn't work. It shows MNO 2 check-in but in real only one.

What about check-out? Any idea about that?

Thanks in advance.

6

Looking at your expected results again, they don't match your data. For example CGA did checkin on the 4th, but didn't checkout until the 14th. Using this query:

select organisation,
sum(case when [check-in date] = '20180604' AND [check-in date] <>  [check-out date] THEN 1 else 0 end) as checkin,
sum(case when [check-out date] = '20180604' AND [check-out date]  <> [check-in date] THEN 1 else 0 end) as [check-out] from #test
group by organisation

I get:

image

Which matches your data, I believe

7

No,

New check-in is the one who is actually check-in, not room move. Similarly, check-out is the one who is actually check-out, not room move.

I expect the result as below

result

8

As I said, your sample data doesn't support your desired result. Or you need to explain it another way

9

hi

I know this topic is from a long time ago

I tried to understand what you were SAYING

I think I have the solution
Please let me know
Thanks
:slight_smile:
:slight_smile:

I have gotten the CheckIn part
I

think

the LOGIC used in the SQL is right
"Experts" "Seniors" in this forum please help get checkOUT
:slight_smile:

drop create data .. 
use tempdb 

go 


drop table #test 
go 

create table #test
(
Organisation varchar(255),
MNO varchar(255),
[check-in date] date,
[check-out date] date,
[Room No] Varchar(255)
)

insert into dbo.#test
values ('MNO','A','20180503','20180507','5')
insert into dbo.#test
values ('MNO','B','20180505','20180513','3-a')
insert into dbo.#test
values ('MNO','A','20180507','20180604','7')
insert into dbo.#test
values ('MNO','A','20180604','20180615','9')
insert into dbo.#test
values ('MNO','B','20180604','20180615','3-a')
insert into dbo.#test
values ('MNO','C','20180604','20180615','2')
insert into dbo.#test
values ('CQA','A','21080601','20180613','11')
insert into dbo.#test
values ('CQA','A','20180603','20180614','12')
insert into dbo.#test
values ('CQA','B','20180602','20180604','14')
insert into dbo.#test
values ('CQA','B','20180604','20180614','13')
go
SQL 
SELECT DISTINCT a.organisation, 
                CASE 
                  WHEN b.count > 0 THEN 1 
                  ELSE 0 
                END AS checkin 
FROM   #test a 
       LEFT JOIN (SELECT [room no], 
                         [organisation], 
                         Count(*) AS count 
                  FROM   #test 
                  GROUP  BY [room no], 
                            [organisation] 
                  HAVING Count(*) > 1) b 
              ON a.[organisation] = b.[organisation]
Results ..

image

10

hi

i had earlier given a solution

Looking at it again .. i understood differently
I rewrote the SQL

Rewrote SQL 
SELECT a.organisation, 
       Sum(a.count) / 2 
FROM   (SELECT DISTINCT a.organisation, 
                        a.[room no], 
                        count 
        FROM   #test a 
               LEFT JOIN (SELECT [room no], 
                                 [organisation], 
                                 Count(*) AS count 
                          FROM   #test 
                          GROUP  BY [room no], 
                                    [organisation] 
                          HAVING Count(*) > 1) b 
                      ON a.[organisation] = b.[organisation] 
                         AND a.[room no] = b.[room no]) a 
GROUP  BY a.organisation