How can get the the smallest Time value and the second minimum value for each date ?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

I'm trying to display the smallest time value and the second minimum time value, and the biggest date value and the second maximum time value for each date:

SQL

select  max (times_out), min (times_out), (SELECT MAX( times_out )
  FROM Emp_Attendance
 WHERE times_out < ( SELECT MAX( times_out )
                 FROM Emp_Attendance ) ),

				 (SELECT Min( times_out )
  FROM Emp_Attendance
 WHERE times_out > ( SELECT Min( times_out )
                 FROM Emp_Attendance where month (Times_Out) =6 and EmpID=35 )), 

				 min (Times_IN), max(Times_IN),
				 (SELECT Min( Times_IN )
  FROM Emp_Attendance
 WHERE Times_IN > ( SELECT Min( Times_IN )
                 FROM Emp_Attendance where month (Times_Out) =6 and EmpID=35)),
				  (SELECT Max( Times_IN )
  FROM Emp_Attendance
 WHERE Times_IN < ( SELECT Max( Times_IN )
                 FROM Emp_Attendance where month (Times_Out) =6 and EmpID=35)),
				 CAST(_Date AS DATE) as DateAttendance





				 from Emp_Attendance  where month (Times_Out) =6 and EmpID=35
				 group by CAST(_Date AS DATE)

for the min and max it display a right values but for the others it displays the same values for all the dates.
Any solution please§

Posted 9-Jun-15 4:56am

Leila Toumi

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Accepted Answer · 2015-06-09T05:36:00

Solution 1

It's not entirely clear what output you're trying to get. Would something like this help?

SQL

WITH cte As
(
    SELECT
        EmpID,
        CAST(_Date As date) As DateAttendance,
        Times_In,
        Times_Out,
        ROW_NUMBER() OVER (PARTITION BY EmpID, CAST(_Date As date) ORDER BY Times_In) As RNI,
        ROW_NUMBER() OVER (PARTITION BY EmpID, CAST(_Date As date) ORDER BY Times_Out DESC) As RNO
    FROM
        dbo.Emp_Attendance
    WHERE
        Month(Times_Out) = 6
    And
        EmpID = 35
)
SELECT
    EmpID,
    DateAttendance,
    Max(CASE WHEN RNI = 1 THEN Times_In ELSE Null END) As FirstTimeIn,
    Max(CASE WHEN RNI = 2 THEN Times_In ELSE Null END) As SecondTimeIn,
    Max(CASE WHEN RNO = 2 THEN Times_Out ELSE Null END) As SecondLastTimeOut,
    Max(CASE WHEN RNO = 1 THEN Times_Out ELSE Null END) As LastTimeOut
FROM
    cteRawData
GROUP BY
    EmpID,
    DateAttendance
;

Posted 9-Jun-15 5:36am

Richard Deeming

Comments

Leila Toumi 9-Jun-15 12:23pm

thank you very much :) it works successfully that's what I want to get :)

Leila Toumi 9-Jun-15 13:26pm

please what if I want to display data from 2 tables (Emp_Attendance and Emp_permissionrequest), i try this sql request but i get a wrong result:

WITH per As
(
SELECT
Emp_PermissionsRequest.EmployeeID,
Emp_Attendance.EmpID,
CAST(Emp_PermissionsRequest._Date As date) As DatePermission,
CAST(Emp_Attendance._Date As date) As DateAttendance,

Emp_PermissionsRequest.StartTime,
Emp_PermissionsRequest.EndTime,
Emp_Attendance.times_IN,
Emp_Attendance.Times_Out,
ROW_NUMBER() OVER (PARTITION BY Emp_PermissionsRequest.EmployeeID, CAST(Emp_PermissionsRequest._Date As date) ORDER BY StartTime) As RNI,
ROW_NUMBER() OVER (PARTITION BY Emp_PermissionsRequest.EmployeeID, CAST(Emp_PermissionsRequest._Date As date) ORDER BY EndTime DESC) As RNO,
ROW_NUMBER() OVER (PARTITION BY Emp_Attendance.EmpID, CAST(Emp_Attendance._Date As date) ORDER BY Times_In) As RNIn,
ROW_NUMBER() OVER (PARTITION BY Emp_Attendance.EmpID, CAST(Emp_Attendance._Date As date) ORDER BY Times_Out DESC) As RNOut
FROM

MST_Employee
inner join Emp_Attendance on Emp_Attendance.EmpID=MST_Employee.ID
inner join Emp_PermissionsRequest on Emp_PermissionsRequest.EmployeeID=MST_Employee.ID

-- dbo.Emp_PermissionsRequest
WHERE
Month(Emp_Attendance._Date) = 6
And
EmployeeID = 35
)
SELECT
EmployeeID,--DateAttendance,
DatePermission,
Max(CASE WHEN RNI = 1 THEN StartTime ELSE Null END) As FirstStartTime,
Max(CASE WHEN RNI = 2 THEN StartTime ELSE Null END) As SecondStartTime,
Max(CASE WHEN RNI = 3 THEN StartTime ELSE Null END) As ThirdStartTime,
Max(CASE WHEN RNI = 4 THEN StartTime ELSE Null END) As LastStartTime,

Max(CASE WHEN RNO = 4 THEN EndTime ELSE Null END) As FirstEndTime,
Max(CASE WHEN RNO = 3 THEN EndTime ELSE Null END) As SeconEndTime,
Max(CASE WHEN RNO = 2 THEN EndTime ELSE Null END) As ThirdEndTime,
Max(CASE WHEN RNO = 1 THEN EndTime ELSE Null END) As LastEndTime,

Max(CASE WHEN RNIn = 1 THEN Times_IN ELSE Null END) As FirstTimesIn,
Max(CASE WHEN RNIn = 2 THEN Times_IN ELSE Null END) As SecondTimesIn,
Max(CASE WHEN RNIn = 3 THEN Times_IN ELSE Null END) As ThirdTimeIN,
Max(CASE WHEN RNIn = 4 THEN Times_IN ELSE Null END) As LastTimeIn,

Max(CASE WHEN RNOut = 4 THEN Times_Out ELSE Null END) As FirstEndTime,
Max(CASE WHEN RNOut = 3 THEN Times_Out ELSE Null END) As SeconEndTime,
Max(CASE WHEN RNOut = 2 THEN Times_Out ELSE Null END) As ThirdEndTime,
Max(CASE WHEN RNOut = 1 THEN Times_Out ELSE Null END) As LastEndTime

FROM
per
GROUP BY
EmployeeID,
DatePermission --DateAttendance-- ,
;

Richard Deeming 9-Jun-15 13:53pm

There's nothing obviously wrong with your query. Can you reproduce the problem with some dummy data in a SQL Fiddle[^]?