0

I made an formula in an update statement in order to generate Targets according to a Date_Key. The table 'Test' exists of two relevant columns:

'Date_Key'(INT) and 'Target'(DECIMAL)

The logic I want to implement is as follows: 

UPDATE       Test
SET          Target = 175+(2.879546*([Date_Key]-25))

It works but I would like three digits after the comma.

Can someone help me out?

Thank you in advance!

user3248190
  • 101
  • 1
  • 10
  • 1
    What do you mean "records will be added"? You mean a column? "records" usually refers to rows, not columns. – Tab Alleman Nov 25 '14 at 17:44
  • Out of morbid curiosity, can you help me understand your logic? – billinkc Nov 25 '14 at 19:56
  • I want to increase the target by plus 2.879546 each month. The target starts at 175 on the first month this FY. The date key of the first month of FY was 25. The date key of the second month of FY was 26 so the expression wil increase the target by 2.879546. And so on.. – user3248190 Nov 25 '14 at 21:01
  • I changed my question after I realized that I needed an SQL task instead of a derived column. The only issue I have now is that there are no digits after the comma, I would like to have thee digits after the comma. – user3248190 Nov 26 '14 at 13:11

1 Answers1

0

You need to cast your INT to a more precise format; in your case is should be DECIMAL (X,3).

UPDATE       Test
SET          Planned_Headcount = 175+(2.879546*(CAST([Date_Key] as DECIMAL(X,3))-25))

The "X" above should be a number that large enough to contain the results of your math. 12 would likely suffice, but you would need to test since I have no idea what the value of [Date_Key] might be.

Also, ensure that the decimal column you are trying to update (either "Target" or "Planned Headcount", whichever is correct) also has at least 3 digits of precision, i.e. is DECIMAL(x,3).

This seems like an odd requirement.

Eric Hauenstein
  • 2,557
  • 6
  • 31
  • 41
  • Hi Eric, Thank you very much! The Date_key column only exists of exacts numbers; your second suggestion did the trick. I had to specify the 3 digits of precision in the data type of the Target column. I agree on your odd requirement comment. It is a temporary solution to fill up the targets in our data mart. Thanks again! – user3248190 Nov 26 '14 at 13:53