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 SThis is a multi-part message in MIME format.
--=_NextPart_000_0BC1_01C6A589.01BE9E50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Are you making this too difficult? Perhaps this will do the same without =error.
SELECT
TermEndDate
, count( Members )
FROM Person_mm_Board
WHERE BoardID =3D @.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 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 =3D @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S > >
--=_NextPart_000_0BC1_01C6A589.01BE9E50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Are you making this too difficult? =Perhaps this will do the same without error.
SELECT TermEndDate , count( Members )FROM =Person_mm_BoardWHERE BoardID =3D @.BoardIDGROUP BY TermEndDateORDER BY =TermEndDate
-- Arnie Rowland* "To be =successful, your heart must accompany your knowledge."
"Ryan"
--=_NextPart_000_0BC1_01C6A589.01BE9E50--|||This is a multi-part message in MIME format.
--=_NextPart_000_0033_01C6A59D.202C71F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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 things to find the source of the problem. The question remains =- Why is the datetime 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 =3D @.BoardID)
GROUP BY TermEndDate
ORDER BY TermEndDate) AS S INNER JOIN Board AS B ON B.BoardID =3D S.BoardID
WHERE S.Members < B.Members "Arnie Rowland" <arnie@.1568.com> wrote in message =news:eVSMvOcpGHA.2400@.TK2MSFTNGP03.phx.gbl...
Are you making this too difficult? Perhaps this will do the same =without error.
SELECT
TermEndDate
, count( Members )
FROM Person_mm_Board
WHERE BoardID =3D @.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 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 =3D @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S > >
--=_NextPart_000_0033_01C6A59D.202C71F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
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 things to find the source of the problem. The question remains - Why is the datetime field not =showing up when used as a subquery? Thanks.
SELECT TOP(1) TermEndDate, MembersFROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) =AS Members, BoardID &n=bsp; FROM =Person_mm_Board AS P &n=bsp; WHERE (BoardID =3D @.BoardID) = =GROUP BY TermEndDate &nbs=p; =; ORDER BY TermEndDate) AS S
INNER JOIN Board AS B
ON B.BoardID =3D S.BoardID
WHERE S.Members < B.Members
"Arnie Rowland" wrote in message =news:eVSMvOcpGHA.2400=@.TK2MSFTNGP03.phx.gbl...
Are you making this too difficult? =Perhaps this will do the same without error.
SELECT TermEndDate , count( Members )FROM Person_mm_BoardWHERE BoardID =3D @.BoardIDGROUP BY =TermEndDateORDER BY TermEndDate
-- Arnie Rowland* "To be =successful, your heart must accompany your knowledge."
"Ryan"
--=_NextPart_000_0033_01C6A59D.202C71F0--|||This is a multi-part message in MIME format.
--=_NextPart_000_004C_01C6A59E.30B9DA20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
OK I cast the datetime field to varchar and then back to datetime in the =main 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 Expr1, COUNT(*) AS Members, BoardID
FROM Person_mm_Board AS P
WHERE (BoardID =3D @.BoardID)
GROUP BY TermEndDate, BoardID
ORDER BY TermEndDate) AS S INNER JOIN
Board AS B ON S.BoardID =3D B.BoardID AND =S.Members < B.NumberMembers
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message =news:OJ3MOdcpGHA.4548@.TK2MSFTNGP03.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 things to find the source of the problem. The question remains =- Why is the datetime 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 =3D @.BoardID)
GROUP BY TermEndDate
ORDER BY TermEndDate) AS S INNER JOIN Board AS B ON B.BoardID =3D S.BoardID
WHERE S.Members < B.Members "Arnie Rowland" <arnie@.1568.com> wrote in message =news:eVSMvOcpGHA.2400@.TK2MSFTNGP03.phx.gbl...
Are you making this too difficult? Perhaps this will do the same =without error.
SELECT
TermEndDate
, count( Members )
FROM Person_mm_Board
WHERE BoardID =3D @.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 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 =3D @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S > >
--=_NextPart_000_004C_01C6A59E.30B9DA20
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
OK I cast the datetime field to varchar =and then back to datetime in the main query and it seems to work. Here's my =final query.
SELECT TOP (1) =S.Members, CAST(S.Expr1 AS datetime) AS TermEndFROM (SELECT TOP (100) PERCENT CAST(TermEndDate AS =Varchar) AS Expr1, COUNT(*) AS Members, BoardID &n=bsp; FROM =Person_mm_Board AS P &n=bsp; WHERE (BoardID =3D @.BoardID) = =GROUP BY TermEndDate, BoardID &n=bsp; ORDER BY TermEndDate) AS S INNER JOIN =; Board AS B ON S.BoardID =3D B.BoardID AND S.Members < B.NumberMembers
"Ryan"
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 things to find the source of the problem. The question remains - Why is the datetime field not =showing up when used as a subquery? Thanks.
SELECT TOP(1) TermEndDate, MembersFROM (SELECT TOP (100) PERCENT TermEndDate, =COUNT(*) AS Members, =BoardID &n=bsp; FROM =Person_mm_Board AS =P &n=bsp; WHERE (BoardID =3D =@.BoardID) = = GROUP BY =TermEndDate &nbs=p; =; ORDER BY TermEndDate) AS S
INNER JOIN Board AS B
ON B.BoardID =3D S.BoardID
WHERE S.Members < B.Members
"Arnie Rowland" wrote in =message news:eVSMvOcpGHA.2400=@.TK2MSFTNGP03.phx.gbl...
Are you making this too difficult? =Perhaps this will do the same without error.
SELECT TermEndDate , count( Members )FROM Person_mm_BoardWHERE BoardID =3D @.BoardIDGROUP BY =TermEndDateORDER BY TermEndDate
-- Arnie Rowland* "To =be successful, your heart must accompany your knowledge."
"Ryan"
--=_NextPart_000_004C_01C6A59E.30B9DA20--|||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|||This is a multi-part message in MIME format.
--=_NextPart_000_0C84_01C6A5D2.9A8AF130
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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. Without 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@.TK2MSFTNGP05.phx.gbl...
OK I cast the datetime field to varchar and then back to datetime in =the main 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 Expr1, COUNT(*) AS Members, BoardID
FROM Person_mm_Board AS P
WHERE (BoardID =3D @.BoardID)
GROUP BY TermEndDate, BoardID
ORDER BY TermEndDate) AS S INNER JOIN
Board AS B ON S.BoardID =3D B.BoardID AND =S.Members < B.NumberMembers
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message =news:OJ3MOdcpGHA.4548@.TK2MSFTNGP03.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 things to find the source of the problem. The question =remains - Why is the datetime 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 =3D @.BoardID)
GROUP BY TermEndDate
ORDER BY TermEndDate) AS S INNER JOIN Board AS B ON B.BoardID =3D S.BoardID
WHERE S.Members < B.Members "Arnie Rowland" <arnie@.1568.com> wrote in message =news:eVSMvOcpGHA.2400@.TK2MSFTNGP03.phx.gbl...
Are you making this too difficult? Perhaps this will do the same =without error.
SELECT
TermEndDate
, count( Members )
FROM Person_mm_Board
WHERE BoardID =3D @.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 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 =3D @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S > >
--=_NextPart_000_0C84_01C6A5D2.9A8AF130
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
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. Without that, we are just playing twenty questions with =you.
-- Arnie Rowland* "To be successful, your heart must =accompany your knowledge."
"Ryan"
OK I cast the datetime field to =varchar and then back to datetime in the main query and it seems to work. Here's =my final query.
SELECT TOP =(1) S.Members, CAST(S.Expr1 AS datetime) AS TermEndFROM (SELECT TOP (100) PERCENT CAST(TermEndDate AS =Varchar) AS Expr1, COUNT(*) AS Members, =BoardID &n=bsp; FROM =Person_mm_Board AS =P &n=bsp; WHERE (BoardID =3D =@.BoardID) = = GROUP BY TermEndDate, =BoardID &n=bsp; ORDER BY TermEndDate) AS S INNER =JOIN =; Board AS B ON S.BoardID =3D B.BoardID AND S.Members < B.NumberMembers
"Ryan"
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 things to find the source =of the problem. The question remains - Why is the datetime field not =showing up when used as a subquery? Thanks.
SELECT TOP(1) TermEndDate, MembersFROM (SELECT TOP (100) PERCENT TermEndDate, =COUNT(*) AS Members, =BoardID &n=bsp; FROM =Person_mm_Board AS =P &n=bsp; WHERE (BoardID =3D =@.BoardID) = = GROUP BY =TermEndDate &nbs=p; =; ORDER BY TermEndDate) AS S
INNER JOIN Board AS B
ON B.BoardID =3D S.BoardID
WHERE S.Members < B.Members
"Arnie Rowland" wrote in =message news:eVSMvOcpGHA.2400=@.TK2MSFTNGP03.phx.gbl...
Are you making this too =difficult? Perhaps this will do the same without error.
SELECT TermEndDate , count( Members )FROM Person_mm_BoardWHERE BoardID =3D @.BoardIDGROUP BY TermEndDateORDER BY TermEndDate
-- Arnie Rowland* "To =be successful, your heart must accompany your =knowledge."
"Ryan"
--=_NextPart_000_0C84_01C6A5D2.9A8AF130--
No comments:
Post a Comment