Apply Clause in SQL

Have you were felt need for passing outer query field value to inner or Sub Query as a where clause or join and have sub query field in outer Select.

With conventional SQL it’s not possible. This can be done using SQL SERVER 2005 and 2008 new feature called as Outer Apply. It works like left outer join with outer Query field in sub Query.

Sample:

Note: In this sample I am trying to get pervious total invoice value for that customer as on bill date

Select Srno,Amount ,DocDT, PreAmount From tranSales sl
Outer Apply
(
Select SUM(Amount) PreAmount From tranSales PrSl
where COID = 1 and PrSl.DocDt < sl.DocDt and PrSl.CustomerID = sl.CustomerID
Group by CustomerID
) PreSl
where COID = 1 and sl.CustomerID = 1 Order by SRNO

I am sure this will help lot of peoples.

Switin Kotian