Link to home
Start Free TrialLog in
Avatar of Goutam Singh
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')

Open in new window


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

Open in new window


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
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Why Goutam has "Present" status and Mansi has "Absent"?
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:
SELECT u.Id, u.userid, u.name, u.empcode, MAX(t.InTime), MAX(t.OutTime), MAX(t.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

Open in new window

Avatar of Goutam Singh
Goutam Singh

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 :)
I want the data to consist the WorkDate of those Employees who are absent
And which date should they have if they went absent?
@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.
Please suggest the same
I 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

Open in new window

@Vitor Montalvão: Thanks :)
@Vitor Montalvão:
  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

Open in new window


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

Open in new window

@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
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks :)