Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2003
    Location
    chennai,India
    Posts
    4

    Angry Unanswered: 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
    aravind

  2. #2
    Join Date
    Jul 2003
    Posts
    45
    as 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
    If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    assume that a computer is "occupied" when the latest logon for it is "Login"

    this query will list the individual machines:
    Code:
    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:
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    blindman, the logoffs are there, as separate rows

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


  6. #6
    Join Date
    Jul 2003
    Location
    chennai,India
    Posts
    4
    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

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •