April 28, 2014 at 7:13 am
Hi,
I have a flat file with 2 colums: TradeID and Nominal. Now I would like to add third column showing the summed nominal (=position) per tradeID. Please see an example attached. What would be the best way to do this?
br,
Jack
April 28, 2014 at 7:22 am
the best way is to create a view, which would calculate that information on demand.
Best practice is to never add a column containing agregates in a table row, since it woudl require an update every time data changes.
you could achieve the same thing in line with a user defined function and a calculated column that uses that function, but the view is my recommendation.
;WITH MyCTE([TradeID],[nominal])
AS
(
SELECT 'TRADE1',500 UNION ALL
SELECT 'TRADE1',1000 UNION ALL
SELECT 'TRADE2',2000 UNION ALL
SELECT 'TRADE2',2000
)
SELECT
MyCTE.[TradeID],
MyCTE.[nominal],
Sumz.[position]
FROM MyCTE
INNER JOIN(SELECT [TradeID],SUM([nominal]) As position FROM MyCTE GROUP BY [TradeID]) Sumz
ON MyCTE.[TradeID]=Sumz.[TradeID]
Lowell
April 29, 2014 at 1:48 am
Thanks a lot Lowell, I'll investigate that. So, you don't think it makes sense to do it in the SSIS package using eg. Aggregate Transformation?
br,
Jack
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply