CODEWITHSUNDEEP
sql-servert-sqlcasesqldatatypes
at-hex
at-hex

Reputation: 3130

MONEY Datatype CAST issue in IIF or CASE clause

Here is a sample query sample that produces correct output:

DECLARE @T TABLE (
    keydate DATE,
    price   MONEY
);

INSERT INTO @T(keydate, price) VALUES
('2014-07-23', 32464),
('2014-07-26', 32478),
('2014-07-27', 32473)


;WITH
cte_tbl (keydate, price, prev_price1)
AS (
SELECT TOP(1) 
    T.keydate,
    T.price,
    T.price AS prev_price
    FROM @T AS T
    ORDER BY T.keydate
UNION ALL
SELECT
    T.keydate,
    T.price,
    T.prev_price
    FROM (
    SELECT
        TT.keydate,
        TT.price,
        IIF(ABS(TT.price - cte_tbl.prev_price1) < 10, cte_tbl.prev_price1, TT.price) AS prev_price,
        ROW_NUMBER() OVER(ORDER BY TT.keydate) AS ROWNUM
        FROM @T AS TT
        INNER JOIN cte_tbl ON cte_tbl.keydate < TT.keydate
    ) AS T
    WHERE T.ROWNUM = 1
)

SELECT *
FROM cte_tbl 
ORDER BY keydate

The correct output is:

keydate price       prev_price1
2014-07-23  32464.00    32464.00
2014-07-26  32478.00    32478.00
2014-07-27  32473.00    32478.00

If I change to decimal prices holding MONEY with a dot:

INSERT INTO @T(keydate, price) VALUES
('2014-07-23', 324.64),
('2014-07-26', 324.78),
('2014-07-27', 324.73)

The output would be wrong:

keydate price   prev_price1
2014-07-23  324.64  324.64
2014-07-26  324.78  324.64
2014-07-27  324.73  324.64

Prices are in CTE statement actually but it does not reject the mess.

I need to store them as normal prices and have correct output behavior as above: 

('2014-07-23', 324.64),
('2014-07-26', 324.78),
('2014-07-27', 324.73)

The output should be:

keydate price       prev_price1
2014-07-23  32464.00    324.64
2014-07-26  32478.00    324.78
2014-07-27  32473.00    324.78

Thank you for your help!

Upvotes: 0

Views: 230

Answers (1)

GarethD
GarethD

Reputation: 69789

It is because the diffence between 32464 and 32478' is more than 10, but the difference between324.64and324.78` is not. Change this line:

IIF(ABS(TT.price - cte_tbl.prev_price1) < 10, cte_tbl.prev_price1, TT.price)

to:

IIF(ABS(TT.price - cte_tbl.prev_price1) < 0.10, cte_tbl.prev_price1, TT.price)

If you are reducing the prices by a factor of 10, you need to reduce the comparison by a factor of 10.

Upvotes: 3

Related Questions