I have a series of records that I periodically sum up, and then place those
sums in a master record for easy reporting. Every time the sum changes, I
note down that a change was made by updating a datetime in the master.
The trick is knowing if anything changed or not. I do this...
DECLARE @.didChange AS boolean
SET @.didChange = FALSE
IF ((@.newFilled IS null AND @.oldFilled IS NOT null) OR (@.newFilled IS NOT
null AND @.oldFilled IS null) OR (ROUND(@.newFilled, 2) <> ROUND(@.oldFilled,
2))) @.didChange = TRUE
there's a couple more IFs following. BTW, there's a syntax error in there
somewhere, I'm still trying to find it.
My question is whether or not I can simplify the if down to something
smaller. As you can see, it tests three cases...
1) the value was changed TO null from anything
2) the value was changed FROM null to anything
3) the value simply changed
In the past I tried a greatly simplified solution...
IF ISNULL(@.newFilled, 0) <> ISNULL(@.oldFilled, 0) THEN @.didChange = TRUE
The problem with this approach is that it "misses" changes from null to zero
or back. It just didn't work right.
So can I do this...
IF ISNULL(@.newFilled, -1) <> ISNULL(@.oldFilled, -1) THEN...
The values are always positive, so they can never be -1. Do you think this
is a good approach, or is there some edge case I'm forgetting about?
MauryAhh, the syntax error...
obviously that should be a tinyint (or bit I guess) and there needs to be a
SET
those are fixed.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment