MIN - returns nothing?

[TomB]

SELECT MIN(StartTime)
FROM tblLocationBookings
HAVING (MIN(StartTime) > CONVERT(DATETIME, '4/20/2004 10:30:00 AM',
102))

I expect the above to return the next "Booking" after 10:30 am on 4/20/2004.
It returns nothing
There is data in the table; 4 of which are on 4/20/2004 with Start Times
after 10:30. Is there something about smalldatetime, I don't know? Or am I
doing something completely wrong?
The purpose (if you care) is to populate a <select> with all the times
available up to the next booking.


CREATE TABLE [dbo].[tblLocationBookings] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[LocationID] [int] NOT NULL ,
[StartTime] [smalldatetime] NOT NULL ,
[EndTime] [smalldatetime] NULL ,
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BookerID] [int] NULL
) ON [PRIMARY]
GO

Thanks
TomB

[Bob Barrows [MVP]]

TomB wrote:

> SELECT MIN(StartTime)
> FROM tblLocationBookings
> HAVING (MIN(StartTime) > CONVERT(DATETIME, '4/20/2004 10:30:00
> AM', 102))
>
> I expect the above to return the next "Booking" after 10:30 am on
> 4/20/2004.

Let's examine what this query does:

It first calculates the MIN(StartTime). It then compares that calculated
value to '4/20/2004 10:30:00 AM' (the CONVERT is not necessary - but date
literals should be supplied in YYYYMMDD format to avoid ambiguity).

So in this case, the min(StartTime) is likely to be less than the compared
datetime in your HAVING clause, so nothing is returned.

What you really want to do is filter the data used to calculate the min
value BEFORE the min value is calculated. That means you need to use the
WHERE clause:

SELECT MIN(StartTime)
FROM tblLocationBookings
WHERE StartTime > '20040420 10:30:00 AM'


WHERE filters before aggregates are calculated. HAVING filters AFTER
aggregates are calculated. Here is an example where HAVING would be useful.
Say yhou wanted to see booking starttimes by location, but only for
locations whose next booking is after '20040420 10:30:00 AM'. This query:

SELECT LocationID,MIN(StartTime)
FROM tblLocationBookings
GROUP BY LocationID

Might produce these results:
1 2/25/2004 9:30 AM
2 4/21/2004 9:30 AM
3 4/1/2004 10:30 AM
4 3/29/2004 9:00 AM

This modification:
SELECT LocationID,MIN(StartTime)
FROM tblLocationBookings
GROUP BY LocationID
HAVING Min(StartTime) > '20040420 10:30:00 AM'

will yield this result:
2 4/21/2004 9:30 AM

HTH,
Bob Barrows


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[TomB]

Thanks. I had no idea why there even was a Having since it "seemed" to do
the same as Where.

Your explanation, explains why my query was working on some tests but not
others.

Thanks again.

Tom B

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%239baYauJEHA.2776@TK2MSFTNGP12.phx.gbl...

> TomB wrote:

> > SELECT MIN(StartTime)
> > FROM tblLocationBookings
> > HAVING (MIN(StartTime) > CONVERT(DATETIME, '4/20/2004 10:30:00
> > AM', 102))
> >
> > I expect the above to return the next "Booking" after 10:30 am on
> > 4/20/2004.

>
> Let's examine what this query does:
>
> It first calculates the MIN(StartTime). It then compares that calculated
> value to '4/20/2004 10:30:00 AM' (the CONVERT is not necessary - but date
> literals should be supplied in YYYYMMDD format to avoid ambiguity).
>
> So in this case, the min(StartTime) is likely to be less than the compared
> datetime in your HAVING clause, so nothing is returned.
>
> What you really want to do is filter the data used to calculate the min
> value BEFORE the min value is calculated. That means you need to use the
> WHERE clause:
>
> SELECT MIN(StartTime)
> FROM tblLocationBookings
> WHERE StartTime > '20040420 10:30:00 AM'
>
>
> WHERE filters before aggregates are calculated. HAVING filters AFTER
> aggregates are calculated. Here is an example where HAVING would be

useful.

> Say yhou wanted to see booking starttimes by location, but only for
> locations whose next booking is after '20040420 10:30:00 AM'. This query:
>
> SELECT LocationID,MIN(StartTime)
> FROM tblLocationBookings
> GROUP BY LocationID
>
> Might produce these results:
> 1 2/25/2004 9:30 AM
> 2 4/21/2004 9:30 AM
> 3 4/1/2004 10:30 AM
> 4 3/29/2004 9:00 AM
>
> This modification:
> SELECT LocationID,MIN(StartTime)
> FROM tblLocationBookings
> GROUP BY LocationID
> HAVING Min(StartTime) > '20040420 10:30:00 AM'
>
> will yield this result:
> 2 4/21/2004 9:30 AM
>
> HTH,
> Bob Barrows
>
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>