CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2008-r2sql-server-2012
Vidya Vikas Mishra
Vidya Vikas Mishra

Reputation: 13

Query works in sql server 2012 but not in server 2008

I created below query it's works good in sql server 2012 but not working in sql server 2008 or 2008R2. Please help me to resolve this.

Select Batchno, BatchId, Transactiontype, LoanAccountNumber, TransactionId
    , StaticDetailId, Name, Comment, BatchTotal
    , case when Amount<0 then Amount*-1 
           Else Amount End as Amount
    , ((BatchTotal) - (SUM(case when Amount<0 then Amount*-1 Else Amount End) 
           OVER(order BY Batchno ROWS BETWEEN 1000 Preceding and current row))) AS BatchBal 
From (SELECT row_number() over (order by BO.BatchID) as [BatchNo]
           , BO.BatchID, sd.TransactionType, tra.LoanAccountNumber, tra.TransactionID
           , tra.StaticDetailID,tra.Name,tra.Comment, BO.BatchTotal,tra.Amount
        FROM BackOfficeBatchTransactionDetails tra
        join BackOfficeBatchDetails BO 
            on BO.BatchID=tra.BatchID and tra.isactive=1 and tra.isdeleted=0 
        join StaticDetails sd on sd.StaticDetailID=tra.StaticDetailID and sd.isactive=1
        where BO.isactive=1 and BO.isdeleted=0 AND BO.BatchId=@BatchID
     ) AS G

Upvotes: 0

Views: 107

Answers (1)

SchmitzIT
SchmitzIT

Reputation: 9552

The reason for the query failing is this bit of the statement:

ROWS BETWEEN 1000 Preceding and current row

The ROWS (and RANGE keywords were only introduced in SQL Server 2012:

http://www.pawlowski.cz/2012/06/ms-sql-2012-window-functions-introduction/

Upvotes: 3

Related Questions