newbie query help

hello all,i am developing an application which displays available pcs in our lab.all activites are stored in a sql server table.Sql server 2000

Table structure is field name datatype null values
logon varchar(6) canbe null (values = login or logoff)
username varchar(20) canbe null (eg user01,B023)
computername varchar(20) canbenull (if its in lab1 name will be lab1-01,lab1-02..,if its in lab2 name will be lab2-01,lab2-02.)
logontime datetime(8) canbenull (26/11/2002 11:17:52)

table sample is

Logon computername username logontime



Login lab1-01 x 2003-07-09 11:17:00.000

Login lab1-03 y 2003-07-09 11:19:00.000

Logoff lab1-01 x 2003-07-09 11:20:00.000

with these details is it possible to see currently how many machines r occupied in each lab.if so how?i am planning to write an asp page which will display currently available machines,so tat user can go to particular lab .i am a sql newbie .if any 1 can guide me tat wuld be helpful for me .thx in advance

-regards
aravindas far as i can see (im finding it hard to understand exactly what fields will be in your database) all you should need to extract the info is something along the lines of:

select from tableA computername
where (logon = ' ')

presuming the table is called 'tableA'

you might be better off having another field of type boolean which simply shows whether anyone is logged into the machine, maybe 0 for vacant or 1 for in-use.

hope this helps|||assume that a computer is "occupied" when the latest logon for it is "Login"

this query will list the individual machines:
select computername
, logontime as LastLoginTime
from yourtable as TT
where logontime
= ( select max(logontime)
from yourtable
where logontime = TT.logontime )
and logon = 'Login'
order
by computername
this query will count them by lab and list them in order by the fewest occupied to the most:
select left(computername,4) as Lab
, count(*) as OccupiedCount
from yourtable as TT
where logontime
= ( select max(logontime)
from yourtable
where logontime = TT.logontime )
and logon = 'Login'
group
by left(computername,4)
order
by 2
rudy|||How can you tell if they are occupied if all you have is the last logon time? You need a field that indicates their logoff time as well. With that, you can not only tell which machines are occupied (at least one associated logon has a null logoff time), but also other valuable information such as usage patterns.

blindman|||blindman, the logoffs are there, as separate rows

that's why my query checks for when the latest logon for the machine is "Login"

:)|||hello all thx for ur replies ,i didt read ur query.i created a new table with computername,status (which can be login or logoff) ,when old table gets inserted it triggers an event which sets the status of particular machine in new table .now i can query the new table to see the availability...is it a good practice? its my first sql prg .gimme some inputs

-regards
aravind|||Ahh...I see your logoff rows now. I've worked tables that way before, but I prefer storing logoffs as a separate field rather than a separate record. That way each row represents a single session and it makes calculations easier. By using a separate field you don't need the Max subquery, and finding the total amount of time for a particular user across sessions is as simple as sum(LogoffTime - LogonTime).

blindman