SQL Query - Datewise data from single table

Question

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.

Amit Gupta · 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:

Sachin Singh · Answer

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

Amit Gupta · Answer

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 Singh · Answer

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

Riddhi Valecha · Answer

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.

Prashant Malav · Answer

Table Name tblmain select date, count(distinct copiesbooked) as copiesbooked, count(distinct surveycount) as surveycount from tblmain group by date

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

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