How to create daily attendance report in login time, logout time, total hours, total logs

Question

2.00/5 (2 votes)

See more:

C#

i want to show daily attendance::: 

Iam Create table name tblattendance

Create 3 coulmns: 
OrgId
UserId
DateOfTransaction

inserted data :
orgid  useid   DateOfTransaction
1       1        06-02-2016:08:12
1       2        06-02-2016:08:20
1       3        06-02-2016:08:30
1       1        06-02-2016:15:43
1       1        06-02-2016:15:45
1       2        06-02-2016:15:43
 
evng time only 1 and 3 userids only punch but not punch userid 2

i want to show daily attendance output 

Userid   Date         Login Time   Logout Time	  Total Hours	    Total Logs
1        06-02-2016   08:12        15:45           7 hours 12 mins   3
2        06-02-2016   08:20        15:43           7 hours 10 mins   2
3        06-02-2016   08:30          -                 -             1
2

What I have tried:

i know only show
Select * from tblattendance where tblattendance=06-02-2016
this code to only show
orgid useid DateOfTransaction
1 1 06-02-2016:08:12
1 2 06-02-2016:08:20
.........

i want to show ogin Time, Logout Time, Total Hours, Total logs

Posted 6-Feb-16 4:00am

prabhakarcs

Add a Solution

Comments

BillWoodruff 6-Feb-16 11:07am

How familiar are you with Linq ? You are working in VB or C# ?

F-ES Sitecore 8-Feb-16 4:28am

This is obviously homework, so if it's SQL homework I doubt they'll accept a code-based answer :)

prabhakarcs 6-Feb-16 11:32am

working with C# sir..

please give me solution sir...

BillWoodruff 6-Feb-16 23:34pm

Using language like "give me" is not appropriate for this forum.

We're here to help you learn how to do things for yourself.

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)

BillWoodruff · Answer 1 · 2016-02-06T05:46:00

I'm going to help you get started by showing you how to use Linq to create a collection of DataRows organized by UserID:

C#

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

// in some Method in some Class in some NameSpace
var userIDGroups = dt.Rows.Cast<datarow>()
    .GroupBy(row => row[1])
    .Select(grp => grp.ToList()).ToList();

foreach (var grp in userIDGroups)
{
    Console.WriteLine("UserID: {0}", grp[0].ItemArray[1]);

    foreach (var row in grp)
    {
        Console.WriteLine("Date: {0}", row.ItemArray[2]);
    }
}

This code:

a. converts the DataRowCollecton to an IEnumerable so we can use Linq on it.

b. then it uses 'GroupBy to get a set of 'Groups (IEnumerables of Rows ... a 'LookUp object, actually), selected by the 'UserID field ... field #2 in the Row

c. then we select each group/LookUp, and turn it into a List of DataRows

d. finally, we turn this IEnumerable of Lists of DataRows into a List.

The result of executing this on your DataTable will look like this:

UserID: 1
Date: 2/6/2016 8:12:00 AM
Date: 2/6/2016 3:43:00 PM
Date: 2/6/2016 3:45:00 PM
UserID: 2
Date: 2/6/2016 8:20:00 AM
Date: 2/6/2016 3:43:00 PM
UserID: 3
Date: 2/6/2016 8:30:00 AM

If you study this code carefully you can see how you can access any field in the rows organized by UserId; you can then get the Min and Max Dates in each group and calculate time-elapsed, etc.