Problem with GROUP BY syntax and expression

I am really struggling with this code and would appreciate knowing how
to group by the expression (constants) in the SELECT clause:
DECLARE @.LO INT
DECLARE @.HI INT
DECLARE @.StartDate varchar(10)
DECLARE @.EndDate varchar(10)
SELECT @.StartDate = '01/01/2005'
SELECT @.EndDate = '06/30/2005'
SELECT @.LO = 250
SELECT @.HI = 333
SELECT
StateCD
, CountyCD
, Zip
, Z.CityName
, Z.StateCode
, Z.CountyName
, 'Criteria' = 'JumboRange:' + Convert(varchar(4),@.LO) + '-' +
Convert(varchar(4),@.HI)
, 'StartingDate' = @.StartDate
, 'ThruDate' = @.EndDate
, JumboAmount = SUM(JumboAmount)
, JumboMortgages = SUM(JumboMortgages)
, JumboFIXMortgages = SUM(JumboFIXMortgages)
, JumboFIXAmount = SUM(JumboFIXAmount)
, JumboARMMortgages = SUM(JumboARMMortgages)
, JumboARMAmount = SUM(JumboARMAmount)
FROM LoanDetails T INNER JOIN dbo.ZipCodesPreferred Z
ON T.StateCD = Z.FIPS_State AND T.CountyCD = Z.FIPS_County AND T.Zip =
Z.ZipCode
GROUP BY
StateCD
, CountyCD
, Zip
, Z.CityName
, Z.StateCode
, Z.CountyName
, 'Criteria' = 'JumboRange:' + Convert(varchar(4),@.LO) + '-' +
Convert(varchar(4),@.HI)
, 'StartingDate' = @.StartDate
, 'ThruDate' = @.EndDateRemove the aliases from the GROUP BY.
GROUP BY
StateCD
,CountyCD
, Zip
, Z.CityName
, Z.StateCode
, Z.CountyName
,'JumboRange:' + Convert(varchar(4),@.LO) + '-' + Convert(varchar(4),@.HI)
,@.StartDate
,@.EndDate
"JJA" <johna@.cbmiweb.com> wrote in message
news:1123780031.845579.256660@.o13g2000cwo.googlegroups.com...
> I am really struggling with this code and would appreciate knowing how
> to group by the expression (constants) in the SELECT clause:
> DECLARE @.LO INT
> DECLARE @.HI INT
> DECLARE @.StartDate varchar(10)
> DECLARE @.EndDate varchar(10)
> SELECT @.StartDate = '01/01/2005'
> SELECT @.EndDate = '06/30/2005'
> SELECT @.LO = 250
> SELECT @.HI = 333
> SELECT
> StateCD
> , CountyCD
> , Zip
> , Z.CityName
> , Z.StateCode
> , Z.CountyName
> , 'Criteria' = 'JumboRange:' + Convert(varchar(4),@.LO) + '-' +
> Convert(varchar(4),@.HI)
> , 'StartingDate' = @.StartDate
> , 'ThruDate' = @.EndDate
> , JumboAmount = SUM(JumboAmount)
> , JumboMortgages = SUM(JumboMortgages)
> , JumboFIXMortgages = SUM(JumboFIXMortgages)
> , JumboFIXAmount = SUM(JumboFIXAmount)
> , JumboARMMortgages = SUM(JumboARMMortgages)
> , JumboARMAmount = SUM(JumboARMAmount)
> FROM LoanDetails T INNER JOIN dbo.ZipCodesPreferred Z
> ON T.StateCD = Z.FIPS_State AND T.CountyCD = Z.FIPS_County AND T.Zip =
> Z.ZipCode
> GROUP BY
> StateCD
> , CountyCD
> , Zip
> , Z.CityName
> , Z.StateCode
> , Z.CountyName
> , 'Criteria' = 'JumboRange:' + Convert(varchar(4),@.LO) + '-' +
> Convert(varchar(4),@.HI)
> , 'StartingDate' = @.StartDate
> , 'ThruDate' = @.EndDate
>|||I would use a table expression, like this:
create table t(i int, d money)
insert into t values(1, 1.00)
insert into t values(1, 2.00)
insert into t values(2, 3.00)
insert into t values(2, 4.00)
select t.*, 'Criteria' = 'Some text here'
from (select i, sum(d) sumd from t group by i) t
drop table t
Besides, what's the point of grouping by both CountyCD and CountyName?
If you group by only by StateCD and CountyCD, the query might run much
faster. The rest columns could be retrieved after grouping by, like
this:
create table t(i int, d money)
insert into t values(1, 1.00)
insert into t values(1, 2.00)
insert into t values(2, 3.00)
insert into t values(2, 4.00)
create table s(i int, sname char(5))
insert into s values(1,'One')
insert into s values(2,'Two')
select s.sname, t.sumd, 'Criteria' = 'Some text here'
from (select i, sum(d) sumd from t group by i) t
join s on s.i=t.i
sname sumd Criteria
-- -- --
One 3.0000 Some text here
Two 7.0000 Some text here
drop table t
drop table s|||Thank you so much. Great idea...I now have it working per your
suggestion.

Problem with DateAdd & GetDate()

Hi,
I've been struggling trying to figure out this code to no avail. I need to
query a date time field on an external database to give me data if the
Closed_Time has changed within the last day. The Closed_Time field looks
like this 9/1/2005 11:59:00 AM. The code I've been attempting to use is
(Closed_Time > DATEADD(day, - 7, GETDATE()) and when that is executed a
Lexical Element error message is returned. I've searched on the boards for
other variations of this code and none have worked so far.
I'm hoping someone can shed a little light on it for me.
Thanks.What error message?
Also look up DATEDIFF in Books Online, if you haven't already.
ML
http://milambda.blogspot.com/|||Whats the exact error message ?
HTH, Jens Suessmeyer.|||ML wrote:
> What error message?
> Also look up DATEDIFF in Books Online, if you haven't already.
>
Why would DATEDIFF be relevant?
--
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.|||> I've been struggling trying to figure out this code to no avail. I need
> to
> query a date time field on an external database to give me data if the
> Closed_Time has changed within the last day. The Closed_Time field looks
> like this 9/1/2005 11:59:00 AM. The code I've been attempting to use is
> (Closed_Time > DATEADD(day, - 7, GETDATE()) and when that is executed a
> Lexical Element error message is returned. I've searched on the boards
> for
> other variations of this code and none have worked so far.
Assume the current date/time is 20060126 11:07:13.003. What does the
evaluation of the expression "DATEADD(day, - 7, GETDATE()" yield? It is
likely not the value you desire. However, your description doesn't match
this query - you said "... has changed within the last day" yet your
expression subtracts 7 days from the current date/time.
At a minimum, the following should help you understand datetime values and
how they should be used.
http://www.karaszi.com/sqlserver/info_datetime.asp
And as everyone else suggested, post the error message - exactly as it
appears. Perhaps it would help to define/identify what "external database"
means - I've never seen sql server return an error message with the text
"lexical element error" in any form.|||Jason wrote:
> Hi,
> I've been struggling trying to figure out this code to no avail. I
> need to query a date time field on an external database to give me
Using OPENQUERY?

> data if the Closed_Time has changed within the last day. The
> Closed_Time field looks like this 9/1/2005 11:59:00 AM. The code
> I've been attempting to use is (Closed_Time > DATEADD(day, - 7,
> GETDATE()) and when that is executed a Lexical Element error message
> is returned. I've searched on the boards for other variations of
> this code and none have worked so far.
> I'm hoping someone can shed a little light on it for me.
>
I suggest asking for help in a group devoted to the type of external
database you are using. It sounds as if that rdbms requires a different
syntax for the DATEADD function than that required by Transact-SQL.
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.|||The exact error message is
ADO Error Driver) Expected lexical element not found :)
I was informed that it could be an ODBC issue. I get data from a company
and I use their own ODBC connection to pull it. We're under the assumption
that we are limited becasue of the connection. With that in mind I just
attempted this Closed_Time > DATEADD(day, -1, 1/26/2006 10:02:25 AM)) and th
e
same error was given. If I'm limited by the ODBC connection, what other way
can I get records that were just updated in the last day.
Thanks for all the help, I appreciate it.
Jason
"Bob Barrows [MVP]" wrote:

> Jason wrote:
> Using OPENQUERY?
>
> I suggest asking for help in a group devoted to the type of external
> database you are using. It sounds as if that rdbms requires a different
> syntax for the DATEADD function than that required by Transact-SQL.
> 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.
>
>|||The query is taking place via a SQL task in a DTS package.
"Bob Barrows [MVP]" wrote:

> Jason wrote:
> Using OPENQUERY?
>
> I suggest asking for help in a group devoted to the type of external
> database you are using. It sounds as if that rdbms requires a different
> syntax for the DATEADD function than that required by Transact-SQL.
> 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.
>
>|||"ODBC" tells us nothing. Is it an Oracle database? Foxpro? MySQL? DB2? (I've
never seen a Jet error message containing the word "Lexical" so I think
Access can be ruled out)
You have to use syntax that works in the external database. We cannot help
you with that without knowing the type of database involved. And even then,
the chances of finding somebody who is knowledgeable about that database in
a SQL Server group are not great.
Could you ask a developer in that "company" to write a query that does what
is needed and send it to you?
Bob Barrows
Jason wrote:
> The exact error message is
> ADO Error Driver) Expected lexical element not found :)
> I was informed that it could be an ODBC issue. I get data from a
> company and I use their own ODBC connection to pull it. We're under
> the assumption that we are limited becasue of the connection. With
> that in mind I just attempted this Closed_Time > DATEADD(day, -1,
> 1/26/2006 10:02:25 AM)) and the same error was given. If I'm limited
> by the ODBC connection, what other way can I get records that were
> just updated in the last day.
> Thanks for all the help, I appreciate it.
> Jason
> "Bob Barrows [MVP]" wrote:
>
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.|||Ok it looks like in order to query that database I need to use this syntax.
(Closed_Time > { ts '2006-01-25 00:00:00' })
Is there then a way for me to store the current date in this format in
another table perhaps and then pass it along to this query or would that fai
l
on me as well? I'm just looking to automate it as much as possible.
Thanks.
"Bob Barrows [MVP]" wrote:

> "ODBC" tells us nothing. Is it an Oracle database? Foxpro? MySQL? DB2? (I'
ve
> never seen a Jet error message containing the word "Lexical" so I think
> Access can be ruled out)
> You have to use syntax that works in the external database. We cannot help
> you with that without knowing the type of database involved. And even then
,
> the chances of finding somebody who is knowledgeable about that database i
n
> a SQL Server group are not great.
> Could you ask a developer in that "company" to write a query that does wha
t
> is needed and send it to you?
> Bob Barrows
> Jason wrote:
> --
> 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.
>
>