Hi, Thanks for the reply

here goes the example

Source Table

CREATE TABLE StudentHistory(

StudentHistoryId int IDENTITY(1,1) NOT NULL,

StudentId VARCHAR(6) NOT NULL,

DepartmentId VARCHAR(5) NOT NULL,

ProcessedMonth INT NOT NULL,

ProcessedYear INT NOT NULL,

ProcessedDate DATETIME NOT NULL,

InsertedDate DATETIME NOT NULL

CONSTRAINT PK_StudentHistory_StudentHistoryId PRIMARY KEY CLUSTERED

(

StudentHistoryId ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

Considerations

1) StudentId and DepartmentId are foreign Keys

2) Every month for a year will have present active records

3) ProcessedDate is daily process time getdate

4) InsertedDate is the record first time inserted for that month in a year

DestinationTable

CREATE TABLE Student(

PrimarykeyId int IDENTITY(1,1) NOT NULL,

StudentId VARCHAR(6) NOT NULL,

DepartmentId VARCHAR(5) NOT NULL,

InsertedDate DATETIME NOT NULL,

LastUpdated DATETIME NOT NULL

CONSTRAINT PK_StudentHistory_PrimarykeyId PRIMARY KEY CLUSTERED

(

PrimarykeyId ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

Consider like Student 1 is mapped to three departments in both the tables

for today history table is inserted with same student mapped to another department.

Student table lookup for student and department when left outer join is performed the matched three records are also returning null