2

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
  • 1
    You should add scripted CREATE TABLE definitions, etc, as per these instructions. – Max Vernon Jan 25 at 22:47
  • Sure but as the first part is concerned is that going to keep my data valid as needed? – jwize Jan 25 at 22:49
  • Thanks! The two scripts you added simply allow others to more easily understand and work with the question. I'd upvote the question, but I'm out of votes for today. – Max Vernon Jan 25 at 22:55
  • If you could add some sample data it could help, and the desired result. BTW it seems there is something wrong with the computed column. dbfiddle.uk/… – McNets Jan 25 at 23:24
0

Your query seem ok.

you should thoroughly Test the Resultset in all server.

Only concern is that when it is ISNULL then Why you will update with 0.Is it really necessary ?

Also second CTE is not required.

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
)

 select ec.Id, l.HasAttachments
  INTO TempEmployeeCourse from History l INNER JOIN 
      EmployeeCourse ec on l.CompanyCourseId = ec.CompanyCourseId AND 
      l.EmployeeId = ec.EmployeeId
where WHERE l.rownum = 1


update EC
set HasAttachments =TEC.HasAttachments 
,UniqueId =TEC.UniqueId  
from EmployeeCourse EC
inner join dbo.TempEmployeeCourse TEC 
on TEC.Id =EC.EmployeeCourse.Id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.