CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Ayman
Ayman

Reputation: 872

cte using sql server

When I try to run the below query

  seq = row_number() over 
(
  partition by t.CustID
  order by t.InvoiceID, 
           t.Date,
           CASE WHEN t.S_Type = 'Receipt Voucher' THEN 1 ELSE 2 END
 )

;

WITH cte
AS (
    SELECT  CustID,
        [InvoiceID],
        S_Type,
        DATE,
        Debit,
        Credit,
        seq = row_number() OVER (
            PARTITION BY CustID,
            ORDER BY InvoiceID,
                DATE,
                CASE 
                    WHEN S_Type = 'Receipt Voucher'
                        THEN 1
                    ELSE 2
                    END
            )
    FROM Statement
    )
SELECT c.[InvoiceID],
    c.S_Type AS Type,
    c.DATE,
    .Debit,
    c.Credit,
    b.Balance
FROM cte c
CROSS APPLY (
    SELECT Balance = SUM(Debit) - SUM(Credit)
    FROM cte AS x
    WHERE x.CustID = c.CustID
        AND x.seq <= c.seq
    ) b
WHERE c.CustID = '48'
    AND DATE BETWEEN '2015-01-01'
        AND '2016-01-01'
ORDER BY seq

I get the following error:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '='. Msg 156, Level 15, State 1, Line 21 Incorrect syntax near the keyword 'ORDER'.

Upvotes: 0

Views: 82

Answers (1)

Siyual
Siyual

Reputation: 16917

The problem is here in your Cte:

...
seq = row_number() OVER (
        PARTITION BY CustID,
        ORDER BY InvoiceID,
            DATE,
            CASE 
                WHEN S_Type = 'Receipt Voucher'
                    THEN 1
                ELSE 2
                END
        )
...

You have a , after PARTITION BY CustID, the correct syntax is without that comma:

...
seq = row_number() OVER (
        PARTITION BY CustID
        ORDER BY InvoiceID,
            DATE,
            CASE 
                WHEN S_Type = 'Receipt Voucher'
                    THEN 1
                ELSE 2
                END
        )
...

Another issue you have in your query is that you do not have an alias for the Debit field in the final SELECT:

SELECT c.[InvoiceID],
    c.S_Type AS Type,
    c.DATE,
    .Debit,  <-- Here
    c.Credit,
    b.Balance

The full query would be as follows:

;With Cte As
(
    Select  CustId, InvoiceId, S_Type, Date, Debit, Credit,
            Seq = Row_Number() Over (   Partition By    CustId 
                                        Order By        InvoiceId, 
                                                        Date, 
                                                        Case 
                                                            When S_Type = 'Receipt Voucher' 
                                                                Then 1 
                                                            Else 2 
                                                        End
                                    )
    From    Statement
)
Select      C.InvoiceID,
            C.S_Type    As Type,
            C.Date,
            C.Debit,
            C.Credit,
            B.Balance
From        Cte     C
Cross Apply 
(
    Select  Balance = Sum(X.Debit) - Sum(X.Credit)
    From    Cte     X
    Where   X.CustId = C.CustId
    And     X.seq <= C.seq
) B
Where   C.CustID = '48'
And     C.Date Between '2015-01-01' And '2016-01-01'
Order By C.seq

Upvotes: 3

Related Questions