Join Tables - 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)

Join Tables

Author

Topic

Nikkap
Starting Member

8 Posts

Posted - 2007-06-01 : 10:50:45

How could Join these 2 tables

Table Periods
StartDate Lastdate Rate
02/01/06 07/03/06 2.25
08/03/06 14/06/06 2.50
15/06/06 08/08/06 2.75

Table Payments
PayDate Amount
--------- -----------
08/05/2006 50000
17/07/2006 50000
19/07/2006 50000

So I have the next result

Result

StartDate LastDate Rate Amount
02/01/06 07/03/06 2.25 0
08/03/06 08/05/06 2.50 50000
09/05/06 14/06/06 2.50 0
15/06/06 17/07/06 2.75 50000
18/07/06 19/07/06 2.75 50000
20/07/06 08/08/06 2.75 0

Thank you very much in advance

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-01 : 11:16:23

what is the joining column between the two?
how do they connect?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Nikkap
Starting Member

8 Posts

Posted - 2007-06-01 : 12:53:26

Thank u spirit1 for your reply.

Periods.StartDate with Payments.PayDate

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-02 : 02:56:21

[code]
StartDate LastDate Rate Amount
02/01/06 07/03/06 2.25 0
08/03/06 08/05/06 2.50 50000
09/05/06 14/06/06 2.50 0
15/06/06 17/07/06 2.75 50000
18/07/06 19/07/06 2.75 50000
20/07/06 08/08/06 2.75 0
[/code]

can you explain how did the records in red comes from ?

Nikkap
Starting Member

8 Posts

Posted - 2007-06-02 : 05:18:31

Thank u for your reply Khtan

Sorry,

to make it understood
it is something like bank account , loan interest

Table Periods
StartDate Lastdate Rate
02/01/06 07/03/06 2.25
08/03/06 14/06/06 2.50
15/06/06 08/08/06 2.75

Table Payments
PayDate Amount
--------- -----------
08/05/2006 50000
17/07/2006 50000
19/07/2006 50000

I have also @principal parameter

So I have the next result if set @principal = 500000

Result

StartDate LastDate Rate Amount Principal Interest
02/01/06 07/03/06 2.25 0 500000
08/03/06 08/05/06 2.50 50000 500000
09/05/06 14/06/06 2.50 0 450000
15/06/06 17/07/06 2.75 50000 450000
18/07/06 19/07/06 2.75 50000 400000
20/07/06 08/08/06 2.75 0 350000

There is one record if rate change or payment have done so the principal have reduced.

02/01/06 up to 07/03/06 the rate was 2.25
08/03/06 up to 08/05/06 the rate changed to 2.50
09/05/06 up to 14/06/06 the rate is the same but the principal for calculate interest have changed.

Payment have done 08/05/06 but principal in order to calculate interest will change the next day 09/05/06

Thank u very much

Nikkap
Starting Member

8 Posts

Posted - 2007-06-05 : 09:27:35

Some idea pls ? How to connect these tables and show the above result ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources