Max and Where

OmegaSupreme

I'm using max and where in my select statement but the problem is I want the max of all the dates for a given id but only want to return those rows that fall between certain dates. A simplified version of the query is something like this : SELECT MAX(BookingDate.bookingDate) AS MaxDate, MIN(BookingDate.bookingDate) AS MinDate FROM Booking WHERE (Booking.fkChannelId = ? ) AND (BookingDate.bookingDate > ?) AND (BookingDate.bookingDate < ?) But I'd like the max and min dates for all the bookings for a given channel id but only want to return rows that fall within the dates. Life is hard :confused: Try not! Do or do not, there is no try. - Master Yoda

Mike Dimmick

Without knowing your data schema (you refer to two tables Booking and BookingDate in your example, but you don't join them) it's a bit difficult to know how to help. GROUP BY and HAVING clauses may be helpful. GROUP BY allows data to be aggregated across groups, and HAVING allows you to select from the aggregated data. If WHERE applies to the data in the table, HAVING applies to the aggregated data. Stability. What an interesting concept. -- Chris Maunder

WoutL

Just try: SELECT MAX(BookingDate.bookingDate) AS MaxDate, MIN(BookingDate.bookingDate) AS MinDate FROM Booking HAVING (BookingDate.bookingDate > ?) AND (BookingDate.bookingDate < ?) WHERE (Booking.fkChannelId = ? ) Maybe the HAVING part belongs after WHERE... Wout Louwers

OmegaSupreme

Thank you both for your help. I tried adding the HAVING clause instead of WHERE and got the following errors: Column 'BookingDate.bookingDate' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause. Column 'Booking.fkChannelId' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause. The tables I have are as follows each with a one to many relationship with the following table:

Ad
pkAdId
Booking
pkBookingId
fkAdId
BookingDate
pkBookingDateId
fkBookingId
BookingHour
pkBookingHourId
fkBookingDateId

and heres the query as it stands :

SELECT DISTINCT 
		                      Ad.pkAdId, Ad.fileName, Ad.name,
 Ad.length, Ad.lastUpdated, MAX(BookingDate.bookingDate) AS MaxDate, MIN(BookingDate.bookingDate) AS MinDate, 
		                      AVG(BookingHour.bookedSlots + BookingHour.bonusSlots) AS AverageSlots
		FROM         Ad INNER JOIN
		                      Booking ON Ad.pkAdId = Booking.fkAdId INNER JOIN
		                      BookingDate ON Booking.pkBookingId = BookingDate.fkBookingId INNER JOIN
		                      BookingHour ON BookingDate.pkBookingDateId = BookingHour.fkBookingDateId
		
		WHERE  (BookingDate.bookingDate >= @startDate) 
		AND (BookingDate.bookingDate <= @endDate) 
		AND (Booking.fkChannelId = @channelId) AND
		GROUP BY Ad.pkAdId, Ad.fileName, Ad.name, Ad.length, Ad.lastUpdated
		ORDER BY Ad.fileName

Try not! Do or do not, there is no try. - Master Yoda

WoutL

Try: SELECT DISTINCT Ad.pkAdId, Ad.fileName, Ad.name, Ad.length, Ad.lastUpdated, MAX(BookingDate.bookingDate) AS MaxDate, MIN(BookingDate.bookingDate) AS MinDate, AVG(BookingHour.bookedSlots + BookingHour.bonusSlots) AS AverageSlots FROM Ad INNER JOIN Booking ON Ad.pkAdId = Booking.fkAdId INNER JOIN BookingDate ON Booking.pkBookingId = BookingDate.fkBookingId INNER JOIN BookingHour ON BookingDate.pkBookingDateId = BookingHour.fkBookingDateId WHERE (Booking.fkChannelId = @channelId) GROUP BY Ad.pkAdId, Ad.fileName, Ad.name, Ad.length, Ad.lastUpdated Having MinDate >= @startDate) AND MaxDate <= @endDate) ORDER BY Ad.fileName Wout Louwers

OmegaSupreme

Hi Wout, Thanks for your reply, unfortunatley Query Analyser complains : Invalid column name 'MinDate'. Invalid column name 'MaxDate'. Also I want those bookings between the dates not whose max and min booking dates fall between the dates. Sorry to be a pain and thanks again. Try not! Do or do not, there is no try. - Master Yoda

WoutL

SELECT DISTINCT Ad.pkAdId, Ad.fileName, Ad.name, Ad.length, Ad.lastUpdated, MAX(BookingDate.bookingDate) AS MaxDate, MIN(BookingDate.bookingDate) AS MinDate, AVG(BookingHour.bookedSlots + BookingHour.bonusSlots) AS AverageSlots FROM Ad INNER JOIN Booking ON Ad.pkAdId = Booking.fkAdId INNER JOIN BookingDate ON Booking.pkBookingId = BookingDate.fkBookingId INNER JOIN BookingHour ON BookingDate.pkBookingDateId = BookingHour.fkBookingDateId WHERE (Booking.fkChannelId = @channelId) GROUP BY Ad.pkAdId, Ad.fileName, Ad.name, Ad.length, Ad.lastUpdated Having MAX(BookingDate.bookingDate) >= @startDate AND MIN(BookingDate.bookingDate) <= @endDate) ORDER BY Ad.fileName ?? Wout Louwers

OmegaSupreme

Worked like a charm, thank you very much :-D:-D:-D Try not! Do or do not, there is no try. - Master Yoda