complext query for looping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

complext query for looping

Author

Topic

sushil
Starting Member

11 Posts

Posted - 2010-05-26 : 13:03:18

I have to compare row by row for two tables, A and B which result following output. I will be very much grateful if anyone can give idea about this query in sql.

Table A
Employee Name EmployeeID Fund Date Orig Loan
Doe, Jane 123-12-1234 1/15/2009 $      300.00
Doe, Jane 123-12-1234 3/6/2009 $      200.00

Table B
Employee Name EmployeeID Refund Amount
Doe, Jane 123-12-1234 $ 250.00

OutPut
Employee Name EmployeeID Refund applied against funding Derived fund date associated with refund
Doe, Jane 123-12-1234 $    200.00 3/6/2009
Doe, Jane 123-12-1234 $      50.00 1/15/2009

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-26 : 13:29:54

In TableB (Refund) you don't have any datetime column. In such case how can you determine the refunds needs to be adjusted against which entry in TableA.

Using your sample data given by you, I have written one sql query.
This is just to help you to start with and may not be complete solution for your requirement.

create table TableA
(EmployeeName varchar(100),
EmployeeId varchar(50),
FundDate datetime,
Loan Int)

Create Table TableB
(EmployeeName varchar(100),
EmployeeId varchar(50),
Refund Int)

Insert into TableA
Select 'Doe, Jane', '123-12-1234','2009-01-15',300 union
Select 'Doe, Jane', '123-12-1234','2009-06-03',200

Insert into TableB
Select 'Doe, Jane', '123-12-1234', 250

Select T1.EmployeeName, T1.EmployeeId,T1.FundDate, T1.Loan - isnull(T2.Refund,0)
From
(Select Row_number() over (Partition By EmployeeId order by FundDate) as SrNo,
* from TableA) As T1
left Join
(Select Row_number() over (Partition By EmployeeId Order by EmployeeId ) as SrNo,
* from TableB) T2
on T1.EmployeeId = T2.EmployeeId and T1.Srno = T2.Srno

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

sushil
Starting Member

11 Posts

Posted - 2010-05-26 : 16:55:18

Oh yeah, first we take the maximum fund date for the given employeeID and then start populating.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources