I have a table and data like below:
Employee:
Id DepartmentId
1 100
2 100
3 100
LeaveRequest:
Id SentFromEmployeeId SentToEmployeeId
1 1 2
2 1 2
3 2 3
LeaveUpdateLogs:
Id RequestedDate LeaveRequestId Status
1 2021-11-01 11:55:56 1 Pending
2 2021-11-02 10:55:56 1 Accepted
3 2021-11-03 11:55:56 1 Accepted
4 2021-11-04 09:55:56 1 Declined
5 2021-11-05 10:55:56 1 Closed
6 2021-11-06 05:55:56 2 Pending
7 2021-11-07 05:55:56 2 Accepted
8 2021-11-08 02:55:56 2 Accepted
9 2021-11-09 05:55:56 2 Declined
10 2021-11-10 05:55:56 2 Closed
Now here I want to calculate statistics as below for a particular department:
Total number of requests sent
and received
for DepartmentId 100
.
But I am confused here for getting data for "Sent
" and "Received
" like below:
select SentFromEmployeeId,SentToEmployeeId,* from LeaveUpdateLogs l
inner join LeaveRequest lr on l.LeaveRequestId = lr.Id
inner join Employee e1 on e1.Id = lr.SentFromEmployeeId
inner join Employee e2 on e2.Id = lr.SentToEmployeeId
where (l.RequestedDate >= '2021-11-01' and l.RequestedDate < '2021-11-16')
and (e1.DepartmentId =100 or e2.DepartmentId = 100)
But this doesn't return any data although I have "25" records between this 2 dates. When I comment out this 3 lines:
inner join Employee e1 on e1.Id = lr.SentFromEmployeeId
inner join Employee e2 on e2.Id = lr.SentToEmployeeId
(e1.DepartmentId =100 or e2.DepartmentId = 100)
Then query is working fine but I want to get the data for particular department.
Can someone please help me fix this logic?
CodePudding user response:
Since I think you are only asking how to join on multiple values here are a couple ways of you could tackle this.
inner join Employee e1 on e1.Id in (lr.SentFromEmployeeId, lr.SentToEmployeeId)
Or
inner join Employee e1 on e1.Id = lr.SentFromEmployeeId
OR e1.Id = lr.SentToEmployeeId