2 number subtraction in ssis

Question

Post reply

2 number subtraction in ssis

sql Anand

SSCommitted

Points: 1597
More actions
October 31, 2011 at 1:17 am

#250912

hi
i m having table like this
empno ename sal
1 kkk 21000
2 fff 25000
3 ggg 30000
now i want to substrate (empno2) sal - (empno1) sal
how to do this in ssis package

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Is there any business rule that says which salaries should be subtracted by other salaries?

Or is it just random?

Since you are trying to compare rows with each other (which is not a set-based operation), you'll probably need a script component as a transformation for that.

For more information:

http://msdn.microsoft.com/en-us/library/ms136114.aspx

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

Thomas.s (10/31/2011)
hi
i m having table like this
empno ename sal
1 kkk 21000
2 fff 25000
3 ggg 30000
now i want to substrate (empno2) sal - (empno1) sal
how to do this in ssis package

Could you set this as the dataflow source?

SELECT a.empno, a.ename, a.sal, ISNULL(a.sal - b.sal,0) AS [sal - sal]

FROM myStangeTable a

LEFT OUTER JOIN myStangeTable b ON a.empno - 1 = b.empno

Bear in mind that I'm guessing as to your business logic of which "sal" to minus from which "sal", so you may need to adjust to suit your rules.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

sql Anand SSCommitted Points: 1597 More actions · Answer 3

hi,

thanks for ur help..but i dont want query in sql i want only ssis package mapping in sql server 2008

neeta.chander SSC Enthusiast Points: 155 More actions · Answer 4

For this problem, best solution will be to modify sql query

If you still want to go SSIS way, use a Lookup, modify the query in advanced query to get a value less than current value or for last empId (as per your business logic), and finally use a derived column to subtract the salary value returned by Lookup from salary.

sql Anand SSCommitted Points: 1597 More actions · Answer 5

ya thanks..just i need empno1.sal -empno2.sal in ssis map.

i dont want less than current value etc

neeta.chander SSC Enthusiast Points: 155 More actions · Answer 6

Use lookup...Join on EmployeeId...modify advanced query to something like

select top 1 * from

(select * from [dbo].[Employees]) [refTable]

where [refTable].[Id]< ? Order by Id desc

(there are other ways also to get last max value..this came ad hoc to me)

Use derived column, subtract Salary returned by lookup from source salary.

Cadavre SSC-Forever Points: 41690 More actions · Answer 7

Thomas.s (10/31/2011)
hi,
thanks for ur help..but i dont want query in sql i want only ssis package mapping in sql server 2008

That's why I said set it as the dataflow source.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 8

Thomas.s (10/31/2011)
ya thanks..just i need empno1.sal -empno2.sal in ssis map.
i dont want less than current value etc

What exactly do you mean with "ssis map"?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

steveb. SSC-Forever Points: 46733 More actions · Answer 9

If you don't want to make a change to the sql source,

another way of doing this would be to add your data source twice, then use a derived column to minus one from the key of one data source.

Then do a merge based on this key and do the subtraction in subsequent derived column.