Member Avatar for qasimidl

I m using sqlserver there is table name of Attendence
Columns of Tables are

1)EmpCode int
2)AttendenceDate datetime
3)AttendenceTime Datetime
4)status tinyint

Data in table would be like this

Code AttendenceDate AttendenceTime status
1001 3/27/2009 3/27/2009 9:05 1
1001 3/27/2009 3/27/2009 7:05 0

1002 3/28/2009 3/27/2009 10:05 1 //user didnot checkout

1003 3/29/2009 3/27/2009 7:05 1
1003 3/29/2009 3/27/2009 7:05 0

1001 3/30/2009 3/27/2009 7:05 1
1001 3/30/2009 3/27/2009 7:05 0
1003 3/29/2009 3/27/2009 7:05 0 //User didnot checkin

when we find "1" in staus it means its checkin and "0" means Check out
i have to make query to show the result like this

Code date Time in Time out
1001 3/27/2009 9:05 7:05


How to make a perfect and happy query for it
Thanks in advance
waiting Blush
basically i get this data inform of txt file which is generated by machine .where user check in it stores the data and he may go for outdoor work to get late not to come back to check out ...

  • 3 Contributors
  • 4 Replies
  • 103 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by kvprajapati

Dani AI

Generated

The real problem is pairing each check-in with the next check-out for the same employee and day. gave a UI direction and pointed out a CASE-based column trick, but neither provides a reliable pairing. Two practical SQL strategies follow: a modern one (uses LEAD) and a backwards-compatible one (correlated subquery). Both return EmpCode, the day and a TimeIn/TimeOut pair; if checkout is missing the TimeOut will be NULL so the UI can show "not checked out".

Modern (SQL Server 2012+): use window functions to peek at the next row for the same EmpCode/day and take the next timestamp when the current row is a check-in.

WITH ordered AS (
  SELECT EmpCode,
         CAST(AttendenceTime AS date) AS Day,
         AttendenceTime,
         status,
         LEAD(status) OVER (PARTITION BY EmpCode, CAST(AttendenceTime AS date) ORDER BY AttendenceTime) AS next_status,
         LEAD(AttendenceTime) OVER (PARTITION BY EmpCode, CAST(AttendenceTime AS date) ORDER BY AttendenceTime) AS next_time
  FROM Attendance
)
SELECT EmpCode, Day, AttendenceTime AS TimeIn, next_time AS TimeOut
FROM ordered
WHERE status = 1 AND (next_status = 0 OR next_status IS NULL);

Older servers (2005/2008): find the nearest later checkout per check-in with a correlated subquery.

SELECT a.EmpCode,
       CAST(a.AttendenceTime AS date) AS Day,
       a.AttendenceTime AS TimeIn,
       (SELECT MIN(b.AttendenceTime)
        FROM Attendance b
        WHERE b.EmpCode = a.EmpCode
          AND b.status = 0
          AND b.AttendenceTime > a.AttendenceTime
          AND CAST(b.AttendenceTime AS date) = CAST(a.AttendenceTime AS date)
       ) AS TimeOut
FROM Attendance a
WHERE a.status = 1;

Notes: pre-clean the machine TXT (ensure proper datetime types, remove obvious duplicates). Decide business rules for multiple ins/outs and for shifts that cross midnight (group by a shift date, not calendar date). Add an index on (EmpCode, AttendenceTime) to improve performance.

Recommended Answers

All 4 Replies

Member Avatar for dsweb1017

I m using sqlserver there is table name of Attendence
Columns of Tables are

1)EmpCode int
2)AttendenceDate datetime
3)AttendenceTime Datetime
4)status tinyint

Data in table would be like this

Code AttendenceDate AttendenceTime status
1001 3/27/2009 3/27/2009 9:05 1
1001 3/27/2009 3/27/2009 7:05 0

1002 3/28/2009 3/27/2009 10:05 1 //user didnot checkout

1003 3/29/2009 3/27/2009 7:05 1
1003 3/29/2009 3/27/2009 7:05 0

1001 3/30/2009 3/27/2009 7:05 1
1001 3/30/2009 3/27/2009 7:05 0
1003 3/29/2009 3/27/2009 7:05 0 //User didnot checkin

when we find "1" in staus it means its checkin and "0" means Check out
i have to make query to show the result like this

Code date Time in Time out
1001 3/27/2009 9:05 7:05


How to make a perfect and happy query for it
Thanks in advance
waiting Blush
basically i get this data inform of txt file which is generated by machine .where user check in it stores the data and he may go for outdoor work to get late not to come back to check out ...

You can use a Repeater Control and modify the Header Template and Item Templates. Use CSS to layout your html list item elements.

Example:

<asp:Repeater ID="Repeater1" runat="server">
        <HeaderTemplate>
        <ul>
        <li>Code</li>
        <li>Date</li>
        <li>Time In</li>
        <li>Time Out</li>
        </ul>
        </HeaderTemplate>
        <ItemTemplate>
        <ul>
        <li>
         <%# DataBinder.Eval(Container.DataItem, "Code") %></li>
         <li>
         <%# DataBinder.Eval(Container.DataItem, "Date") %></li>
         <li>
         <%# DataBinder.Eval(Container.DataItem, "Time In") %></li>
          <li>
        <%# DataBinder.Eval(Container.DataItem, "Time Out") %></li>
        </ul>
        </ItemTemplate>
        </asp:Repeater>

This will basically give you a table layout with column headers and rows without the use of table tags.

if you need the sql code to retrieve the information above then it will look similar to this:
select EmpCode, AttendenceDate,AttendenceTime,status
From DatabaseName
where status=1

Edited by kvprajapati because: For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.
Member Avatar for qasimidl

no its wronge answer absoluty

Member Avatar for dsweb1017

Sorry I misunderstood your request. What exactly are you looking for? I am not understanding fully by your initial post.

Regards,
dsweb1017

Member Avatar for kvprajapati

Use (MS-SQL) CASE construct.

...
CASE WHEN STATUS=1 THEN AttendenceTime else ' ' END as TimeIn, CASE WHEN STATUS=0 THEN AttendenceTime else ' ' END as TimeOut
...
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.