By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.Department
(
DepartmentNumber tinyint identity(1, 1) not null,
DepartmentName varchar(50) NOT NULL,
DepartmentNameAlternative varchar(50) NOT NULL,
Increment tinyint null,
)
INSERT INTO dbo.Department (DepartmentName, DepartmentNameAlternative, Increment)
VALUES
('HR', 'HR', 10)
, ('IT', 'IT', 11)
, ('Sales', 'Sales', 12);
CREATE TABLE dbo.DailyFile
(
DepartmentName varchar(50) NOT NULL,
)
INSERT INTO dbo.DailyFile (DepartmentName)
VALUES
('HR')
, ('HR')
, ('Sales')
, ('IT')
, ('Manufacturing')
, ('IT')
, ('Manufacturing')
, ('Sales');
insert into dbo.Department (DepartmentName, DepartmentNameAlternative, Increment)
select distinct df.DepartmentName, df.DepartmentName, COALESCE(MAX(Increment), 0) + 1
Msg 8120 Level 16 State 1 Line 35
Column 'dbo.DailyFile.DepartmentName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.