Get monthly attendance

Question

I have a table with Fields and Values like this

IF EXISTS(SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('myTable'))
    BEGIN;
        DROP TABLE [myTable];
    END;
    GO

CREATE TABLE [myTable] (
    [myTableID] INTEGER NOT NULL IDENTITY(1, 1),
    [EmpID] INTEGER NULL,
    [Name] VARCHAR(255) NULL,
    [TimeIn] datetime,
    [TimeOut] datetime,
    [Status] VARCHAR(255) NULL,
    PRIMARY KEY ([myTableID])
);
GO

INSERT INTO myTable([EmpID],[Name],[TimeIn],[TimeOut],[Status]) VALUES(1,'Emmanuel','2018-01-09 08:25:39',NULL,'Present');
INSERT INTO myTable([EmpID],[Name],[TimeIn],[TimeOut],[Status]) VALUES(1,'Emmanuel',NULL,'2018-01-09 16:13:53','Present');
INSERT INTO myTable([EmpID],[Name],[TimeIn],[TimeOut],[Status]) VALUES(1,'Emmanuel','2018-01-11 08:25:39',NULL,'Present');
INSERT INTO myTable([EmpID],[Name],[TimeIn],[TimeOut],[Status]) VALUES(1,'Emmanuel',NULL,'2018-01-11 11:13:53','Present');
INSERT INTO myTable([EmpID],[Name],[TimeIn],[TimeOut],[Status]) VALUES(1,'Emmanuel','2018-01-11 12:25:39',NULL,'Present');
INSERT INTO myTable([EmpID],[Name],[TimeIn],[TimeOut],[Status]) VALUES(1,'Emmanuel',NULL,'2018-01-11 16:13:53','Present');
INSERT INTO myTable([EmpID],[Name],[TimeIn],[TimeOut],[Status]) VALUES(2,'Stone','2018-01-09 09:33:44',NULL,'Present');
INSERT INTO myTable([EmpID],[Name],[TimeIn],[TimeOut],[Status]) VALUES(2,'Stone',NULL,'2018-01-09 16:33:44','Present');
INSERT INTO myTable([EmpID],[Name],[TimeIn],[TimeOut],[Status]) VALUES(4,'Basia','2018-01-31 15:08:07',NULL,'Present');
INSERT INTO myTable([EmpID],[Name],[TimeIn],[TimeOut],[Status]) VALUES(4,'Basia',NULL,'2018-01-31 16:08:07','Present');
INSERT INTO myTable([EmpID],[Name],[TimeIn],[TimeOut],[Status]) VALUES(2,'Stone','2018-01-19 09:33:44',NULL,'Present');
INSERT INTO myTable([EmpID],[Name],[TimeIn],[TimeOut],[Status]) VALUES(2,'Stone',NULL,'2018-01-19 16:33:44','Present');
INSERT INTO myTable([EmpID],[Name],[TimeIn],[TimeOut],[Status]) VALUES(4,'Basia','2018-01-11 15:08:07',NULL,'Present');
INSERT INTO myTable([EmpID],[Name],[TimeIn],[TimeOut],[Status]) VALUES(4,'Basia',NULL,'2018-01-11 16:08:07','Present');
INSERT INTO myTable([EmpID],[Name],[TimeIn],[TimeOut],[Status]) VALUES(5,'Nathan','2018-01-19 08:55:22',NULL,'Present');
INSERT INTO myTable([EmpID],[Name],[TimeIn],[TimeOut],[Status]) VALUES(5,'Nathan',NULL,'2018-01-19 17:46:32','Present');
INSERT INTO myTable([EmpID],[Name],[TimeIn],[TimeOut],[Status]) VALUES(5,'Nathan','2018-01-12 08:55:22',NULL,'Present');
INSERT INTO myTable([EmpID],[Name],[TimeIn],[TimeOut],[Status]) VALUES(5,'Nathan',NULL,'2018-01-12 17:46:32','Present');
INSERT INTO myTable([EmpID],[Name],[TimeIn],[TimeOut],[Status]) VALUES(5,'Nathan','2018-01-08 08:55:22',NULL,'Present');
INSERT INTO myTable([EmpID],[Name],[TimeIn],[TimeOut],[Status]) VALUES(5,'Nathan',NULL,'2018-01-08 17:46:32','Present');

I am trying to get the number of days employee was present for work in company. This is what I have tried so far

DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = '2018-01-01'
SET @EndDate = '2018-01-31'

SELECT EmpID, COUNT(*) AS DaysWorked
FROM myTable 
WHERE CAST([TimeIn] AS DATE) >= @StartDate AND CAST([TimeIn] AS DATE) <= @EndDate
GROUP BY EmpID

The result I get look like this. but Emmanuel should be working 2 days as he came in the morning and left mid day and then he came back again so that should be counting as one day.

EmpID       DaysWorked
----------- -----------
1           3
2           2
4           2
5           3

Answer 1

This should work for you.

    DECLARE @StartDate datetime
    DECLARE @EndDate datetime
    SET @StartDate = '2018-01-01'
    SET @EndDate = '2018-01-31'

   SELECT EmpId,  COUNT(*) as DaysWorked 
    FROM
    (
      SELECT DISTINCT EmpId,CAST(TimeIn AS DATE) AS [Date]
      FROM myTable
      WHERE TimeIn IS NOT NULL
      AND CAST(TimeIn AS DATE) BETWEEN @StartDate AND @EndDate
    )T
    GROUP BY EmpId
    ORDER BY EmpId

Output :

EmpId   DaysWorked
1         2
2         2
4         2
5         3