SQL Server Performance

Create View[complex specs]

Discussion in 'Getting Started' started by superdrog, Jul 1, 2007.

  1. superdrog New Member

    Heres d Question:
    Provide a SQL statement to create a view in Microsoft SQL Server that will return only 1 record displaying the contents of the MEETING_NUMBER field. The rules for the view are as follows:
    • The START_DATE field which contains the start date of the meeting record must contain a value before the current system date.
    •The START_DATE field must be the closest date to the current system date.

    Here is my attempt:
    CREATE VIEW Meeting_View
    AS
    SELECT Meeting.MEETING_NUMBER
    FROM Meeting m1
    WHERE START_DATE < GETDATE()
    AND EXISTS
    (SELECT MAX(START_DATE)
    FROM Meeting m2
    WHERE m1.MEETING_NUMBER = m2.MEETING_NUMBER)

    Can anyone tell me where im goin wrong???
  2. Adriaan New Member

    Something simple like this, perhaps ...

    SELECT TOP 1 m1.MEETING_NUMBER
    FROM Meeting m1
    WHERE m1.START_DATE < GETDATE()
    ORDER BY m1.START_DATE DESC

    The point is that your EXISTS subquery will always find one row that answers the criteria (unless there are no rows in the table yet).
  3. superdrog New Member

    That doesnt cover the second criteria.Thats where my problem really is
  4. Adriaan New Member

    Huh?

    SELECT TOP 1 MeetingNr -- give us the first MeetingNr
    FROM Meeting
    WHERE START_DATE < GETDATE() -- where the start date is before the system date (but not equal to)
    ORDER BY START_DATE DESC -- when sorting on the start_date in descending order

    Not sure how that doesn't give you the most recent startdate. Perhaps your Start_Date column is not a DATETIME column?
  5. Adriaan New Member

    Give us sample data where my query would return the incorrect MeetingNr, and explain why it is the incorrect MeetingNr.
  6. superdrog New Member

    How bout this?

    CREATE VIEW Meeting_View
    AS
    SELECT Meeting.MEETING_NUMBER
    FROM Meeting
    WHERE START_DATE < GETDATE()
    AND NOT EXISTS
    (SELECT MAX(START_DATE)
    FROM Meeting
    WHERE START_DATE < GETDATE())
  7. Adriaan New Member

    You do not seem to understand the concept of EXISTS subqueries.

    When you add this:

    AND NOT EXISTS
    (SELECT MAX(START_DATE)
    FROM Meeting
    WHERE START_DATE < GETDATE())

    ... this means that SQL will look for any row in Meeting where Start_Date is before the system date. If it does not find such a row, then the main query will return all rows according to the rest of the criteria. But if SQL does find a row where Start_Date is before the system data, then the main query does not return any row.

    The MAX() expression is meaningless in an EXISTS subquery - it's the WHERE clause of the subquery that is critical.

    Meanwhile, we're still unclear on what your second criteria is.
  8. superdrog New Member

    Sorry.my second criteria is:
    The START_DATE field must be the closest date to the current system date
  9. Adriaan New Member

    So you want to retrieve only rows where Start_Date is before the current system date (WHERE Start_Date < GETDATE()).

    And you want to order the rows descending by the Start_Date (ORDER BY Start_Date DESC) so that the 'latest' ones, which are closest to the system date, are at the start of the list.

    Finally you ask SQL to show you the MeetingNr from the very first row in that ordered list, which has the start date closest to the system date (SELECT TOP 1).

    ***

    Like I said, show us some data where my query gives you the incorrect MeetingNr, and tell us which one it should return.
  10. superdrog New Member

    I GET WHAT UR SAYING AND TOP 1 WILL WORK BUT I WAS TRYING TO SAY IT LIKE THIS:

    "select a meeting that start earlier than today, provided there doesn't exist a meeting that is later than the meeting selected"
  11. Adriaan New Member

    How would you recognize a meeting that is "later than the meeting selected"? Presumably it would have the later Start_Date, correct? But the query is already showing you the one with the latest Start_Date before the system date.

    Note that GETDATE() includes the time-of-day. Use an expression like this to eliminate the time-of-day:

    DATEADD(d, 0, DATEDIFF(d, 0, GETDATE()))

Share This Page