How do in take First In Last out time data and display in asp.net c#

Question

0.00/5 (No votes)

See more:

Hi,

I have the below table with columns.
aDate aTime EmpCode EmpName aDateTime
27-Dec-11 9:31 32 Amarnath KP 12/27/11 9:31
27-Dec-11 9:42 32 Amarnath KP 12/27/11 9:42
27-Dec-11 10:01 32 Amarnath KP 12/27/11 10:01
27-Dec-11 14:10 32 Amarnath KP 12/27/11 14:10
27-Dec-11 14:11 32 Amarnath KP 12/27/11 14:11
27-Dec-11 14:13 32 Amarnath KP 12/27/11 14:13
27-Dec-11 14:46 32 Amarnath KP 12/27/11 14:46
27-Dec-11 20:46 32 Amarnath KP 12/27/11 20:46

I want to display the records like below(Parameters From Date and To date)

EmpCode FirstIN LastOUT

32 9.31 20.46

Thanks

Posted 18-Nov-14 18:00pm

v2vinoth

Add a Solution

2 solutions

Solution 1

It is Not Possible in one Query..

our have to take two query for getting your output..

In a first query just sort FirstIN Column in ascending order ...

In Second query just sort LastOUT Column in Descending order....

For Example.

SQL

Select EmpCode,FirstIN from tblName order by FirstIN asc
Select EmpCode,LastOUT from tblName order by LastOUT desc

Thanks
AARIF SHAIKH

Posted 18-Nov-14 18:14pm

aarif moh shaikh

Comments

v2vinoth 19-Nov-14 1:16am

I want to display the same in Grid. If I split this how can I show in front end.

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)

Dusara Maulik · Accepted Answer · 2014-11-18T19:55:00

Hi,

You need to use self join with this table as below:

SELECT EmpIN.EmpCode,
       EmpIn.EmpName,
       EmpIn.FirstIN,
       EmpOut.LastOut
FROM   (SELECT   EmpCode,
                 EmpName,
                 aDate,
                 MIN(aTime) AS [FirstIN]
        FROM     tblINOUT
        GROUP BY EmpCode,EmpName, aDate) AS EmpIN
       LEFT OUTER JOIN
       (SELECT   EmpCode,
                 EmpName,
                 aDate,
                 MAX(aTime) AS [LastOut]
        FROM     tblINOUT
        GROUP BY EmpCode,,EmpName, aDate) AS EmpOUT
       ON EmpIN.EmpCode = EmpOUT.EmpCode
          AND EmpIN.aDate = EmpOUT.aDate;