Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Using a minus ("-") operator in the WHERE clause

26 views
Skip to first unread message

Jean

unread,
Jul 9, 2007, 10:30:53 AM7/9/07
to
Hello all,

I am trying to solve a query problem in SQL Server 2k.

The WHERE clause of my query contains the following line which
compares the values in two tables:
XRATE.YEAR= HISTORY_DATA.YEAR - 1

The reason for the subtraction ("-1") above is because I want to
return the results for the XRATE where it was 1 year before.

However, I am getting an empty result set. The year field is decimal
in both tables.
When I leave out the subtraction, I do get a result set.

Am I using the correct syntax for comparing the two years?

SQL Menace

unread,
Jul 9, 2007, 10:43:32 AM7/9/07
to

maybe you need this (I don't know how your data is stored so this is a
guess)

HISTORY_DATA.YEAR = XRATE.YEAR-1

Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx


Jason Lepack

unread,
Jul 9, 2007, 10:49:57 AM7/9/07
to
Your line of code doesn't even execute in SQL Server 2000 (or 2005)
with a datetime value, so I don't know what you're using. However,
this will work in SQL Server 2000:

year(XRATE) = year(HISTORY_DATA) - 1

On Jul 9, 10:30 am, Jean <jeanjanss...@hotmail.com> wrote:

Jean

unread,
Jul 10, 2007, 2:16:53 AM7/10/07
to
hello guys

as i said in my first post, the field YEAR is a decimal field, not
datetime.
Even though I am referring to a calendar year, my data is stored as a
decimal for special purposes.

My question is why can't I subtract 1 from a decimal in the WHERE
clause? The code runs but returns no rows.

> > Am I using the correct syntax for comparing the two years?- Hide quoted text -
>
> - Show quoted text -


ML

unread,
Jul 10, 2007, 3:20:02 AM7/10/07
to
And you are absolutely certain that data corresponding to the given criteria
actually exists? Try removing some of the conditions.

For a more accurate answer provide DDL, sample data and expected results.


ML

---
http://milambda.blogspot.com/

Erland Sommarskog

unread,
Jul 10, 2007, 5:42:49 AM7/10/07
to
Jean (jeanja...@hotmail.com) writes:
> as i said in my first post, the field YEAR is a decimal field, not
> datetime.
> Even though I am referring to a calendar year, my data is stored as a
> decimal for special purposes.
>
> My question is why can't I subtract 1 from a decimal in the WHERE
> clause? The code runs but returns no rows.

Maybe because there are no rows to return?

I'm afraid that I can only ask you to compose a script that demonstrates
the problem.

One thing I have found in operations like this, is that I often get
confused and have the -1 in the wrong place. Maybe this is what you
need:

XRATE.YEAR - 1 = HISTORY_DATA.YEAR

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

jhof...@googlemail.com

unread,
Jul 10, 2007, 8:01:24 AM7/10/07
to
On 10 Jul, 10:42, Erland Sommarskog <esq...@sommarskog.se> wrote:

> Jean (jeanjanss...@hotmail.com) writes:
> > as i said in my first post, the field YEAR is a decimal field, not
> > datetime.
> > Even though I am referring to a calendar year, my data is stored as a
> > decimal for special purposes.
>
> > My question is why can't I subtract 1 from a decimal in the WHERE
> > clause? The code runs but returns no rows.
>
> Maybe because there are no rows to return?
>
> I'm afraid that I can only ask you to compose a script that demonstrates
> the problem.
>
> One thing I have found in operations like this, is that I often get
> confused and have the -1 in the wrong place. Maybe this is what you
> need:
>
> XRATE.YEAR - 1 = HISTORY_DATA.YEAR
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...

> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

I think that the problem is in your JOIN statement...

without the full query it is difficult to know how you have structured
your statement, but if it is something like this:

SELECT *
FROM XRATE
JOIN HISTORY_DATA
ON XRATE.YEAR = HISTORY_DATA.YEAR
WHERE XRATE.YEAR = HISTORY_DATA.YEAR - 1

If this is the case, then the issue is that your where clause
immediately excludes any rows returned by your join (XRATE.YEAR can
not equal HISTORY_DATA.YEAR *and* HISTORY_DATA.YEAR - 1 at the same
time!)

If your statement is structured like this, try:

SELECT *
FROM XRATE
JOIN HISTORY_DATA
ON XRATE.YEAR = HISTORY_DATA.YEAR - 1

J

Jean

unread,
Jul 10, 2007, 8:31:10 AM7/10/07
to
Thanks - your comments made me check the whole query and I found the
problem.

There are actually two places where I need to add the "-1" in the
WHERE clause.

WHERE
...
AND
XRATE.YEAR= HISTORY_DATA.YEAR - 1
AND
...
...
AND
XRATE.YEAR= HISTORY_DATA.YEAR - 1
AND
...


On Jul 10, 11:42 am, Erland Sommarskog <esq...@sommarskog.se> wrote:


> Jean (jeanjanss...@hotmail.com) writes:
> > as i said in my first post, the field YEAR is a decimal field, not
> > datetime.
> > Even though I am referring to a calendar year, my data is stored as a
> > decimal for special purposes.
>
> > My question is why can't I subtract 1 from a decimal in the WHERE
> > clause? The code runs but returns no rows.
>
> Maybe because there are no rows to return?
>
> I'm afraid that I can only ask you to compose a script that demonstrates
> the problem.
>
> One thing I have found in operations like this, is that I often get
> confused and have the -1 in the wrong place. Maybe this is what you
> need:
>
> XRATE.YEAR - 1 = HISTORY_DATA.YEAR
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>

> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...

--CELKO--

unread,
Jul 10, 2007, 10:17:20 AM7/10/07
to
On Jul 9, 9:30 am, Jean <jeanjanss...@hotmail.com> wrote:
> Hello all,
>
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

YEAR is a reserved word in SQL as well as too vague to be a data
element name -- year of what?? Next your will have tables with names
like HistoryData that beg the question history of what??

>> However, I am getting an empty result set. The year field [sic: columns are not fields] is decimal in both tables. <<

Why didn't you use temporal data types? The usual design error is to
have only one time in a row to capture when an event started, then do
horrible self-joins to get the duration of the status change. Let me
use a history table for price changes. The fact to store is that a
price had a duration:

CREATE TABLE PriceHistory
(upc CHAR(13) NOT NULL
REFERENCES Inventory(upc),
start_date DATE NOT NULL,
end_date DATE, -- null means current
CHECK(start_date < end_date),
PRIMARY KEY (upc, start_date),
item_price DECIMAL (12,4) NOT NULL
CHECK (item_price > 0.0000),
etc.);

You actually needs more checks to assure that the start date is at
00:00 and the end dates is at 23:59:59.999 Hrs. You then use a
BETWEEN predicate to get the appropriate price.

SELECT ..
FROM PriceHistory AS H, Orders AS O
WHERE O.sales_date BETWEEN H.start_date
AND COALESCE (end_date, CURRENT_TIMESTAMP);

It is also a good idea to have a VIEW with the current data:

CREATE VIEW CurrentPrices (..)
AS
SELECT ..
FROM PriceHistory
WHERE end_date IS NULL;

There is a common kludge to repair a failure to design a history table
properly that you can put in a VIEW if you are not able to set things
right. Assume that every day we take a short inventory and put it in
a journal. The journal is a clip board paper form that has one line
per item per day, perhaps with gaps in the data. We want to get this
into the proper format, namely periods shown with a (start_date,
end_date) pair for durations where each item had the same quantity on
hand. This is due to Alejandro Mesa

CREATE TABLE InventoryJournal
(journal_date DATETIME NOT NULL,
item_id CHAR(2) NOT NULL,
onhand_qty INTEGER NOT NULL);

WITH ItemGroups
AS
(SELECT journal_date, item_id, onhand_qty,
ROW_NUMBER() OVER(ORDER BY item_id, journal_date, onhand_qty)
- ROW_NUMBER() OVER(PARTITION BY item_id, onhand_qty
ORDER BY journal_date) AS item_grp_nbr
FROM Journal),

QtyByDateRanges
AS
(SELECT MIN(journal_date) AS start_date,
MAX(journal_date) AS end_date,
item_id, onhand_qty
FROM ItemGroups
GROUP BY item_id, onhand_qty, item_grp_nbr)

SELECT start_date, end_date, item_id, onhand_qty
FROM QtyByDateRanges;

This might be easier to see with some data and intermediate steps

INSERT INTO InventoryJournal VALUES('2007-01-01', 'AA', 100);
INSERT INTO InventoryJournal VALUES('2007-01-01', 'BB', 200);
INSERT INTO InventoryJournal VALUES('2007-01-02', 'AA', 100);
INSERT INTO InventoryJournal VALUES('2007-01-02', 'BB', 200);
INSERT INTO InventoryJournal VALUES('2007-01-03', 'AA', 100);
INSERT INTO InventoryJournal VALUES('2007-01-03', 'BB', 300);

start_date end_date item_id onhand_qty
==========================================
'2007-01-01' '2007-01-03' 'AA' 100
'2007-01-01' '2007-01-02' 'BB' 200
'2007-01-03' '2007-01-03' 'BB' 300

Now, download the Rick Snodgrass book on Temporal Queries in SQL from
the University of Arizona website (it is free). And finally Google up
my article at www.DBAzine.com on transition constraints.

SQL Menace

unread,
Jul 10, 2007, 10:29:17 AM7/10/07
to

run these 2 queries and you will see that sql server will round 999
milliseconds up to the next day

select convert(datetime,'2007-07-10 23:59:59.999'),
convert(datetime,'2007-07-10 23:59:59.998')

why not do
>= '20070710'
and < '20070711'

instead of using those milliseconds with between???

Tibor Karaszi

unread,
Jul 10, 2007, 11:26:38 AM7/10/07
to
<CELKO>:

>>>You actually needs more checks to assure that the start date is at
>>>00:00 and the end dates is at 23:59:59.999 Hrs. You then use a
>>>BETWEEN predicate to get the appropriate price

<SQL Menace>:


> run these 2 queries and you will see that sql server will round 999
> milliseconds up to the next day

Probably because Joe practices cowboy coding in SQL Server, and don't follow the basic SQL Server
best practices for handling datetime values. I'm pretty certain I've pointed out this before to Joe,
but it doesn't seem to sink in:
http://www.karaszi.com/SQLServer/info_datetime.asp#Searching
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"SQL Menace" <denis...@gmail.com> wrote in message
news:1184077757.8...@57g2000hsv.googlegroups.com...

Tony Rogerson

unread,
Jul 10, 2007, 12:42:56 PM7/10/07
to
> SELECT ..
> FROM PriceHistory AS H, Orders AS O
> WHERE O.sales_date BETWEEN H.start_date
> AND COALESCE (end_date, CURRENT_TIMESTAMP);

Still ignoring this vendor best practice.

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/07/06/sql-style-from-x-y-z-or-inner-join-we-are-all-cowboy-coders.aspx

As Tibor points out, you actually ignore the standard way of date formatting
too.

The pattern is clear; you ignore standards and best practice yourself; you
think 'the standard' is 'your standard' even when it differs from documented
and accepted methods.

This isn't a global / local dialect thing, its a personal preference that
you don't want to give up regardless of the cost to other devs/support guys
that follow and have to maintain your on fringe style of coding.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]


"--CELKO--" <jcel...@earthlink.net> wrote in message
news:1184077040.7...@r34g2000hsd.googlegroups.com...

--CELKO--

unread,
Jul 10, 2007, 1:51:35 PM7/10/07
to
>> run these 2 queries and you will see that sql server will round 999 milliseconds up to the next day <<

I have another posting about the SQL Server rounding errors, other SQL
products and the FIPS-127 requirements. I need to add that to this
stock posting.

I am hoping that when 64 bit comes along, they will do something to
get rid of this hang over from the UNIX/Sybase/16 bit days.

Alex Kuznetsov

unread,
Jul 10, 2007, 2:02:55 PM7/10/07
to

If it is done it will break version-to-version portability - not a
nice thing to hope for. Correct?

Alex Kuznetsov, SQL Server MVP
http://sqlserver-tips.blogspot.com/


Tibor Karaszi

unread,
Jul 10, 2007, 4:54:25 PM7/10/07
to
> I am hoping that when 64 bit comes along, they will do something to
> get rid of this hang over from the UNIX/Sybase/16 bit days.


64 bit has been out for about 7 years now. No, MS decided to *not* change datatype behavior going
across processor platform. That would, IMO, be a ridiculous idea. A better idea would be to
introduce a new datatype with better ANSI SQL Compliance.

"--CELKO--" <jcel...@earthlink.net> wrote in message

news:1184089895.8...@n60g2000hse.googlegroups.com...

Jamie Collins

unread,
Jul 11, 2007, 8:53:51 AM7/11/07
to
On Jul 10, 3:29 pm, SQL Menace <denis.g...@gmail.com> wrote:
> > You actually needs more checks to assure that the start date is at
> > 00:00 and the end dates is at 23:59:59.999 Hrs. You then use a
> > BETWEEN predicate to get the appropriate price
>
> why not do>= '20070710'
>
> and < '20070711'
>
> instead of using those milliseconds with between???

Because Celko uses closed-closed representation of start- and end date
pairs, as do I because I find it more intuitive than the closed-open
representation you propose. For example, if I know the last time
granule on which I can submit my entry to a competition is '2007-07-11
23:59:59.997' then things are pretty clear, however if told the
closing date is (midnight on the) '2007-07-12' I perhaps wouldn't
realise that a 6am submission on the 12th of July would miss the
deadline.

The BETWEEN construct is intuitive for human readers, particularly IMO
with DATETIME values. One thing I know Celko appreciates is being able
to state a single 'fact' as a one-liner, which is also why he likes

column1 = COALESCE(@param1, column1)

stylee predicates.

Jamie.

--

ML

unread,
Jul 11, 2007, 9:04:04 AM7/11/07
to
There is, however, a significant difference between 999 and 997. Significant
when it comes to date/time values, anyway. ;) In SQL Server 999 is not
"closed".


ML

---
http://milambda.blogspot.com/

SQL Menace

unread,
Jul 11, 2007, 9:10:12 AM7/11/07
to

Joe,

remember the other day when you were saying something about 'defensive
programming' I believe you quoted Ken Henderson
well defensive programming would be to use
>= start date and < the next day (of the end date)
what happens if they change the date from datetime to smalldatetime?

It will work the same on every platform no matter if it is
smalldatetime,datetime datetime with nano/pico second precision, you
will never ever have to change this code
BTW SQL SERVER 2008 will introduce 4 new date data types (see below)

In my opinion BETWEEN should be removed, BETWEEN is just a lazy
programmers way of saying >= and <=

Once I worked for a company and we ran a monthly report of orders.
Between our report and the Great Plains report there was a small
discrepancy
We queried the table for all the orders for the month, same number as
in Great Plains
Next we summed the amounts and guess what? It matched.
Then we looked at the proc and yes there was the between
It turned out that exactly at midnight there was an order placed on
the first of the month (so what are the odds of that?)

I wrote 2 articles on my blog
Do You Know How Between Works With Dates?
http://sqlservercode.blogspot.com/2006/10/do-you-know-how-between-works-with.html

How Are Dates Stored In SQL Server?
http://sqlservercode.blogspot.com/2006/11/how-are-dates-stored-in-sql-server.html

It is not as extensive as Tibor's article but it is a good read

SQL Server 2008 New Datatypes
--------------------------------
DATE - ANSI-compliant date data type
TIME - ANSI-compliant time data type with variable precision
DATETIMEOFFSET - timezone aware/preserved datetime
DATETIME2 - like DATETIME, but with variable precision and large date
range

GEOMETRY - "flat earth" spatial data type
GEOGRAPHY - "round earth" spatial data type
HIERARCHYID - represents hierarchies using path enumeration model

Tibor Karaszi

unread,
Jul 11, 2007, 4:56:45 PM7/11/07
to
... and you would need different ways to search depending on if it is datetime (.997) or
smalldatetime (.). And with new datetime datatypes, you will need even more ways to do this.


"ML" <M...@discussions.microsoft.com> wrote in message
news:BA2F911E-1B35-4A58...@microsoft.com...

ML

unread,
Jul 11, 2007, 5:44:01 PM7/11/07
to
One (two) suggestions for the parser wish list:

expression { < | <= } expression { < | <= } expression

expression { > | >= } expression { > | >= } expression


;)


ML

---
http://milambda.blogspot.com/

--CELKO--

unread,
Jul 12, 2007, 11:30:24 PM7/12/07
to
>> In my opinion BETWEEN should be removed, BETWEEN is just a lazy Not programmers way of saying >= and <=
<<

And IN() is a lazy way of saving a chain of ORs?

I disagree. Both of these constructs are at a higher level of
abstraction and clearly show n-ary relationships in a shorthand that
passes that meaning to a human being. In case of BETWEEN it can also
give an optimizer a hint about using tree strucutred indexes, but that
is a bonus.

>> Once I worked for a company and we ran a monthly report of orders. Between our report and the Great Plains report there was a small discrepancy. Then we looked at the proc and yes there was the betwee. It turned out that exactly at midnight there was an order placed on the first of the month (so what are the odds of that?) <<

LOL! That was a killer! This is why (as I said in another posting), I
use DDL to force the precision to one or two decimal place in the
Sybase/SQL Server family. I can then port the code to ANSI/ISO
compliant products with a mechanical text edit.

I often wondered since the Sybase/SQL Server uses a floating point for
their DATETIME, why there is no delta of error in date equality.
Again, I guess it is a "code museam" thing from the pre-IEEE Standard
days.


Tibor Karaszi

unread,
Jul 13, 2007, 2:18:28 AM7/13/07
to
> I often wondered since the Sybase/SQL Server uses a floating point for
> their DATETIME,

I don't know about Sybase, but SQL Server actually uses two integers (int or smallint depending on
whether datetime or smalldatetime). One integer represents date part and the other represents time
part.

"--CELKO--" <jcel...@earthlink.net> wrote in message

news:1184297424.5...@r34g2000hsd.googlegroups.com...

@remove_mebisit.nl Arno Brinkman

unread,
Jul 13, 2007, 3:41:37 AM7/13/07
to
Hi Celko,

>>> In my opinion BETWEEN should be removed, BETWEEN is just a lazy Not programmers way of saying >= and <=
>
> And IN() is a lazy way of saving a chain of ORs?
>

> In case of BETWEEN it can also
> give an optimizer a hint about using tree strucutred indexes, but that
> is a bonus.

This may not make any difference IMO, because in both cases lower and upper bounds are known for the field in question.
Optimizer should be smart enough to handle this.

SELECT
*
FROM
TableX x
WHERE
x.FieldX >= 4 and
x.FieldX <= 8

SELECT
*
FROM
TableX x
WHERE
x.FieldX BETWEEN 4 and 8

For both cases FieldX
lower-bound = 4 (scan start)
upper-bound = 8 (scan end)

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database with many powerful SQL features:
http://www.firebirdsql.org
http://www.firebirdsql.info

General database developer support:
http://www.databasedevelopmentforum.com

Support list for Interbase and Firebird users:
firebird...@yahoogroups.com

Nederlandse firebird nieuwsgroep:
news://newsgroups.firebirdsql.info


0 new messages