SQL Server Performance Forum – Threads Archive

Create View[complex specs]

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???

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).

That doesnt cover the second criteria.Thats where my problem really is

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?

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

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())

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.

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

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.

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"

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()))

]]>