SQL does not substract - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL does not substract

Author

Topic

hmoner
Starting Member

2 Posts

Posted - 2005-06-19 : 22:57:52

Hi guys
Lemme explain the situation in 2 lines:
DB Structure:
http://img133.echo.cx/img133/6610/dbstructure6hs.jpg

Client goes to computer shop. Shop has 1 or more NORMAL promotions (of the kind: for each u$sXX ammount you buy, you get X qtty of points to exchange them later by gifts). EACH NORMAL PROMOTION can have 0 or more special "sub"promotions (same kind as normal promotion, but you get more points)
For each purchase, client gets an invoice with ammount, points he got, and details about if promotion is normal or special (or both)
Later, client can exchange points by gifts...

... there's more but I think that this will be enought to explain ...

My problem is:
I was requested to report this (for all clients of actual promotion):

NAME, ACTUAL_POINTS, NORMAL_AMMOUNT, SPECIAL_AMMOUNT, TOTAL_AMMOUNT, NORMAL_POINTS, SPECIAL_POINTS, TOTAL_POINTS, EXCHANGED_POINTS
(order can vary)

*******************************
But, following SQL gives me ACTUAL_POINTS = 0 for all records...
It should give
"SUM(INVOICE.POINTS) - SUM(EXCHANGE.TOTALPOINTS)"
for each record (client)
*******************************

Please look image so you understand the error i get ...
http://img61.echo.cx/img61/8879/dbqueryproblem5it.jpg

--------------------------------------

SELECT
CLIENT.NAME AS CLIENT,
SUM(INVOICE.POINTS) - SUM(EXCHANGE.TOTALPOINTS) AS ACTUAL_POINTS,
SUM(CASE WHEN IS_SPECIAL_PROMO = TRUE THEN 0 ELSE INVOICE.AMMOUNT END) AS NORMAL_AMMOUNT,
SUM(CASE WHEN IS_SPECIAL_PROMO = TRUE THEN INVOICE.AMMOUNT ELSE 0 END) AS SPECIAL_AMMOUNT,
SUM(INVOICE.AMMOUNT) AS TOTAL_AMMOUNT,
SUM(CASE WHEN IS_SPECIAL_PROMO = TRUE THEN 0 ELSE INVOICE.POINTS END) AS NORMAL_POINTS,
SUM(CASE WHEN IS_SPECIAL_PROMO = TRUE THEN INVOICE.POINTS ELSE 0 END) AS SPECIAL_POINTS,
SUM(INVOICE.POINTS) AS TOTAL_POINTS,
SUM(EXCHANGE.TOTALPOINTS) AS EXCHANGED_POINTS
FROM
CLIENT
LEFT OUTER JOIN INVOICE ON (CLIENT.ID = INVOICE.IDCLIENT)
LEFT OUTER JOIN EXCHANGE ON (CLIENT.ID = EXCHANGE.IDCLIENT)
WHERE
(CLIENT.IDPROMO = 1) // I replace "1" by :PARAM_ID_NORMALPROMOTION
GROUP BY
CLIENT.NAME,ACTUAL_POINTS
ORDER BY
CLIENTE.NAME

--------------------------------------

I tried replacing "SUM(INVOICE.POINTS)" by 10000 to test if substraction worked, but i got 10000 for every record, and I also replaced "- SUM(EXCHANGE.TOTALPOINTS)" by 1000 but i got -1000 for every record, so it seems that both fields are taken as NULL...

Does any1 have a solution to correct this ?

Does any1 have another way to make this query ? Easily ?

Thanks 4 reading and helping

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-06-19 : 23:55:37

Without taking a lot of time to look at the data (you should post some sample data and a CREATE TABLE statement to get faster help), I would guess you are actually getting zero back on each side, not NULL. NULL plus or minus anything else is NULL. However, the following sentence:

quote:

I tried replacing "SUM(INVOICE.POINTS)" by 10000 to test if substraction worked, but i got 10000 for every record, and I also replaced "- SUM(EXCHANGE.TOTALPOINTS)" by 1000 but i got -1000 for every record

indicates a zero on both sides. What is your TOTAL_POINTS and EXCHANGED_POINTS columns showing?

Post some sample data in INSERT statements and give us CREATE table statements if this doesn't point you in the right direction.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

hmoner
Starting Member

2 Posts

Posted - 2005-06-20 : 00:26:17

Hi Derrick
I'm afraid I cannot post SQL code for creating tables nor data, because the SQL engine I'm using is nexusDB (www.nexusdb.com). This engine if full ISO compliant and well, support guys are on vacation now :(

- Here is table structure, without field types
http://img133.echo.cx/img133/6610/dbstructure6hs.jpg

and
- TOTAL_POINTS and EXCHANGED_POINTS show:

TOTAL_POINTS = NORMAL_POINTS+SPECIAL_POINTS = TOTAL POINTS CLIENT GOT IN ALL PURCHASES HE MADE

EXCHANGED_POINTS = POINTS THAT CLIENT EXCHANGED FOR PRIZES

So, SUM(TOTAL_POINTS) - SUM(EXCHANGED_POINTS) should show ACTUAL_POINTS (or POINTS_LEFT if you want)

I apreciatte any help you can give me...
Sorry if I cannot post data nor Create SQL :( this engine does not allow this

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources