MS SQL Complex Query

Question

0.00/5 (No votes)

See more:

Dear All,
I have one case as below.

Table & Data

CustomerName OnAcountAmt ReceivedDate CollAmt CollDate
XYZ 50,0000 01/01/2010 0 ---
XYZ 50,0000 01/01/2010 20,0000 30/01/2010
XYZ 50,0000 01/01/2010 10,0000 05/02/2010

I have required the result as below

CustomerName OnAccountAmt BalanceAmt tillCollAge
XYZ 50,0000 50,0000 30
XYZ 50,0000 30,0000 5
XYZ 50,0000 20,0000 29 [till day]

Please help me to get the above result, with the help of MS SQL Query.

Thanks,
Sunil Kumar Singh

Posted 6-Apr-10 19:58pm

Singh Kumar Sunil

Add a Solution

3 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mustafa Ismail Mustafa · Answer 1 · 2010-04-06T23:46:00

Should be something like this:

(Assuming the table is called tblData)

SQL

SELECT
	T.CusotmerName, T.OnAccountAmt, (T.OnAccountAmt - T.ColAmt) as [Balance Amt], 
	DATEDIFF(day,T.ReceivedDate, T.ColDate) as [TilColAge]
	
FROM
	tblData as T

If that answers your question, then don't forget to mark the question as answered.

Jörgen Andersson · Answer 2 · 2010-04-07T11:19:00

Solution 3

If I have understood your question correctly, this is an example of where the analytic function LAG() would have been very useful.
Sadly enough it doesn't exist in SQL Server (yet).

A workaround can be found here[^].

Posted 7-Apr-10 11:19am

Jörgen Andersson

Singh Kumar Sunil · Answer 3 · 2010-04-07T00:15:00

Solution 2

thanks for reply but not get the required result, as per your MS SQL, we have to calculate Balance Amount from perivous row data - next row data , similary age also.

Posted 7-Apr-10 0:15am

Singh Kumar Sunil

MS SQL Complex Query

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0

MS SQL Complex Query

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us