I'm getting strange results from a SQL statement that works - most of the 
time ... any help is appreciated.

Here's the statement

SELECT SUM(Net) AS avgNet, SUM(Gross) AS avgGross, SUM(RNLS) AS avgRNLS, 
SUM(Extn) AS avgExtn, SUM(Pkgs) AS avgPkgs, SUM(PMT) AS avgPMT, SUM(Misc) 
AS avgMisc, SUM(Inet) AS avgInet, DSSDate
FROM DSS
WHERE (DSSDate <= '9/10/2002')
GROUP BY DSSDate

(The problem is apparently in the WHERE clause ...)

Here's what's happening ... I have data in an MS SQL Server database table 
called "DSS" through September 2002, (I somehow think it is important to 
note that there is currently no data in the table beyond September 2002), 
the "DSSDate" column is of type datetime. 

As long as this query is executed using a date in the WHERE clause that is 
9/30/2002 or less ... I get the return I expect with properly performed 
calculations -BUT- if the query is executed using a date that is greater 
than September (where no records should be found) ... example 
10/1/2002 ... instead of no records ... ALL RECORDS ARE RETURNED. The same 
behavior is exhibited whether the query is issued from my ASP page or from 
MS SQL Server's Query Analyzer.

Does anyone know what's wrong here ... is there another way to perform 
comparisons on dates.

Thanks for any help
Chip

If you put the date in as 2002/10/1 does it work properly?

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <cdukes77@b...>
Subject: [asp_databases] Help with a SQL Statement


: I'm getting strange results from a SQL statement that works - most of the
: time ... any help is appreciated.
:
: Here's the statement
:
: SELECT SUM(Net) AS avgNet, SUM(Gross) AS avgGross, SUM(RNLS) AS avgRNLS,
: SUM(Extn) AS avgExtn, SUM(Pkgs) AS avgPkgs, SUM(PMT) AS avgPMT, SUM(Misc)
: AS avgMisc, SUM(Inet) AS avgInet, DSSDate
: FROM DSS
: WHERE (DSSDate <= '9/10/2002')
: GROUP BY DSSDate
:
: (The problem is apparently in the WHERE clause ...)
:
: Here's what's happening ... I have data in an MS SQL Server database table
: called "DSS" through September 2002, (I somehow think it is important to
: note that there is currently no data in the table beyond September 2002),
: the "DSSDate" column is of type datetime.
:
: As long as this query is executed using a date in the WHERE clause that is
: 9/30/2002 or less ... I get the return I expect with properly performed
: calculations -BUT- if the query is executed using a date that is greater
: than September (where no records should be found) ... example
: 10/1/2002 ... instead of no records ... ALL RECORDS ARE RETURNED. The same
: behavior is exhibited whether the query is issued from my ASP page or from
: MS SQL Server's Query Analyzer.
:
: Does anyone know what's wrong here ... is there another way to perform
: comparisons on dates.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Nope - Same result ... good try though.

Chip

> If you put the date in as 2002/10/1 does it work properly?

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <cdukes77@b...>
Subject: [asp_databases] Help with a SQL Statement


: I'm getting strange results from a SQL statement that works - most of the
: time ... any help is appreciated.
:
: Here's the statement
:
: SELECT SUM(Net) AS avgNet, SUM(Gross) AS avgGross, SUM(RNLS) AS avgRNLS,
: SUM(Extn) AS avgExtn, SUM(Pkgs) AS avgPkgs, SUM(PMT) AS avgPMT, SUM(Misc)
: AS avgMisc, SUM(Inet) AS avgInet, DSSDate
: FROM DSS
: WHERE (DSSDate <= '9/10/2002')
: GROUP BY DSSDate
:
: (The problem is apparently in the WHERE clause ...)
:
: Here's what's happening ... I have data in an MS SQL Server database 
table
: called "DSS" through September 2002, (I somehow think it is important to
: note that there is currently no data in the table beyond September 2002),
: the "DSSDate" column is of type datetime.
:
: As long as this query is executed using a date in the WHERE clause that 
is
: 9/30/2002 or less ... I get the return I expect with properly performed
: calculations -BUT- if the query is executed using a date that is greater
: than September (where no records should be found) ... example
: 10/1/2002 ... instead of no records ... ALL RECORDS ARE RETURNED. The 
same
: behavior is exhibited whether the query is issued from my ASP page or 
from
: MS SQL Server's Query Analyzer.
:
: Does anyone know what's wrong here ... is there another way to perform
: comparisons on dates.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Just to clarify:

WHERE DSSDate > '2002/10/1'

doesn't work (with the ')? I know it wont work if you leave out the ' marks,
but I'd be surprised if it also didn't work if you do include the ' marks.

Have you tried using DateDiff() as well? (where DateDiff(...) > 0)

If that still doesn't work, can you send a CREATE TABLE script, plus some
sample dummy data so we can try?

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <cdukes77@b...>
Subject: [asp_databases] Re: Help with a SQL Statement


: Nope - Same result ... good try though.
:
: Chip
:
: > If you put the date in as 2002/10/1 does it work properly?
:
: Cheers
: Ken
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: <cdukes77@b...>
: Subject: [asp_databases] Help with a SQL Statement
:
:
: : I'm getting strange results from a SQL statement that works - most of
the
: : time ... any help is appreciated.
: :
: : Here's the statement
: :
: : SELECT SUM(Net) AS avgNet, SUM(Gross) AS avgGross, SUM(RNLS) AS avgRNLS,
: : SUM(Extn) AS avgExtn, SUM(Pkgs) AS avgPkgs, SUM(PMT) AS avgPMT,
SUM(Misc)
: : AS avgMisc, SUM(Inet) AS avgInet, DSSDate
: : FROM DSS
: : WHERE (DSSDate <= '9/10/2002')
: : GROUP BY DSSDate
: :
: : (The problem is apparently in the WHERE clause ...)
: :
: : Here's what's happening ... I have data in an MS SQL Server database
: table
: : called "DSS" through September 2002, (I somehow think it is important to
: : note that there is currently no data in the table beyond September
2002),
: : the "DSSDate" column is of type datetime.
: :
: : As long as this query is executed using a date in the WHERE clause that
: is
: : 9/30/2002 or less ... I get the return I expect with properly performed
: : calculations -BUT- if the query is executed using a date that is greater
: : than September (where no records should be found) ... example
: : 10/1/2002 ... instead of no records ... ALL RECORDS ARE RETURNED. The
: same
: : behavior is exhibited whether the query is issued from my ASP page or
: from
: : MS SQL Server's Query Analyzer.
: :
: : Does anyone know what's wrong here ... is there another way to perform
: : comparisons on dates.
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
@p2p.wrox.com.

Your last question put me on the right track ... and like all SQL problems 
the answer is obvious (I feel foolish that I didn't see it before I posted 
the question).

Here's the query that works properly all of the time ... I needed a query 
to ISOLATE a month's worth of data once the data went beyond just one 
month.

SELECT SUM(Net) AS avgNet, SUM(Gross) AS avgGross, SUM(RNLS) AS avgRNLS, 
SUM(Extn) AS avgExtn, SUM(Pkgs) AS avgPkgs, SUM(PMT) AS avgPMT, SUM(Misc) 
AS avgMisc, SUM(Inet) AS avgInet, DSSDate
FROM  DSS
WHERE (DSSDate >= '9/1/2002') AND (DSSDate <= '9/30/2002')
GROUP BY DSSDate

I feel really dumb that I didn't see this ... I was so focused on a 
solution with a limited data set that I forgot to look at the bigger 
picture. I guess the lesson to be learned here is not about SQL or ASP but 
rather a lesson about using complete (accurate/real-world) data sets to 
test developmental code. In other words ... I should have taken the time 
to build an honest data environment - before writing the ASP page!!

Thanks for your help.

Chip


> Just to clarify:

WHERE DSSDate > '2002/10/1'

doesn't work (with the ')? I know it wont work if you leave out the ' 
marks,
but I'd be surprised if it also didn't work if you do include the ' marks.

Have you tried using DateDiff() as well? (where DateDiff(...) > 0)

If that still doesn't work, can you send a CREATE TABLE script, plus some
sample dummy data so we can try?

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <cdukes77@b...>
Subject: [asp_databases] Re: Help with a SQL Statement


: Nope - Same result ... good try though.
:
: Chip
:
: > If you put the date in as 2002/10/1 does it work properly?
:
: Cheers
: Ken
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: <cdukes77@b...>
: Subject: [asp_databases] Help with a SQL Statement
:
:
: : I'm getting strange results from a SQL statement that works - most of
the
: : time ... any help is appreciated.
: :
: : Here's the statement
: :
: : SELECT SUM(Net) AS avgNet, SUM(Gross) AS avgGross, SUM(RNLS) AS 
avgRNLS,
: : SUM(Extn) AS avgExtn, SUM(Pkgs) AS avgPkgs, SUM(PMT) AS avgPMT,
SUM(Misc)
: : AS avgMisc, SUM(Inet) AS avgInet, DSSDate
: : FROM DSS
: : WHERE (DSSDate <= '9/10/2002')
: : GROUP BY DSSDate
: :
: : (The problem is apparently in the WHERE clause ...)
: :
: : Here's what's happening ... I have data in an MS SQL Server database
: table
: : called "DSS" through September 2002, (I somehow think it is important 
to
: : note that there is currently no data in the table beyond September
2002),
: : the "DSSDate" column is of type datetime.
: :
: : As long as this query is executed using a date in the WHERE clause that
: is
: : 9/30/2002 or less ... I get the return I expect with properly performed
: : calculations -BUT- if the query is executed using a date that is 
greater
: : than September (where no records should be found) ... example
: : 10/1/2002 ... instead of no records ... ALL RECORDS ARE RETURNED. The
: same
: : behavior is exhibited whether the query is issued from my ASP page or
: from
: : MS SQL Server's Query Analyzer.
: :
: : Does anyone know what's wrong here ... is there another way to perform
: : comparisons on dates.
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
@p2p.wrox.com.