SQL Query - Datewise data from single table

Riddhi Valecha

5y945

Hello Team,

I have a table with more than 300 records. This table has the timing for each employee for specific days.

EMPCODE	PUNCHDATE	PUNCHTIME	INOUT	NAME
18	02-01-2020	16:31:40	O	NITA
18	02-01-2020	07:15:19	I	NITA
18	03-01-2020	07:15:34	I	NITA
18	03-01-2020	15:31:40	O	NITA
18	04-01-2020	14:48:40	O	NITA
18	04-01-2020	07:15:51	I	NITA
18	06-01-2020	07:13:00	I	NITA
18	06-01-2020	15:31:48	O	NITA
18	07-01-2020	14:46:39	O	NITA
18	07-01-2020	07:16:03	I	NITA
18	08-01-2020	07:15:50	I	NITA
18	08-01-2020	15:32:34	O	NITA
18	09-01-2020	07:13:42	I	NITA
18	09-01-2020	14:47:55	O	NITA
18	10-01-2020	15:31:23	O	NITA

E.g - This is for Employee Code - 18.

From this table, I need to get -

1. FOr specific date say-31-1-2020, min Intime is 07:14:13 and max out time is 15:30:10.

Similarly, I need min Intime and max out time for each day and display in grid.

EmpCode- Name	Date	MinIntime	Maxuttime	Total Hours
18-NITA	31-01-2020	07:14:13	15:30:10	8
18-NITA	30-01-2020	07:16:04	14:46:43	7

How do I get this output?

There are total 2 employees.

EMPCODE	PUNCHDATE	PUNCHTIME	INOUT	NAME
20	02-01-2020	07:34:43	I	NIRMALA
20	02-01-2020	14:55:09	O	NIRMALA
20	03-01-2020	15:30:07	O	NIRMALA
20	03-01-2020	07:34:50	I	NIRMALA
20	06-01-2020	07:24:23	I	NIRMALA
20	06-01-2020	16:06:58	O	NIRMALA
20	07-01-2020	15:12:37	O	NIRMALA
20	07-01-2020	07:37:38	I	NIRMALA
20	08-01-2020	07:36:24	I	NIRMALA
20	08-01-2020	15:57:55	O	NIRMALA
20	09-01-2020	14:45:37	O	NIRMALA
20	09-01-2020	07:32:27	I	NIRMALA
20	10-01-2020	15:32:41	O	NIRMALA
20	10-01-2020	07:26:02	I	NIRMALA

I - Intime ; O - Outtime

Please help to form the query.

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Gupta5y ago
Accepted answer
OfCourse it can be optimized but as of now you can try with CTE
;WITH cte1 AS
(
    SELECT Empcode, PUNCHDATE AS [Date],
    CASE WHEN INOUT = 'I' THEN [PUNCHTIME] END AS INOUT_INTIME,
    CASE WHEN INOUT = 'O' THEN [PUNCHTIME] END AS INOUT_OUTTIME
    FROM PunchTable
), cte2 as
(
    select Empcode, [Date], MIN(INOUT_INTIME) AS INTIME, MAX(INOUT_OUTTIME) AS OUTTIME
    , DATEDIFF(Hour, MIN(INOUT_INTIME), MAX(INOUT_OUTTIME)) as [Hours]
    FROM CTE1
    GROUP BY Empcode, [Date]
)
select Empcode, MIN([Date])AS DATE, MIN(INTIME)AS INTIME, MAX(OUTTIME)AS OUTTIME, [HOURS]--MIN([Hours])AS HOURS
from cte2
--WHERE [DATE] = '2020-01-02'
GROUP BY EMPCODE
, [DATE]
, [HOURS]
ORDER BY EMPCODE
output:
Amit Gupta5y ago
Here's the most optimized query, in addition to @sachin's post
select EmpCode, PunchDate, Name, I as MinTime, O as MaxTime ,
DateDiff(hour,I,O) as TotalHour
from(
SELECT EMPCODE, PUNCHDATE,PUNCHTIME, NAME, INOUT
FROM punchtable
) PIVOTTABLE
pivot
(
    Max(PunchTime)
    for INOUT
    in(I,O)
) as pv
--WHERE PUNCHDATE = '2020-01-02'
Sachin Singh5y ago
If I is always one time entry and O can be multiple then below query will work
select EmpCode,PunchDate,Name,I as MinTime,O as MaxTime , DateDiff(hour,I,O) as TotalHour
from EmpData
pivot (Max(PunchTime) for INOUT in(I,O)) as pv
Riddhi Valecha5y ago
Hello Sir,
Query-
select EmpCode,PunchDate,Name,I as MinTime,O as MaxTime , DateDiff(hour,I,O) as TotalHour
from EmpData
pivot (Min(PunchTime) for INOUT in(I,O)) as pv
is working for few dates, but there are records in which date are same, but INOUT Column as more I and O values.
I am sharing examples-

11 01-01-2020 06:06:31 I Kesar

11 01-01-2020 14:13:22 O Kesar

11 01-01-2020 06:06:25 O Kesar

Here, Min Intime - is 06:06:31 and Max out time is -14:13:22
But, this query is taking outtime as 06:06:25.

Please help in this.
Sachin Singh5y ago
Please test with following query
select EmpCode,PunchDate,Name,I as MinTime,O as MaxTime , DateDiff(hour,I,O) as TotalHour
from EmpData
pivot (Min(PunchTime) for INOUT in(I,O)) as pv
Prashant Malav5y ago
Table Name tblmain select date, count(distinct copiesbooked) as copiesbooked, count(distinct surveycount) as surveycount from tblmain group by date

11	01-01-2020	06:06:31	I	Kesar
11	01-01-2020	14:13:22	O	Kesar
11	01-01-2020	06:06:25	O	Kesar