how does one use a created column as input to group by in SQL?

Question

I am creating a timeregistry in SQL and almost have created the desired view.

What I need is a view with 1 row per date + employee and the first and last recorded activity of that day. I use DATEFROMPARTS first to extract the date. I have used the "group by" statement to filter out duplicate date + employee combinations. unfortunately, this only works with 2 existing columns and thus not the created date-column. I have added a table in http://sqlfiddle.com/#!18/c5f06/13 and my try to solve it. ( I also tried "group by date, name having count(concat(date, name)) = 1) could someone please help me?

BUILD SCHEMA

CREATE TABLE employees(
  id int not null,
  name varchar(25) not null,
  dt DateTime not null,
  action varchar(10) not null,
  primary key(id));
  
INSERT INTO employees
VALUES
('1', 'a', '2022-06-13 17:00:00', 'out'),
('2', 'a', '2022-06-13 12:30:00', 'in'),
('3', 'a', '2022-06-13 12:00:00', 'out'),
('4', 'a', '2022-06-13 9:00:00', 'in'),
('5', 'b', '2022-06-13 19:00:00', 'out'),
('6', 'b', '2022-06-13 9:00:00', 'in'),
('7', 'b', '2022-06-14 18:00:00', 'out'),
('8', 'b', '2022-06-14 8:00:00', 'in'),
('9', 'noname', '2022-06-14 0:00:00', 'in');

RUN SQL

select
DATEFROMPARTS(year(dt), month(dt), day(dt)) as date,
name,
min(dt) over(partition by Datepart(dy, dt), name) as first,
max(dt) over(partition by Datepart(dy, dt), name) as last,
concat(year(dt), month(dt), day(dt), name) as thiscolumnshouldonlyhaveuniquevalues
from employees
where name <> 'noname'
group by dt, name having count(concat(year(dt), month(dt), day(dt), name))= 1
order by date asc, name asc

Answer 1

The solution for your problem is that you need to use DISTINCT.

select distinct
DATEFROMPARTS(year(dt), month(dt), day(dt)) as date,
name,
min(dt) over(partition by Datepart(dy, dt), name) as first,
max(dt) over(partition by Datepart(dy, dt), name) as last,
concat(year(dt), month(dt), day(dt), name) as thiscolumnshouldonlyhaveuniquevalues
from employees
where name <> 'noname'
group by dt, name having count(concat(year(dt), month(dt), day(dt), name))= 1
order by date asc, name asc