Select Column for each day

Question

4.00/5 (1 vote)

See more:

I am so confused which is the best approach to get employee's (first time in) & (last time out) each day when they have login to our biometrics clock system. This data came from a biometric device RAW text file and i just imported it to my DB, Now i needed to select each employees First IN & LAST OUT everyday..

HTML

Emp_Name	Emp_Time		Emp_Type
Mike		2013-04-01 08:30:48	In
Mike		2013-04-01 08:35:48	In
Mike		2013-04-01 17:25:32	Out
Mike		2013-04-01 17:35:32	Out
Mike		2013-04-02 08:35:48	In
Mike		2013-04-02 08:35:48	In
Mike		2013-04-02 17:45:32	Out
Mike		2013-04-02 17:55:32	Out
Josh		2013-04-01 08:30:48	In
Josh		2013-04-01 08:35:48	In
Josh		2013-04-01 17:25:32	Out
Josh		2013-04-01 17:35:32	Out
Josh		2013-04-02 08:35:48	In
Josh		2013-04-02 08:35:48	In
Josh		2013-04-02 17:45:32	Out
Josh		2013-04-02 17:55:32	Out

I have tried this code

PERL

select distinct
CE.Emp_Name,
(select min(Emp_Time) from Raw_Record As MINCE where MINCE.Emp_Type ='In' and MINCE.Emp_Name=CE.Emp_Name) As MinTime,
(select max(Emp_Time) from Raw_Record As MAXCE where MAXCE.Emp_Type ='Out' and MAXCE.Emp_Name=CE.Emp_name) As MaxTime
from Raw_Record As CE

But it only returns the following:

HTML

NAME    TIME IN                 TIME OUT  

Mike	2013-04-01 08:30:48	2013-04-02 17:55:32
Josh	2013-04-01 08:30:48	2013-04-02 17:55:32

What i needed to produce is somehow like this:

HTML

NAME    TIME IN                 TIME OUT

Mike	4/1/2013 08:30:48	2013-04-01 17:35:32
Mike	2013-04-02 08:35:48	2013-04-02 17:55:32
Josh	2013-04-01 08:30:48	2013-04-01 17:35:32
Josh	2013-04-02 08:35:48	2013-04-02 17:55:32

Posted 6-Apr-13 6:38am

JMAM

Updated 6-Apr-13 6:56am

v3

Add a Solution

Comments

vinodkumarnie 6-Apr-13 13:19pm

Nice one..

JMAM 6-Apr-13 13:20pm

Thanks, Do you have an idea how to solve this?

3 solutions

Solution 1

My first response was to replace the use of Raw_Record in your query with the following SELECT statement:

SQL

SELECT Emp_Name, CONVERT(Date, Emp_Time) AS Emp_Date, Emp_Time, Emp_Type
  FROM Raw_Record

The result of this query is:

Emp_Name	Emp_Date	Emp_Time		Emp_Type
Mike		2013-04-01	2013-04-01 08:30:48.000	In 
Mike		2013-04-01	2013-04-01 08:35:48.000	In 
Mike		2013-04-01	2013-04-01 17:25:32.000	Out
Mike		2013-04-01	2013-04-01 17:35:32.000	Out
Mike		2013-04-02	2013-04-02 08:35:48.000	In 
Mike		2013-04-02	2013-04-02 08:35:48.000	In 
Mike		2013-04-02	2013-04-02 17:45:32.000	Out
Mike		2013-04-02	2013-04-02 17:55:32.000	Out
Josh		2013-04-01	2013-04-01 08:30:48.000	In 
Josh		2013-04-01	2013-04-01 08:35:48.000	In 
Josh		2013-04-01	2013-04-01 17:25:32.000	Out
Josh		2013-04-01	2013-04-01 17:35:32.000	Out
Josh		2013-04-02	2013-04-02 08:35:48.000	In 
Josh		2013-04-02	2013-04-02 08:35:48.000	In 
Josh		2013-04-02	2013-04-02 17:45:32.000	Out
Josh		2013-04-02	2013-04-02 17:55:32.000	Out

This way I could include the unique date in the selection and there by getting the maximum and minimum Emp_Time for each unique date.
The resulting query would be:

SQL

SELECT DISTINCT CE.Emp_Name,
       (SELECT MIN(Emp_Time)
          FROM (SELECT Emp_Name, CONVERT(Date, Emp_Time) AS Emp_Date, Emp_Time, Emp_Type
                  FROM Raw_Record) AS MINCE
         WHERE MINCE.Emp_Type ='In' AND MINCE.Emp_Date = CE.Emp_Date AND MINCE.Emp_Name = CE.Emp_Name) AS MinTime,
        (SELECT MAX(Emp_Time)
           FROM (SELECT Emp_Name, CONVERT(Date, Emp_Time) AS Emp_Date, Emp_Time, Emp_Type
                   FROM Raw_Record) AS MINCE
          WHERE MINCE.Emp_Type ='Out' AND MINCE.Emp_Date = CE.Emp_Date AND MINCE.Emp_Name = CE.Emp_Name) AS MaxTime
  FROM (SELECT Emp_Name, CONVERT(Date, Emp_Time) AS Emp_Date, Emp_Time, Emp_Type
          FROM Raw_Record) AS CE

This gives the result you are looking for, but it is not a very efficient and elegant query.

So I started looking for a better way to solve your problem and this is what I came up with:

SQL

SELECT DISTINCT Raw_Record.Emp_Name, Min_In.Emp_Time AS MinTime, Max_Out.Emp_Time AS MaxTime
  FROM Raw_Record

  JOIN (  SELECT Emp_Name, Emp_Type, CONVERT(Date, Emp_Time) AS Emp_Date, Min(Emp_Time) AS Emp_Time
            FROM Raw_Record
           WHERE Emp_Type = 'In'
        GROUP BY Emp_Name, Emp_Type, CONVERT(Date, Emp_Time)) AS Min_In
    ON Min_In.Emp_Name = Raw_Record.Emp_Name AND Min_In.Emp_Date = CONVERT(Date, Raw_Record.Emp_Time)

  JOIN (  SELECT Emp_Name, Emp_Type, CONVERT(Date, Emp_Time) AS Emp_Date, Max(Emp_Time) AS Emp_Time
            FROM Raw_Record
           WHERE Emp_Type = 'Out'
        GROUP BY Emp_Name, Emp_Type, CONVERT(Date, Emp_Time)) AS Max_Out
    ON Max_Out.Emp_Name = Raw_Record.Emp_Name AND Max_Out.Emp_Date = CONVERT(Date, Raw_Record.Emp_Time)

It joins the content of the original table with the results of two sub-queries based on the Emp_Name and unique date (Emp_Date). The first sub-query returns the minimum Emp_Time for the 'In' type and the second the maximum Emp_Time for the 'Out' type.

Posted 6-Apr-13 9:26am

André Kraak

Comments

Maciej Los 6-Apr-13 18:09pm

Interesting solution, +5!

JMAM 6-Apr-13 21:51pm

Thank you for this answer, i will also try this one.

Solution 2

Try this:

SQL

DECLARE @tmp TABLE(Emp_Name VARCHAR(30), Emp_Time DATETIME, Emp_Type VARCHAR(3))

INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Mike', '2013-04-01 08:30:48', 'In')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Mike', '2013-04-01 08:35:48', 'In')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Mike', '2013-04-01 17:25:32', 'Out')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Mike', '2013-04-01 17:35:32', 'Out')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Mike', '2013-04-02 08:35:48', 'In')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Mike', '2013-04-02 08:35:48', 'In')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Mike', '2013-04-02 17:45:32', 'Out')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Mike', '2013-04-02 17:55:32', 'Out')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Josh', '2013-04-01 08:30:48', 'In')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Josh', '2013-04-01 08:35:48', 'In')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Josh', '2013-04-01 17:25:32', 'Out')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Josh', '2013-04-01 17:35:32', 'Out')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Josh', '2013-04-02 08:35:48', 'In')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Josh', '2013-04-02 08:35:48', 'In')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Josh', '2013-04-02 17:45:32', 'Out')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Josh', '2013-04-02 17:55:32', 'Out')

SELECT t3.RowNo, t3.Emp_Name, t3.DateInOut, t3.Time_In, t4.Time_Out
FROM (
	SELECT ROW_NUMBER() OVER (ORDER BY Emp_Name, Time_In) AS RowNo, t1.*
	FROM(
		SELECT Emp_Name, CONVERT(VARCHAR(10), Emp_Time,121) AS DateInOut, MIN(Emp_Time) AS Time_In
		FROM @tmp
		WHERE Emp_Type = 'In'
		GROUP BY Emp_Name, CONVERT(VARCHAR(10), Emp_Time,121)
	) AS t1) AS t3 INNER JOIN (
				SELECT ROW_NUMBER() OVER (ORDER BY Emp_Name, Time_Out) AS RowNo, t2.*
				FROM(
					SELECT Emp_Name, CONVERT(VARCHAR(10), Emp_Time,121) AS DateInOut, MAX(Emp_Time) AS Time_Out
					FROM @tmp
					WHERE Emp_Type = 'Out'
					GROUP BY Emp_Name, CONVERT(VARCHAR(10), Emp_Time,121)
				) AS t2) AS t4 ON t3.RowNo = t4.RowNo

Returned values:

R.  Emp_N.  DateInOut   Time_In                 Time_Out
1   Josh    2013-04-01  2013-04-01 08:30:48.000 2013-04-01 17:35:32.000
2   Josh    2013-04-02  2013-04-02 08:35:48.000 2013-04-02 17:55:32.000
3   Mike    2013-04-01  2013-04-01 08:30:48.000 2013-04-01 17:35:32.000
4   Mike    2013-04-02  2013-04-02 08:35:48.000 2013-04-02 17:55:32.000

You can extract only time from Time_In and Time_Out. To do it, use CAST or CONVERT function[^].

Posted 6-Apr-13 9:33am

Maciej Los

Comments

JMAM 6-Apr-13 21:50pm

Thank you for this answer, i will also try this one.

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mike Meinz · Accepted Answer · 2013-04-06T13:13:00

Here is a query that is a simple extension of the original query that I put in the solution to your original question on 9 March 2013. I just added a check for the date to the two subquery Where clauses.

SQL

select distinct
CE.Emp_Name,
(select min(Emp_Time) from Raw_Record As MINCE where CAST(MINCE.emp_time as DATE)=CAST(CE.emp_time As DATE) AND MINCE.Emp_Type ='In' and MINCE.Emp_Name=CE.Emp_Name) As MinTime,
(select max(Emp_Time) from Raw_Record As MAXCE where CAST(MAXCE.emp_time as DATE)=CAST(CE.emp_time As DATE) AND MAXCE.Emp_Type ='Out' and MAXCE.Emp_Name=CE.Emp_name) As MaxTime
from Raw_record As CE

Tested on SQL Server Express 2012

P.S. Don't forget to have a clustered index to improve performance.

Select Column for each day

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0

Select Column for each day

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us