sqlserver and asp.net c#

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

[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

[qasimidl]

no its wronge answer absoluty

[dsweb1017]

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

Regards,
dsweb1017

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