I have included two pictures that are in Excel just for ease of viewing but I am doing the queries in SQL.
Hopefully, this is possible as the algorithm I have is not giving me the correct results.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Not sure I understand the problem, but if you first pick distinct rows, and then count number of rows per date
select dt, count(1) from ( select distinct dt, job, title, name from t ) as x group by dt
I used dt instead of date since it is a reserved word
I am not quite sure what you are asking, but I think you would want to concatenate the other columns you have into one string. then you group by the date, and count the distinct concatenated fields.
In the below i dump to a temp table first to make it easier to read, but you can do it all in one.
DROP TABLE IF EXISTS #X SELECT Date, Job + ' ' + Title + ' ' Name As NewColumn INTO #X FROM YourTable SELECT Date, COUNT(DISTINCT NewColumn) AS CNT FROM #X GROUP BY Date
simulated table must be replaced by your table, I only believe it as a reference
DECLARE @SimulatedTable TABLE( [Date] DATE, Job VARCHAR(5), Title VARCHAR(5), [Name] VARCHAR(5)) INSERT INTO @SimulatedTable VALUES ('12/7/2019','A','B','ABA'),('12/7/2019','B','A','BAB'),('12/7/2019','A','B','ABA'),('12/7/2019','C','C','CCC'), ('12/7/2019','D','D','DDD'),('12/7/2019','E','E','EEE'),('12/7/2019','F','F','FFF'),('12/8/2019','G','G','GGG'),('12/9/2019','H','H','HHH') SELECT [Date],COUNT(DISTINCT Job+Title+[Name]) AS 'Distinct COUNT Based on Colums' FROM @SimulatedTable GROUP BY [Date]