chinye2020 (9/19/2012)
...
This work perfectly like the CTE, well done, but, how to edit sql query only show AccNo <> 1 ??
...
Easy - but first, we need to sort out your dates. You do yourself no favours by changing the date arithmetic back to the original each time I attempt to correct it. This...
where convert(Date,ti.Transaction_Date, 105) BETWEEN convert(Date,'16-09-2012', 105) AND convert(Date,'30-09-2012', 105)
will result in very poor performance, because even if there is an index on Transaction_date, SQL Server will not be able to use it. You need to match the datatype of the date constants to the column, not the other way around. In order to do that, you need to know the data type of Transaction_Date, and if it is a datetime, you need to know if the time component has values. Can you answer this?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden