Help with DateDiff function - 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 2008 Forums

Transact-SQL (2008)

Help with DateDiff function

Author

Topic

eastwest
Starting Member

3 Posts

Posted - 2010-03-30 : 15:18:46

Hi Everyone,


I have a table called "PaymentsSchedule" as below:

ID	Sequence	ScheduleDate			Amount	

49097	1		2008-01-10 00:00:00.000		333.33	
49098	2		2009-01-10 00:00:00.000		333.33	
49099	3		2010-01-10 00:00:00.000		333.34	

------------------------------------------------------------------------------------------------
I have another table called "Payments"

ID		PaymentDate			PaymentAmount 	OrganizationID


1399999		2007-10-31 00:00:00.000 	50.00		1

----------------------------------------------------------------------

According to the Payments table, customer made a payment of only $50.00 on 2007-10-31 00:00:00.000

Now I want to find out the number of days due for a payment.

Please help me.

Thanks in advance.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-03 : 12:49:10

Hi,

What is the relationship between the two tables.

What is the output you are expecting ( with respect to the sample data posted by you).

Bohra,
I am here to learn from masters and help new bees in learning.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-03 : 12:54:21

seem like this:-


SELECT DATEDIFF(dd, p.PaymentDate,p1.MinDate) AS DaysDue
FROM Payments p
CROSS APPLY (SELECT MIN(ScheduleDate) AS MinDate
             FROM PaymentSchedule
             WHERE ScheduleDate>PaymentDate
            ) p1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources