Goutam Singh
asked on
Present Absent from working date rage
CREATE TABLE [tt](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserId] [varchar](20) NULL,
[EmpCode] [varchar](50) NULL,
[Name] [varchar](200) NULL,
[WorkDate] [varchar](20) NULL,
[InTime] [varchar](20) NULL,
[OutTime] [varchar](20) NULL,
[TotalTime] [varchar](50) NULL,
)
insert into [tt] values
('106','E2E106','Goutam Kumar','2017-02-21','12:54:54 PM','10:06:42 PM','08:55:00')
,('106','E2E106','Goutam Kumar','2017-02-20','12:49:21 PM','09:26:27 PM','07:53:00')
,('106','E2E106','Goutam Kumar','2017-02-15','12:31:51 PM','09:21:14 PM','08:30:00')
,('106','E2E106','Goutam Kumar','2017-02-13','05:46:06 PM','09:32:17 PM','03:46:00')
,('106','E2E106','Goutam Kumar','2017-02-14','01:02:28 PM','09:32:50 PM','07:39:00')
,('111','E2E111','Mansi Manchanda','2017-02-21','12:42:42 PM','09:09:42 PM','08:07:00')
,('111','E2E111','Mansi Manchanda','2017-02-17','12:09:11 PM','09:40:46 PM','06:36:00')
,('111','E2E111','Mansi Manchanda','2017-02-16','11:56:21 AM','09:20:08 PM','08:07:00')
,('111','E2E111','Mansi Manchanda','2017-02-15','01:07:19 PM','09:57:40 PM','08:30:00')
CREATE TABLE tUserInfo(
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserId] [nvarchar](20) NULL,
[Name] [nvarchar](200) NULL,
[EmpCode] [varchar](200) NULL,
)
INSERT into tUserInfo VALUES
('106','Goutam Kumar','E2E106')
,('111','Mansi Manchanda','E2E111')
,('112','Arvind Kumar Prajapati','E2E112')
,('116','Rahul Garg','E2E116')
I want to result like:
Id UserId Name EmpCode InTime OutTime WorkDate Status
1 106 Goutam Kumar E2E106 2017-02-13 Present
2 111 Mansi Manchanda E2E111 2017-02-14 Absent
3 112 Arvind Kumar E2E112 2017-02-14 Absent
4 116 Rahul Garg E2E116 2017-02-17 Absent
I am able to get the Present/Absent status of a user for a single date. But my question here is that I want to get all the users that are present/absent between a given date range
ASKER
@Vitor Montalvão :
yes you are correct users that aren't in tt table those ones are considered "Absent"
The above query is yielding the result to me but I want the data to consist the WorkDate of those Employees who are absent.(Presently, Its showing null, which is the problem I am facing)
thanks :)
yes you are correct users that aren't in tt table those ones are considered "Absent"
The above query is yielding the result to me but I want the data to consist the WorkDate of those Employees who are absent.(Presently, Its showing null, which is the problem I am facing)
thanks :)
I want the data to consist the WorkDate of those Employees who are absentAnd which date should they have if they went absent?
ASKER
@Vitor Montalvão: The above data is all the data that has to be managed with workDate and leavestatus(Present/Absent ).Please suggest the same. Also, I want the workDate to be shown against the status Absent and that workDate is the date on which the employee is Absent i.e the date on which the record is not there for an employee.
thanks.
thanks.
Please suggest the sameI can't do that. I don't know the requirements.
We can help you with the technical part but we can't assume the business needs. You may need to clarify this date question with the people that asked you to find a solution for this problem.
And by your example I think that you should use MIN and not MAX function:
SELECT u.Id, u.userid, u.name, u.empcode, MIN(t.InTime) InTime, MIN(t.OutTime) OutTime, MIN(t.WorkDate) WorkDate,
CASE
WHEN t.UserId IS NULL THEN 'Absent'
ELSE 'Present'
END status
FROM tt t
RIGHT JOIN tUserInfo u ON t.UserId = u.UserId
GROUP BY u.Id, u.userid, u.name, u.empcode, t.UserId
ASKER
@Vitor Montalvão: Thanks :)
ASKER
@Vitor Montalvão:
Vitor i want to data to be like below shown
i don't need the inTime, OutTime . I want status to be displayed along with the Date.
Vitor i want to data to be like below shown
Id UserId Name EmpCode WorkDate Status
1 106 Goutam Kumar E2E106 2017-02-13 Present
2 111 Mansi Manchanda E2E111 2017-02-14 Absent
3 112 Arvind Kumar E2E112 2017-02-14 Absent
4 116 Rahul Garg E2E116 2017-02-17 Absent
i don't need the inTime, OutTime . I want status to be displayed along with the Date.
You still need to know where the date comes from for the Absent cases:
SELECT u.Id, u.userid, u.name, u.empcode, MIN(t.WorkDate) WorkDate,
CASE
WHEN t.UserId IS NULL THEN 'Absent'
ELSE 'Present'
END status
FROM tt t
RIGHT JOIN tUserInfo u ON t.UserId = u.UserId
GROUP BY u.Id, u.userid, u.name, u.empcode, t.UserId
ASKER
@Vitor Montalvão: I have these two tables only and all i want is work date for the absent employees Instead of NULL thanks
Id userid name empcode WorkDate status
1 106 Goutam Kumar E2E106 2017-02-13 Present
2 111 Mansi Manchanda E2E111 2017-02-15 Present
3 112 Arvind Kumar Prajapati E2E112 NULL Absent
4 116 Rahul Garg E2E116 NULL Absent
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks :)
I'm assuming the users that aren't in tt table those ones that should be considered "Absent". If this assumption is correct then the following should work for you:
Open in new window