Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2004
    Posts
    19

    Unanswered: Query - using a max total

    I've got a real simple database for testing out some queries i want to implement on another database. The table structure on which the query is based upon is as follows:

    Appointments(Start Time - Date/Time (p.k.), Duration - Number, Nature - Text, Pupil_ID - Number (f.k.), Class_year - Text (f.k.))

    I'm having trouble on what should be a dead straightforward query. All I want is to return the appointment whose duration is the highest. When I run the following query below, instead of returning the highest duration it returns all rows as if it was ignoring the max constraint. The query i'm using is:


    SELECT [tblappointments].[Start Time], Max([tblappointments].[Duration (mins)]) AS [MaxOfDuration (mins)], [tblappointments].[Nature], [tblappointments].[Pupil_ID], [tblappointments].[Class_Year]
    FROM tblappointments
    GROUP BY [tblappointments].[Start Time], [tblappointments].[Nature], [tblappointments].[Pupil_ID], [tblappointments].[Class_Year];


    Any Ideas???

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    how about

    SELECT TOP 1 whatever FROM yourtable WHERE yourcriteria ORDER BY Duration DESC

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Apr 2004
    Posts
    19
    no, that just returns the first record in the table!!!

    I want to return the appointment having the highest duration.

  4. #4
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by markb_1984
    no, that just returns the first record in the table!!!

    I want to return the appointment having the highest duration.
    If you include the order by duration in a descending fashion (longest durations first) the first record WILL be the longest duration.

  5. #5
    Join Date
    Apr 2004
    Posts
    19
    yep u're right, i made a school boy mistake, Thanks!!!

  6. #6
    Join Date
    Apr 2004
    Posts
    19
    ok then, my next query which i want to achieve to display all those start times that occur after the current (cpu) time. The start time is of type date/time (specifically a short time). Here's the same table i'm working on:

    Appointments(Start Time - Date/Time (p.k.), Duration - Number, Nature - Text, Pupil_ID - Number (f.k.), Class_year - Text (f.k.))


    Whenever i've tried to make use of the date() function, although it works in returning the current date, it doesn't seem to work in retreiving the current time (seems to keep defaulting to 00:00).

    Ideas on how to perform such a query appreciated!!!

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    use now() instead of date()

    izy
    Last edited by izyrider; 04-18-04 at 12:23.
    currently using SS 2008R2

  8. #8
    Join Date
    Apr 2004
    Posts
    19
    Thanks everyone for the help so far...

    Now comes the query of all queries. Following on from the previous 2 queries i've been working my way towards producing a query which evaluates a list of appointments and returns the appointment which is next.

    I've used a where clause (in conjunction with the TOP 1 and order by as per the previous postings) to determine which appointment is closest to the curent date i.e. WHERE appointment.date >= now()

    That's ok, but if there's several appointments, it returns the appointment with the earliest start_time (separate field). I then made the where caluse into:

    WHERE appointment.date >= now() AND appointment.start_time >= now()

    again this fails (on 2nd clause) if supposing the current time is 10pm and the next apointment is 7am the next day.

    Any advice on what should be in the where clause so that just the next appointment is displayed???

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    now() returns a double holding days-point-time.

    if you want to continue with your two separate felds for date & time you will need to strip off the date for the second criteria. probably TimeValue(now()) will do the job for you, but i have not checked (i know it works with date/time in a string but i'm only guessing that it handles a date-type correctly).

    ?? but why not switch to A's normal date/time and use just the one field for both: you can still display date only or time only using format(), and the rest of your life becomes simpler.


    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Apr 2004
    Posts
    19
    Due to all the dependencies and the amount of work that would be involved with the database i am working with, the date and start time have to be kept separate.

    My problem is more a logic one than a programming technical question. Basically, following on from the 2 previous queries i want to create a query which retrieves the next appointment.

    If I simply add WHERE appointment.date >= now() with top 1 and asc ordering (on date and then start time), that fails as if the current time is after the occurence of one appointment, instead of choosing the next appointment that same day, it just selects the 1st appointment of the day (until the following day).

    I then tried WHERE appointment.date >= now() AND appointment.start_date >= now() but this is also incorrect as although it will deal with the problem described with the 1st attempt, this fails when the end of the day is reached (in this case 9pm is latest time apps can occur) as the time for the next appointment is obviously 'less than' the current time.

    Any thoughts on what i need to do to my where clause so that it fully deals with choosing the next appointment???
    Last edited by markb_1984; 04-20-04 at 20:19.

  11. #11
    Join Date
    Apr 2004
    Posts
    19
    Any chance on receiving some help on this problem, please.

  12. #12
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    Izy is more right than you know. I've billed a lot of hours overcoming crappy database design. But you can do what Izy says with logic if you refuse to rework your database:

    WHERE now()<int(appointment.date)+appointment.time-int(appointment.time)

  13. #13
    Join Date
    Apr 2004
    Posts
    19
    Thanks, that seems to work.

    Unless you hear any more, then its working.

    Getting my programming head off and wearing my SQL hat, I can sometimes forget about methods/functions which can be used in both situations i.e. type conversion.

    Once again thanks everyone for your help!!!

Posting Permissions

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