Need help with SELECT statement.

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)

Need help with SELECT statement.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-01-12 : 15:04:53

I need to calculate the payment amount base on the requirement below. Please see the results want and the business rules below.
Thank you so much in advance.

IF OBJECT_ID('P', 'u') IS NOT NULL
DROP TABLE P
go
CREATE TABLE dbo.P
(
[LoanNum] [int] NULL,
[DueDate] DATETIME NULL,
[TotalPayment] [money] NULL,
[PIPayment] [money] NOT NULL,
[EscrowPmt] [money] NOT NULL
)
GO

INSERT INTO dbo.P([LoanNum],[DueDate],[TotalPayment],[PIPayment],[EscrowPmt])VALUES (123456, '03/01/2007', 960.73, 695.48, 265.25)
INSERT INTO dbo.P([LoanNum],[DueDate],[TotalPayment],[PIPayment],[EscrowPmt])VALUES (123456, '12/01/2007', 1054.85, 789.60, 265.25)
INSERT INTO dbo.P([LoanNum],[DueDate],[TotalPayment],[PIPayment],[EscrowPmt])VALUES (123456, '06/01/2008', 1143.70, 878.45, 265.25)
INSERT INTO dbo.P([LoanNum],[DueDate],[TotalPayment],[PIPayment],[EscrowPmt])VALUES (123456, '12/01/2008', 1160.03, 894.78, 265.25)
INSERT INTO dbo.P([LoanNum],[DueDate],[TotalPayment],[PIPayment],[EscrowPmt])VALUES (123456, '06/01/2009', 1072.02, 806.77, 265.25)
go

-- Rules: Calculate missing payments base up on the Duedate. How can I obtain the AllPayment info
-- base up on calculate missing payments duedate.

-- Result want:
LoanNum DueDate TotalPayment PIPayment EscrowPmt AllPayment
----------- ----------------------- --------------------- --------------------- ---------- ----------
123456 2007-03-01 00:00:00.000 960.73 695.48 265.25 8646.57 -- From 03/01/2007 - 11/01/2007 9 payments
123456 2007-12-01 00:00:00.000 1054.85 789.60 265.25 6329.1 -- 12/01/2007 - 05/01/2008 6 payments
123456 2008-06-01 00:00:00.000 1143.70 878.45 265.25 6862.2 -- 06/01/2008 - 11/01/2008
123456 2008-12-01 00:00:00.000 1160.03 894.78 265.25 6960.18 -- 12/01/2008 - 05/01/2009
123456 2009-06-01 00:00:00.000 1072.02 806.77 265.25 5360.1 -- 06/01/2009 - 10/01/2009

Ex: Current due date: 03/01/2007
--Today's date = 09/03/2009 which is equate to 9 payments.

-- How can I tie a duedate column in P table to Calendar table to help me resolve this or using without the calendar table.

SELECT *
FROM P;

-- Calendar table.
/*
dt isWeekday isHoliday HolidayDescription Y FY Q M D DW MonthName DayName W UTCOffSet
----------------------- --------- --------- ------------------------------ ----------- ----------- ---- ---- ---- ---- --------- --------- ---- ---------
2007-03-01 00:00:00.000 1 0 NULL 2007 2007 1 3 1 5 March Thursday 9 5
...
2009-12-31 00:00:00.000 1 0 NULL 2009 2009 4 12 31 5 December Thursday 52 5
2010-01-01 00:00:00.000 1 1 New Year's Day 2010 2010 1 1 1 6 January Friday 52 5
....
2010-12-31 00:00:00.000 1 0 NULL 2010 2010 4 12 31 6 December Friday 52 5
2011-01-01 00:00:00.000 0 1 New Year's Day 2011 2011 1 1 1 7 January Saturday 52 5
2090-12-30 00:00:00.000 0 0 NULL 2090 2090 4 12 30 7 December Saturday 52 5
2090-12-31 00:00:00.000 0 0 NULL 2090 2090 4 12 31 1 December Sunday 1 5
*/

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-12 : 16:02:35

I will not be on long...but two things you need to do.

1) Create a Row_Number() field in table P1, left join it against P2 where P1.Row_Number = P2.Row_Number + 1
2) Use DateDiff(M, P2.Due_Date, P1.DueDate) - 1 to calculate how many months between payments

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-01-12 : 16:33:39

When I change the query as suggested, I got the output like below.
How can I update the new colum to move the result up.
Thanks.

SELECT a.*,
DATEDIFF(m, b.duedate, a.Duedate)
FROM P1 AS a
LEFT JOIN P1 AS b
ON a.rn = (b.rn + 1);

rn LoanNum DueDate TotalPayment PIPayment EscrowPmt
-------------------- ----------- ----------------------- --------------------- --------------------- --------------------- -----------
1 123456 2007-03-01 00:00:00.000 960.73 695.48 265.25 NULL
2 123456 2007-12-01 00:00:00.000 1054.85 789.60 265.25 9
3 123456 2008-06-01 00:00:00.000 1143.70 878.45 265.25 6
4 123456 2008-12-01 00:00:00.000 1160.03 894.78 265.25 6
5 123456 2009-06-01 00:00:00.000 1072.02 806.77 265.25 6

quote:
Originally posted by DP978

I will not be on long...but two things you need to do.

1) Create a Row_Number() field in table P1, left join it against P2 where P1.Row_Number = P2.Row_Number + 1
2) Use DateDiff(M, P2.Due_Date, P1.DueDate) - 1 to calculate how many months between payments

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources