DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Query: Find the first result of one day

  1. #1
    Join Date
    Sep 2010
    Posts
    2

    Query: Find the first result of one day

    Hi all,

    First time poster needed some help with a query.

    I have a query which displays the time each of my students logged in on a selected given date, the problem is that sometimes they will change computer and they will have two login times.

    such as:

    g smith 20/08/2010 11:00
    g smith 20/08/2010 1130

    For this I thought a simple MIN function would solve the problem but the query still returns both results, here is my try:

    SELECT [ADEReport].[dbo].[User].[JID]

    ,MIN(CONVERT(FLOAT, ChangeTime, 114)) as TimeLoggedIn


    FROM [ADEReport].[dbo].[PresenceChange]JOIN [ADEReport].[dbo]. [User]
    ON [ADEReport].[dbo].[PresenceChange].[UID] = [ADEReport].[dbo].[User].[UID]

    AND CONVERT(CHAR(10),ChangeTime,120) = '2010-08-20'
    AND PresenceStatus = 'online'
    group by ChangeTime,JID
    order by JID asc


    I thought converting to a float my help but still no luck.

    Any ideas where i'm going wrong?

  2. #2
    Join Date
    Sep 2010
    Posts
    2
    Just to make it clear I would like to display only the first result of the user hence the use of the MIN function.

  3. #3
    Join Date
    Jul 2007
    Location
    Minnesota
    Posts
    155
    Your query would return every record just as it is written.

    If you use a criteria for the selection, say the student ID, you can then use SELECT TOP 1 MIN(Date) with your query but what you have is filtering by date in which case, all the records will come up.

    If you know student ID, you may also try SELECT DISTINCT (Student ID) and then the rest will follow.

    Try those scenarios and see if it will be a good idea. It is kind of vague the way you have it.

    Emefa

  4. #4
    Join Date
    Oct 2008
    Posts
    142
    maybe:

    ---------------------------------------------------------------------

    SELECT top 1 [ADEReport].[dbo].[User].[JID]

    ,CONVERT(FLOAT, ChangeTime, 114) as TimeLoggedIn


    FROM [ADEReport].[dbo].[PresenceChange]JOIN [ADEReport].[dbo]. [User]
    ON [ADEReport].[dbo].[PresenceChange].[UID] = [ADEReport].[dbo].[User].[UID]

    AND CONVERT(CHAR(10),ChangeTime,120) = '2010-08-20'
    AND PresenceStatus = 'online'
    group by ChangeTime,JID
    order by ChangeTime desc

    ---------------------------------------------------------------------

    this may work because you select the top 1 of ChangeTime with earliest timestamp first.

    let me know if this works out for you well.

    good luck,

    tonci korsano

  5. #5
    Join Date
    Oct 2008
    Posts
    142

    correction...

    this would be clearer:

    ---------------------------------------------------------------------------

    SELECT top 1 [ADEReport].[dbo].[User].[JID]

    ,CONVERT(FLOAT, ChangeTime, 114) as TimeLoggedIn


    FROM [ADEReport].[dbo].[PresenceChange]JOIN [ADEReport].[dbo]. [User]
    ON [ADEReport].[dbo].[PresenceChange].[UID] = [ADEReport].[dbo].[User].[UID]

    AND CONVERT(CHAR(10),ChangeTime,120) = '2010-08-20'
    AND PresenceStatus = 'online'
    order by ChangeTime desc


    --------------------------------------------------------------------------

    good luck!

    tonci korsano

Similar Threads

  1. Replies: 1
    Last Post: 04-08-2003, 09:55 AM
  2. Multiply de result of a query
    By Ricardo in forum Database
    Replies: 2
    Last Post: 07-22-2002, 04:33 AM
  3. Query to find totals by date
    By Ken in forum Database
    Replies: 3
    Last Post: 04-06-2001, 09:31 PM
  4. Problems with NetAddUser
    By Jeff Morgan in forum VB Classic
    Replies: 0
    Last Post: 02-11-2001, 01:54 PM
  5. totalling Columns from a Query result
    By Paul M in forum ASP.NET
    Replies: 1
    Last Post: 11-29-2000, 12:54 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center