How can Select the first record for each date using sql

Question

0.00/5 (No votes)

See more:

, +

I'm trying to select the average of a list of timesIn for each employee:

My table: AttendanceByDevice

enrollNumber   DateAttendance     TimesInOut         Mode  
40              20-07-2015        08:00:00            0  
40              20-07-2015        10:00:00            1  
40              20-07-2015        12:00:00            0  
40              20-07-2015        17:00:00            1  
40              21-07-2015        08:00:00            0  
40              21-07-2015        11:00:00            1  
40              21-07-2015        13:00:00            0  
40              21-07-2015        18:00:00            1

InOutMode =0 it means CheckIn
InOutMode=1 it means CheckOut
I need to select the average of the firsts TimesInOut(Where Mode=0) for each employee and for each date.
example: the average of TimesInOutfor the employee 40 = aveareg (first TimesInOut of 20-07-2015 and first TimesInOut of 21-07-2015 where Mode=0)

SQL

SELECT convert(varchar(5),CAST(AVG(CAST(TimesInOut AS real) - FLOOR(CAST(TimesInOut as real))) AS datetime),108)
FROM Log_Attendance where  Mode=0 and  EnrollNumber =40

any solution please?

Posted 22-Jul-15 2:59am

Leila Toumi

Updated 22-Jul-15 3:09am

v4

Add a Solution

Comments

Kornfeld Eliyahu Peter 22-Jul-15 9:15am

Learn about SQL's GROUP BY...

2 solutions

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)

Andy Lanng · Answer 1 · 2015-07-22T03:54:00

Hi,

Grouping and aggregates will suit your needs.

Groups work by showing a unique column value but actually the rest of the columns are aggregated in that group. You can use aggregate functions on those columns to show min, the lowest of the aggregate values, max, that highest, avg, average of all aggregates, count to show the number of aggregates and so on...

Some aggregates can only be performed on numeric fields, such as avg, but most other will work on any field type.

Min will work on the string format 08:30:00 but it will work "lexicographically". This means that it will perform a character test starting with the left most character: so as long as they are varchar and not int: "9" will be higher that "10" and "99" will be higher that "10000000" but 09 will be lower that 10.

This being so, it will work on your time format because you have leading zeros:

SQL

Select
    enrollNumber,   --Non aggregate field
    DateAttendance, --Non aggregate field
    Min(TimesInOut) as FirstTimeIn, --aggregate field
From table
Where 
    Mode=0
Group By --anything here is a non aggregate field so you can't use aggregates on them
    enrollNumber, 
    DateAttendance
--Anything not in the group by section MUST have an aggregate function
--If the aggregate rules are not met then you'll get an error complaining about aggregates

Just as an aside: After the Group By you can include the HAVING section. This works like the WHERE section but you can include aggregate fields that you cannot in the WHERE section.

You could have HAVING EnrollNumber = 40 but that would not be efficient. It's best to keep that in the WHERE section so it's excluded before the grouping.

I use it to find duplicates so I might have HAVING count(*) > 1

Hope that helps ^_^

Andy

Wendelius · Answer 2 · 2015-07-22T04:01:00

I take it (and hope) that regardless of the formatting you're storing the date and time in a single datetime column.

Not sure if I understood the question correctly but...

Now to get the minimum mode 0 records for an employee per each day you would group by date and select the minimum, for example

SQL

SELECT MIN(AttendanceDate) 
FROM Log_Attendance
WHERE EnrollNumber = 40
AND   Mode = 0
GROUP BY CAST(AttendanceDate AS date)

As said AttendaceDate should contain both date and time.

This far it's easy but you can't calculate average for dates so that part of the question doesn't quite make sense.

How can Select the first record for each date using sql

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0