0

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
5
Views
7 Years
Discussion Span
Last Post by __avd
0

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 __avd: For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

0

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

Regards,
dsweb1017

0

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
...
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.