Payment Analysis - 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 2012 Forums

Transact-SQL (2012)

Payment Analysis

Author

Topic

hayashiryo
Starting Member

46 Posts

Posted - 2014-09-18 : 04:11:33

Hi guys. I need some help.

I need to calculated the average number of days taken by a customer to pay us.

Example:


Customer	Amount	# Days
------------------------------
A		 $500	  22
A		$1200	  21
A		  $50	   4

Where # Days = the number days taken for Customer A to make full payment

In the above example, I have a formula to calculate the average days customer A takes to make full payment:

Sum((Amount * #Days)) / Sum(Amount)
= (500*22)+(1200*21)+(50*4) / (500+1200+50)
= 20.8 days

My table format is below
Note: It contains all customers, not just only 1 customer


Cust	OrderDate	PaymentDate	Amount
-------------------------------------------------
A	2013-08-02	2013-08-25	8272.0000
A	2013-08-12	2013-08-15	234.0000
B	2013-07-15	2013-08-02	129.0000
B	2013-08-11	2013-08-21	9238.0000
C	2013-06-25	2013-10-02	10293.0000
C	2013-07-05	2013-10-02	295.0000
C	2013-07-29	2013-10-02	12.0000

The results I need is


Custr	Total Amount	Avg Days
---------------------------------
A	8506		22.44980014
B	9367		10.11017402
C	10600		98.68320755

Where
Total Amount = sum(Amount)
Avg Days = Formula indicated above

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-09-18 : 06:59:27

[code]SELECT Custr,
SUM(Amount) AS [Total Amount],
SUM((Amount * DATEDIFF(DAY, OrderDate, PaymentDate))) / Sum(Amount) AS [Avg Days]
FROM table
GROUP BY Custr[/code]

KH
[spoiler]Time is always against us[/spoiler]

hayashiryo
Starting Member

46 Posts

Posted - 2014-09-22 : 21:17:08

Hey Khtan. Thanks, it works nicely!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources