I have a employee course table which holds the current record for each of my employees who have some courses that they have taken. Then I have a history that keeps a static version of the save data with some more metadata. The histories can be inserted in any order. That is the importer can just put them in any order. With each history that is added there is a next due date which states when they have to take the course again. I need the live employee course table to be updated with the latest record from the history ordered by NextDueDate Desc, then by Id Desc. There are two properties(HasAttachments, UniqueId) that need to be copied from the history table. The employeeId and companycourseid act as a reference to the history table.
Here is the code I came up with. I think this will work but I need some second opinions.
WITH History AS (
SELECT Id, h.EmployeeId, CompanyCourseId, NextDueDate, HasAttachments,
ROW_NUMBER() OVER (Partition BY EmployeeId,CompanyCourseId ORDER BY
NextDueDate DESC, Id DESC) AS rownum
FROM TrainingHistory h
),
Latest AS
(
SELECT h.* FROM History h
WHERE h.rownum = 1
)
select ec.Id, l.HasAttachments
INTO TempEmployeeCourse from Latest l INNER JOIN
EmployeeCourse ec on l.CompanyCourseId = ec.CompanyCourseId AND
l.EmployeeId = ec.EmployeeId
I just put the results I need for updating into a temp table so I can easily update the required UniqueId and HasAttachments columns into the employee course table.
update EmployeeCourse
set HasAttachments = ISNULL(
(select HasAttachments from dbo.TempEmployeeCourse where Id
EmployeeCourse.Id), 0),
UniqueId = ISNULL(
(select UniqueId from dbo.TempEmployeeCourse where Id =
EmployeeCourse.Id), newid())
GO
DROP TABLE [dbo].TempEmployeeCourse
GO
This seems like the most elegant solution. Is there anything I might be missing here?
EmployeeCourse
CREATE TABLE [dbo].[EmployeeCourse](
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeId] [int] NOT NULL,
[CompanyCourseId] [int] NOT NULL,
[HasAttachments] [bit] NOT NULL,
[UniqueId] [uniqueidentifier] NULL,
CONSTRAINT [PK_Employee_Courses] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
TrainingHistory
CREATE TABLE [dbo].[TrainingHistory](
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeId] [int] NULL,
[NextDueDate] [date] NULL,
[CompanyCourseId] [int] NULL,
[UniqueId] [uniqueidentifier] NULL,
[HasAttachments] bit NOT NULL DEFAULT 0,
CONSTRAINT [Training_History$PrimaryKey] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO