MS SQL复杂查询 [英] MS SQL Complex Query

问题描述

亲爱的，

我有一个案例如下。



表和数据





客户姓名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





我要求的结果如下



客户名称OnAccountAmt BalanceAmt untilCollAge

XYZ 50,0000 50,0000 30

XYZ 50,0000 30,0000 5

XYZ 50,0000 20,0000 29 [直到白天]





请在MS SQL查询的帮助下帮助我获得上述结果。



谢谢，

Sunil Kumar Singh

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

推荐答案

应该是这样的：



（假设该表名为tblData ）

Should be something like this:

(Assuming the table is called tblData)

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.

如果我已正确理解你的问题，这是一个分析函数LAG（）非常有用的例子。

可悲的是它在SQL Server中还不存在（还）。



可以在这里找到解决方法 [ ^ ]。

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[^].

谢谢回复但没有得到所需的结果，根据您的MS SQL，我们必须从重要的行数据计算余额 - 下一行数据，也类似年龄。

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.

这篇关于MS SQL复杂查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！