April 16, 2008 at 8:57 am
Please forgive what is almost certainly a newbie question but any help (even patronizing 😉 is greatly appreciated. Heres my problem:
I have a column which contains values like below:
13900
5950
2450
This is taken from a different system that stores price information in this format, the actual price is and the values which i need to replace the original with are:
139,00
59,50
24,50
So basically just take the last two digits and use them as decimal in a new colum, I have been toying around with the derived column feature in integration services but am unable to get this working. Can anyone help me or point me in the right direction?
Best Regards
Gustav S
Stockholm, Sweden
April 16, 2008 at 9:16 am
have you tried dividing by 100 to move the deciaml point?
April 16, 2008 at 3:32 pm
Of course....
You were right, I was to occupied by the more complicated functions so I didn't think about the obvious... Thanks a lot man for pointing it out to me 😉
Here's my final solution for those of you that are interested (the narrow few that are too embarrassed to admit it) I skipped the derived columns feature and just used the original t-sql to do it:
Where SalesPriceSek and SalesPriceEur originally was xxxx and now is xx.xx. The rest of the columns you can just ignore, but hopefully the complete sql helps someone.
In addition, before I can perform this question, I am using a Datareader Source to collect data via an ODBC, from that I get the columns specified below by using regular sql-syntax and specifiying aliases for the columns (the ones you see below). And I had to convert the data by using Cast() in order to get it exactly as I wanted.
SELECT [ID], PosItemID, cast(SalesPriceSek / 100 AS Decimal(7,2)) AS SalesPriceSek,cast(SalesPriceEuro / 100 AS Decimal(7,2)) AS SalesPriceEuro, PurchasePrice, PurchaseCurrency, RANK() OVER (PARTITION BY PosItemID order by ID DESC) AS RANK
FROM dbo.Prices_Temp
Oh, and I almost forgot, for any of you reading this that are on the same level as me, the Rank part of this sql is basically because I am using the column PosItemId as a primary key in my new solution, and my source (Garp, anyone who knows it please let me know) apparently doesn't have a function that checks for duplicates. So I used the rank function and Conditional Split in Integration Services in order to split out duplicates to an excel-document and report it to the department in charge of our article-hierarchy. This is all thanks to Rafael Salas:
http://rafael-salas.blogspot.com/2007/04/remove-duplicates-using-t-sql-rank.html
I know most of you will just laugh at this but it really helped me, I was so snowed in at all the functions available in BIS so I simply didn't think of it, so once again thanks alot 🙂
//The apparently not so good at math Gustav
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply