i want to show a list of terminated and active employees in a specific range, but if an employee is reappointed, i don't want to show that employee record in the list
from the query i have written there shouldn't be null record since the latest expiry date doesn't fall in those range, but it shows the previous record instead of null
EmployeeId
EmpName
Salary
RegisrationId
1
John Doe
2000
5
2
John Doe
2000
6
RegistrationId
RegistrationStatus
RegisteredDate
CancelledDate
ExpiryDate
5
Terminated
1/1/2019
12/31/2019
12/31/2019
6
Active
1/1/2020
12/31/2020
here is what i have done so far
//The specific date range i want
var expiryFromtoDate = DateTime.Today.AddDays(-90);
var expiryUptoDate = DateTime.Today.AddDays(90
model.Employees = (from e in Employees
where
(e.EmploymentTypeID == ModelConfig.EmploymentTypes.CONTRACT) &&
(
(e.Registration.States == ModelConfig.RegistrationStates.ACTIVE && e.Registration.ExpiryDate.HasValue && (e.Registration.ExpiryDate >= DateTime.Today) && e.Registration.ExpiryDate <= expiryUptoDate) ||
(e.Registration.RegistrationStateID == ModelConfig.RegistrationStates.TERMINATED && e.Registration.CancelledDate.HasValue && e.Registration.CancelledDate >= expiryFromtoDate)
)
select e).ToList();
from the query i have written there shouldn't be null record since the latest expiry date doesn't fall in those range, but it shows the previous record instead of null
This is really confusing, you said 'there shouldn't be null record' but also said 'but it shows previous record instead of null', do you want null or not? And which record do you mean, CancelledDate? ExpiryDate?
As we can see from your code, you are selecting two kinds of employee:
Is contract and Registration.States is "Active" and has ExpiryDate value and this value is after today and will expire in 90 days from today.
Is contract and Registration.RegistrationStateID(?) is "Terminated" and has CancelledDate value and has been canceld after 90 days before today.
All emloyee who met any one of above two conditions will be selected. If the second table you provided is the query result then both two records meet the above conditions.
Would you please provide a clearer description of your problem? That would be much more helpful.
thanks for ur reply, here is what i want to acheive, if the employee is reappointed it update Reappointment - new employmentId colum is added.
right now dont know how to write this query in linq using left outer join for reappointment table and also to use reappointment table in where clause,
-- Region Parameters
DECLARE @p0 Int = 2 -- contract
DECLARE @p1 Int = 1 --active
DECLARE @p2 DateTime = '2019-12-28 00:00:00.000' -- GetDate()
DECLARE @p3 DateTime = '2020-03-27 00:00:00.000' -- GetDate + 90 Days
DECLARE @p4 Int = 9 --terminate
DECLARE @p5 DateTime = '2019-09-29 00:00:00.000'--GetDate - 90 Days
-- EndRegion
SELECT [t0].[EmploymentID], [t0].[RegistrationID], [t1].ExpiryDate, [t1].CancelledDate
FROM [Employments] AS [t0]
INNER JOIN [Registrations] AS [t1] ON [t1].[RegistrationID] = [t0].[RegistrationID]
LEFT OUTER JOIN
Reappointment ON [t0].EmploymentID = Reappointment .EmploymentID
WHERE ([t0].[EmploymentTypeID] = @p0) AND ((([t1].[RegistrationStateID] = @p1) AND ([t1].[ExpiryDate] IS NOT NULL)
AND ([t1].[ExpiryDate] >= @p2)
AND ([t1].[ExpiryDate] <= @p3)) OR (([t1].[RegistrationStateID] = @p4)
AND ([t1].[CancelledDate] IS NOT NULL) AND ([t1].[CancelledDate] >= @p5)))
and (t0.OfficeID = 1511) and (Reappointment .NewEmploymentID = null)
Now we know the two tables provided in the first post are not the results but the Employments table and Registrations table in your database, but the Reappointmenttable is not provided.
marya
if the employee is reappointed it update Reappointment - new employmentId colum is added.
By this, you mean a new record with employmentId will be inserted into your Reappointment table right? Do you make sure the new employmentId added is as same as the corresponding one in your Employments table?
Besides, we don't know what's the relationship between the Employees list in your current linq and these three tables in your database, please provide related code as much as possible including the definition of Employees
see if we can reproduce the problem and solve it.
Member
16 Points
18 Posts
wrong entry is shown in the list instead of null records
Dec 26, 2019 12:06 PM|marya|LINK
i want to show a list of terminated and active employees in a specific range, but if an employee is reappointed, i don't want to show that employee record in the list
from the query i have written there shouldn't be null record since the latest expiry date doesn't fall in those range, but it shows the previous record instead of null
here is what i have done so far
Contributor
3140 Points
983 Posts
Re: wrong entry is shown in the list instead of null records
Dec 27, 2019 02:28 AM|Yang Shen|LINK
Hi marya,
This is really confusing, you said 'there shouldn't be null record' but also said 'but it shows previous record instead of null', do you want null or not? And which record do you mean, CancelledDate? ExpiryDate?
As we can see from your code, you are selecting two kinds of employee:
All emloyee who met any one of above two conditions will be selected. If the second table you provided is the query result then both two records meet the above conditions.
Would you please provide a clearer description of your problem? That would be much more helpful.
Best Regard,
Yang Shen
Member
16 Points
18 Posts
Re: wrong entry is shown in the list instead of null records
Dec 28, 2019 07:42 AM|marya|LINK
thanks for ur reply, here is what i want to acheive, if the employee is reappointed it update Reappointment - new employmentId colum is added.
right now dont know how to write this query in linq using left outer join for reappointment table and also to use reappointment table in where clause,
Contributor
3140 Points
983 Posts
Re: wrong entry is shown in the list instead of null records
Dec 30, 2019 07:24 AM|Yang Shen|LINK
Hi marya,
Sorry but the requirement is still confusing.
Now we know the two tables provided in the first post are not the results but the
Employments
table andRegistrations
table in your database, but theReappointment
table is not provided.By this, you mean a new record with employmentId will be inserted into your Reappointment table right? Do you make sure the new employmentId added is as same as the corresponding one in your Employments table?
Besides, we don't know what's the relationship between the
Employees
list in your current linq and these three tables in your database, please provide related code as much as possible including the definition ofEmployees
see if we can reproduce the problem and solve it.In addition, please check LEFT OUTER JOIN in LINQ for more information.
Best Regard,
Yang Shen