How to compare dates from just only one table, SQL SERVER-CodePudding

I need help because im doing a project and i need to compare dates in SQL server, i need to get the names of the persons that recieved visitations on the same date and i cant do it.

create table Visit (

    Id_Visit int not null identity primary key,
    NumberVisits int not null,
    DescriptionVisits varchar(500) not null,
    DateVisit date not null,
    TimeVisit time not null,
    Person int not null,
    foreign key(Person) references Person(NumPerson)
);

insert into Visit values(2, 'Visita pela filha Joana e pelo neto João', '05-18-2020', '15:30', 1);

insert into Visit values(1, 'Visita pelo amigo Artur', '08-10-2020', '10:00', 2);

insert into Visit values(1, 'Visita pelo filho Joaquim', '09-14-2020', '17:10', 3);

insert into Visit values(1, 'Visita pelo advogado Firminio', '08-11-2020', '15:50', 2);

insert into Visit values(3, 'Visita pelas amigas Belandina, Carmélia e Rosa', '08-10-2020', '14:30', 1);


--Change Date from visit 3

update Visit 
set DateVisit = cast('2020-05-18' as date)
where Id_Visit = 3

--Change Time from visit 3

update Visit
set TimeVisit = cast('15:30' as time)
where Id_Visit = 3

--Change Date from visit 2

update Visit
set DateVisit = cast('2020-05-18' as date)
where Id_Visit = 2;

I want to do a select that only show de number of the persons that had visits in the same date.

CodePudding user response：

I think this is what you want http://sqlfiddle.com/#!18/896ff/2/0

SELECT DISTINCT Person
FROM Visit
WHERE DateVisit IN (
  SELECT DateVisit
  FROM Visit
  GROUP By DateVisit
  HAVING COUNT(*) > 1
 )

This finds the dates that exist more than once in the Visit table (ie. there are multiple visits on that day), then pulls the Persons that were visited.

CodePudding user response：

I think you mean this code

SELECT DateVisit ,COUNT(DISTINCT Person) AS PersonCount
FROM Visit GROUP BY DateVisit

You can also use the condition

SELECT COUNT(DISTINCT Person) AS PersonCount
FROM Visit WHERE datevisit = '2020-05-18'