Difference - pelase help newbie!

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 2000 Forums

Transact-SQL (2000)

Difference - pelase help newbie!

Author

Topic

d
Starting Member

26 Posts

Posted - 2005-06-17 : 06:19:25

Hi

I am struggling with a relativley simple query!

Scenario:
I take a loan out on 1 august 2005 which has a term of 12 months. I want to be able to pull out all records that are due to complete within next three months based on a start date and term.

Any ideas?? Anyone, please!

vivek.kumargupta
Starting Member

45 Posts

Posted - 2005-06-17 : 06:39:16

Hi d,
add some details of the schema you are referring to!!

Thanks,
Vivek

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-17 : 06:43:37

declare @StartDate datetime
set @StartDate = '20050801'
select * from
MyLoanTable
where TakeOutDate between @StartDate and dateadd(m, 3, @StartDate)

Go with the flow & have fun! Else fight the flow

d
Starting Member

26 Posts

Posted - 2005-06-17 : 06:46:28

SELECT EnteredOn, AgreementNumber, term
FROM dbo.Agreements
where "this is where I am stuck"

EnteredOn could be: 1 july 2005
Term: 12 months

I want the query to pick this one out as the term means that this deal will finish within 3 months of now.

Does that help?

d
Starting Member

26 Posts

Posted - 2005-06-17 : 07:01:08

Right, this is what I have:

SELECT TOP 100 PERCENT dbo.Agreements.AgreementNumber, dbo.Agreement_Statuses.Description, dbo.Agreement_Financials.OriginalBalance,
dbo.Agreements.EnteredOn, dbo.Agreement_Financials.Term, dbo.Agreement_Financials.Balance
FROM dbo.Agreements INNER JOIN
dbo.Agreement_Financials ON dbo.Agreements.AgreementNumber = dbo.Agreement_Financials.AgreementNumber INNER JOIN
dbo.Agreement_Statuses ON dbo.Agreements.AgreementStatus = dbo.Agreement_Statuses.AgreementStatus
where dbo.Agreements.EnteredOn between dbo.Agreements.EnteredOn and dateadd(m, 3, dbo.Agreements.EnteredOn)
ORDER BY dbo.Agreements.EnteredOn DESC

But this currently brings back records including ones which have been setup within the last couple of days. How do I intergrate the use of the term field EG record one was enterd on 14 june 06, it has a term of 13 months, i therefore would not expect to see this in my query for another 10 months.

Please help, thanks again

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-17 : 07:33:51

Post your table structures with some sample data and expected result

Madhivanan

Failing to plan is Planning to fail

d
Starting Member

26 Posts

Posted - 2005-06-17 : 07:41:01

In it's original form:

SELECT TOP 100 PERCENT dbo.Agreements.AgreementNumber, dbo.Agreement_Statuses.Description, dbo.Agreement_Financials.OriginalBalance,
dbo.Agreements.EnteredOn, dbo.Agreement_Financials.Term, dbo.Agreement_Financials.Balance
FROM dbo.Agreements INNER JOIN
dbo.Agreement_Financials ON dbo.Agreements.AgreementNumber = dbo.Agreement_Financials.AgreementNumber INNER JOIN
dbo.Agreement_Statuses ON dbo.Agreements.AgreementStatus = dbo.Agreement_Statuses.AgreementStatus
ORDER BY dbo.Agreements.EnteredOn DESC

results:
agreementnumber description origbal enteredon term
123456 test 2423.44 14/06/2006 13
654432 test2 2233.44 10/06/2006 48
234445 test3 1222.22 01/07/2005 12

expected result:
Only the last entry as this is set to have run it full term of 12 monthe within the next 3 months.

The aim is that I identify all loan records that are due to complete within the next 3 months.

Thanks again

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-17 : 08:01:12

Try this

SELECT TOP 100 PERCENT dbo.Agreements.AgreementNumber, dbo.Agreement_Statuses.Description, dbo.Agreement_Financials.OriginalBalance, 
dbo.Agreements.EnteredOn, dbo.Agreement_Financials.Term, dbo.Agreement_Financials.Balance
FROM dbo.Agreements INNER JOIN
dbo.Agreement_Financials ON dbo.Agreements.AgreementNumber = dbo.Agreement_Financials.AgreementNumber INNER JOIN
dbo.Agreement_Statuses ON dbo.Agreements.AgreementStatus = dbo.Agreement_Statuses.AgreementStatus
where Datediff(m, dbo.Agreements.EnteredOn,'20050801')<=3
ORDER BY dbo.Agreements.EnteredOn DESC

Madhivanan

Failing to plan is Planning to fail

d
Starting Member

26 Posts

Posted - 2005-06-17 : 09:06:32

Hi

I see what your saying, maybe I am not making this clear. As far as I can see you need to use dbo.Agreement_Financials.Term field to see when the agreement is due to end, I can see no reference to this??

Thanks again

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-17 : 09:19:36

If dbo.Agreement_Financials.Term is the due date then
use this in place of '20050801' in that query

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources