How to add a column that considers any city a great person

Question

I have a table like this:

Person City workingdate A Newyork 10.11.2015 A Newyork 11.11.2015 A Newyork 12.11.2015 B Newyork 10.11.2015 B Newyork 15.11.2015 B Newyork 16.11.2015 B Newyork 18.11.2015 

I want to have a column that considers a great person inside the city:

 Person City workingdate countdistinctpersonincity A Newyork 10.11.2015 1 A Newyork 11.11.2015 0 A Newyork 12.11.2015 0 B Newyork 10.11.2015 1 B Newyork 15.11.2015 0 B Newyork 16.11.2015 0 B Newyork 18.11.2015 0 

Could you help me how to do this?

 CREATE TABLE Persons (Person varchar(25), City varchar(255), workingdate varchar(10)); insert into Persons values ('A', 'Newyork','10.11.2015'), ('A', 'Newyork','11.11.2015'), ('A', 'Newyork','12.11.2015'), ('B', 'Newyork','10.11.2015'), ('B', 'Newyork','15.11.2015'), ('B', 'Newyork','16.11.2015'), ('B', 'Newyork','18.11.2015'); 

Answer 1

 SELECT p.*, p2.* FROM #Persons p CROSS APPLY (SELECT COUNT (DISTINCT p1.Person) as countdistinctpersonincity FROM #Persons p1 WHERE p1.Person <> p.Person AND p1.City = p.City AND p1.workingdate = p.workingdate) p2; 

Test code

 CREATE TABLE #Persons (Person varchar(25), City varchar(255), workingdate varchar(10)) insert into #Persons values ('A', 'Newyork','10.11.2015'), ('A', 'Newyork','11.11.2015'), ('A', 'Newyork','12.11.2015'), ('B', 'Newyork','10.11.2015'), ('B', 'Newyork','15.11.2015'), ('B', 'Newyork','16.11.2015'), ('B', 'Newyork','18.11.2015'); SELECT p.*, p2.* FROM #Persons p CROSS APPLY (SELECT COUNT (DISTINCT p1.Person) as countdistinctpersonincity FROM #Persons p1 WHERE p1.Person <> p.Person AND p1.City = p.City AND p1.workingdate = p.workingdate) p2; DROP TABLE #Persons; 

The result is the following:

 Person City workingdate countdistinctpersonincity A Newyork 10.11.2015 1 A Newyork 11.11.2015 0 A Newyork 12.11.2015 0 B Newyork 10.11.2015 1 B Newyork 15.11.2015 0 B Newyork 16.11.2015 0 B Newyork 18.11.2015 0 

Answer 2

One option:

 SELECT * ,IIF(ROW_NUMBER() OVER(PARTITION BY City, Person ORDER BY workingdate) = 1, 1, 0) FROM Persons 

---

For SQL Server until 2012, you can use:

 SELECT P.* ,CASE WHEN DS.[City] IS NULL THEN 0 ELSE 1 END FROM Persons P LEFT JOIN ( SELECT City ,Person ,MIN(workingdate) AS workingdate FROM Persons GROUP BY City ,Person ) DS ON P.City = DS.[City] AND P.[Person] = DS.[Person] AND P.[workingdate] = DS.[workingdate] 

Answer 3

I believe this query will give your result. The idea is to check if the previous record, sorted by person, and date are different, so we reached a new person:

 select case when Person != lag(Person, 1, 'XXXXXX') over (order by Person, workingdate) then 1 else 0 end from table; 

To see how this works, an example is given:

 CREATE TABLE #Persons ( Person varchar(25), City varchar(255), workingdate varchar(10) ) insert into #Persons values ('A', 'Newyork','10.11.2015'), ('A', 'Newyork','11.11.2015'), ('A', 'Newyork','12.11.2015'), ('B', 'Newyork','10.11.2015'), ('B', 'Newyork','15.11.2015'), ('B', 'Newyork','16.11.2015'), ('B', 'Newyork','18.11.2015') select p.Person, p.City, p.workingdate, case when p.Person != lag(p.Person, 1, 'XXXXXX') over (order by p.Person, p.workingdate) then 1 else 0 end from #Persons p 

result:

 Person City workingdate COLUMN1 A Newyork 10.11.2015 1 A Newyork 11.11.2015 0 A Newyork 12.11.2015 0 B Newyork 10.11.2015 1 B Newyork 15.11.2015 0 B Newyork 16.11.2015 0 B Newyork 18.11.2015 0