Date-time unsupported in subquery?

I have a query that returns 2 fields, a date-time field and a Count(*)
field. Basically it tells me how many people exist for a specific date. I
want to use this as a subquery and test for the earliest date that the
Count(*) field (members) is below a certain number. However, once I use it
in a subquery I get an error on the date-time field that it is an
unsupported data type... any clues?
SELECT TermEndDate, Members
FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Members
FROM Person_mm_Board AS P
WHERE (BoardID = @.BoardID)
GROUP BY TermEndDate
ORDER BY TermEndDate) AS SAre you making this too difficult? Perhaps this will do the same without err
or.
SELECT
TermEndDate
, count( Members )
FROM Person_mm_Board
WHERE BoardID = @.BoardID
GROUP BY TermEndDate
ORDER BY TermEndDate
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.
gbl...
>I have a query that returns 2 fields, a date-time field and a Count(*)
> field. Basically it tells me how many people exist for a specific date.
I
> want to use this as a subquery and test for the earliest date that the
> Count(*) field (members) is below a certain number. However, once I use i
t
> in a subquery I get an error on the date-time field that it is an
> unsupported data type... any clues?
>
> SELECT TermEndDate, Members
> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Member
s
> FROM Person_mm_Board AS P
> WHERE (BoardID = @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S
>
>|||Yes this will do the same thing. I'm not finished with the main query yet.
The end query will look something like below. Just trying to simplify thin
gs to find the source of the problem. The question remains - Why is the dat
etime field not showing up when used as a subquery? Thanks.
SELECT TOP(1) TermEndDate, Members
FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Members,
BoardID
FROM Person_mm_Board AS P
WHERE (BoardID = @.BoardID)
GROUP BY TermEndDate
ORDER BY TermEndDate) AS S
INNER JOIN Board AS B
ON B.BoardID = S.BoardID
WHERE S.Members < B.Members
"Arnie Rowland" <arnie@.1568.com> wrote in message news:eVSMvOcpGHA.2400@.TK2M
SFTNGP03.phx.gbl...
Are you making this too difficult? Perhaps this will do the same without err
or.
SELECT
TermEndDate
, count( Members )
FROM Person_mm_Board
WHERE BoardID = @.BoardID
GROUP BY TermEndDate
ORDER BY TermEndDate
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.
gbl...
>I have a query that returns 2 fields, a date-time field and a Count(*)
> field. Basically it tells me how many people exist for a specific date.
I
> want to use this as a subquery and test for the earliest date that the
> Count(*) field (members) is below a certain number. However, once I use i
t
> in a subquery I get an error on the date-time field that it is an
> unsupported data type... any clues?
>
> SELECT TermEndDate, Members
> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Member
s
> FROM Person_mm_Board AS P
> WHERE (BoardID = @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S
>
>|||OK I cast the datetime field to varchar and then back to datetime in the mai
n query and it seems to work. Here's my final query.
SELECT TOP (1) S.Members, CAST(S.Expr1 AS datetime) AS TermEnd
FROM (SELECT TOP (100) PERCENT CAST(TermEndDate AS Varchar) AS E
xpr1, COUNT(*) AS Members, BoardID
FROM Person_mm_Board AS P
WHERE (BoardID = @.BoardID)
GROUP BY TermEndDate, BoardID
ORDER BY TermEndDate) AS S INNER JOIN
Board AS B ON S.BoardID = B.BoardID AND S.Members < B.NumberMembers
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message news:OJ3MOdcpGHA.4548@.TK2
MSFTNGP03.phx.gbl...
Yes this will do the same thing. I'm not finished with the main query yet.
The end query will look something like below. Just trying to simplify thin
gs to find the source of the problem. The question remains - Why is the dat
etime field not showing up when used as a subquery? Thanks.
SELECT TOP(1) TermEndDate, Members
FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Members,
BoardID
FROM Person_mm_Board AS P
WHERE (BoardID = @.BoardID)
GROUP BY TermEndDate
ORDER BY TermEndDate) AS S
INNER JOIN Board AS B
ON B.BoardID = S.BoardID
WHERE S.Members < B.Members
"Arnie Rowland" <arnie@.1568.com> wrote in message news:eVSMvOcpGHA.2400@.TK2M
SFTNGP03.phx.gbl...
Are you making this too difficult? Perhaps this will do the same without err
or.
SELECT
TermEndDate
, count( Members )
FROM Person_mm_Board
WHERE BoardID = @.BoardID
GROUP BY TermEndDate
ORDER BY TermEndDate
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.
gbl...
>I have a query that returns 2 fields, a date-time field and a Count(*)
> field. Basically it tells me how many people exist for a specific date.
I
> want to use this as a subquery and test for the earliest date that the
> Count(*) field (members) is below a certain number. However, once I use i
t
> in a subquery I get an error on the date-time field that it is an
> unsupported data type... any clues?
>
> SELECT TermEndDate, Members
> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Member
s
> FROM Person_mm_Board AS P
> WHERE (BoardID = @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S
>
>|||Just to clarify:
if you remove the parenthesis around the number 100 so that the code reads
... (SELECT TOP 100 PERCENT ...
your code is good to run.
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.gbl...
>I have a query that returns 2 fields, a date-time field and a Count(*)
>field. Basically it tells me how many people exist for a specific date. I
>want to use this as a subquery and test for the earliest date that the
>Count(*) field (members) is below a certain number. However, once I use it
>in a subquery I get an error on the date-time field that it is an
>unsupported data type... any clues?
> SELECT TermEndDate, Members
> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS
> Members
> FROM Person_mm_Board AS P
> WHERE (BoardID = @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S
>|||On Wed, 12 Jul 2006 09:48:39 -0500, Ryan wrote:

>I have a query that returns 2 fields, a date-time field and a Count(*)
>field. Basically it tells me how many people exist for a specific date. I
>want to use this as a subquery and test for the earliest date that the
>Count(*) field (members) is below a certain number. However, once I use it
>in a subquery I get an error on the date-time field that it is an
>unsupported data type... any clues?
Hi Ryan,
None at all. Datetime columns are allowed in subqueries. It might help
if could post the actual error message (use copy and paste to prevent
transcription errors). I'd also like to see the structure of the table
(posted as a CREATE TABLE statement).

>SELECT TermEndDate, Members
>FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Members
> FROM Person_mm_Board AS P
> WHERE (BoardID = @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S
The ORDER BY and the TOP (100) PERCENT in the subquery are completely
useless. Get rid of them.
If you need the results to be ordered, put an ORDER BY clause on the
outer query:
SELECT TermEndDate, Members
FROM (SELECT TermEndDate, COUNT(*) AS Members
FROM Person_mm_Board AS P
WHERE BoardID = @.BoardID)
GROUP BY TermEndDate) AS S
ORDER BY TermEndDate
Hugo Kornelis, SQL Server MVP|||It still looks like you are making this too difficult.
Datetime fields work just fine in sub-queries.
Are you attempting to locate the next Board Member with term expiring?
Having a bit more detail about what you are working with (table DDL, sample
data, complete problem story) sure would make it easier to assist you. Witho
ut that, we are just playing twenty questions with you.
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message news:OE5bfhcpGHA.756@.TK2M
SFTNGP05.phx.gbl...
OK I cast the datetime field to varchar and then back to datetime in the mai
n query and it seems to work. Here's my final query.
SELECT TOP (1) S.Members, CAST(S.Expr1 AS datetime) AS TermEnd
FROM (SELECT TOP (100) PERCENT CAST(TermEndDate AS Varchar) AS E
xpr1, COUNT(*) AS Members, BoardID
FROM Person_mm_Board AS P
WHERE (BoardID = @.BoardID)
GROUP BY TermEndDate, BoardID
ORDER BY TermEndDate) AS S INNER JOIN
Board AS B ON S.BoardID = B.BoardID AND S.Members < B.NumberMembers
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message news:OJ3MOdcpGHA.4548@.TK2
MSFTNGP03.phx.gbl...
Yes this will do the same thing. I'm not finished with the main query yet.
The end query will look something like below. Just trying to simplify thin
gs to find the source of the problem. The question remains - Why is the dat
etime field not showing up when used as a subquery? Thanks.
SELECT TOP(1) TermEndDate, Members
FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Members,
BoardID
FROM Person_mm_Board AS P
WHERE (BoardID = @.BoardID)
GROUP BY TermEndDate
ORDER BY TermEndDate) AS S
INNER JOIN Board AS B
ON B.BoardID = S.BoardID
WHERE S.Members < B.Members
"Arnie Rowland" <arnie@.1568.com> wrote in message news:eVSMvOcpGHA.2400@.TK2M
SFTNGP03.phx.gbl...
Are you making this too difficult? Perhaps this will do the same without err
or.
SELECT
TermEndDate
, count( Members )
FROM Person_mm_Board
WHERE BoardID = @.BoardID
GROUP BY TermEndDate
ORDER BY TermEndDate
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.
gbl...
>I have a query that returns 2 fields, a date-time field and a Count(*)
> field. Basically it tells me how many people exist for a specific date.
I
> want to use this as a subquery and test for the earliest date that the
> Count(*) field (members) is below a certain number. However, once I use i
t
> in a subquery I get an error on the date-time field that it is an
> unsupported data type... any clues?
>
> SELECT TermEndDate, Members
> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Member
s
> FROM Person_mm_Board AS P
> WHERE (BoardID = @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S
>
>