Click here to Skip to main content
15,306,142 members
Please Sign up or sign in to vote.
2.00/5 (2 votes)
See more:
i want to show daily attendance::: 

Iam Create table name tblattendance

Create 3 coulmns: 

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

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
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

I'm going to help you get started by showing you how to use Linq to create a collection of DataRows organized by UserID:
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.

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900