Ageing SQL query show incorrect syntax

Question

I have below query which is for ageing report, but it got an error in one line of query where I cant find out the cause.

DECLARE @sql nvarchar(MAX)

SET @sql = N'SELECT
                 deb.code[Original Deb Code], 
                 IIF(deb.Code = iisginno.INNO_Debtor_Code, 
                        iisginno.Unique_Debtor_Code, deb.Code)[debcode], 
                 ISNULL(deb.CompName, iisginno.Debtor_Name)[CompName], 
                 sp.Code, sp.[description],
                 SUM(CASE WHEN DATEDIFF(Year, RefDate, GETDATE()) = 0 AND DateDiff(Month, RefDate, GETDATE()) = 0 THEN (ISNULL(inv.Amount, 0) - ISNULL(inv.PaidAmount, 0)) ELSE 0 END) AS [' + CAST(Month(GETDATE()) AS varchar(25)) + '/' + CAST(Year(GETDATE()) AS nvarchar(25)) + '] ,
            SUM(Case when DATEDIFF(Year, RefDate, getdate()) = 0 and DateDiff(Month, RefDate, getdate()) = 1 then (ISNULL(inv.Amount, 0) - ISNULL(inv.PaidAmount, 0)) else 0 end) as  [' + Cast(Month(getdate()) -1 as nvarchar(25)) + '/' + Cast(Year(getdate()) as nvarchar(25)) + '],
            SUM(Case when DATEDIFF(Year, RefDate, getdate()) = 0 and DateDiff(Month, RefDate, getdate()) = 2 then (ISNULL(inv.Amount, 0) - ISNULL(inv.PaidAmount, 0)) else 0 end)  as  [' + Cast(Month(getdate()) -2 as nvarchar(25)) + '/' + Cast(Year(getdate()) as nvarchar(25)) + '],
            SUM(Case when DATEDIFF(Year, RefDate, getdate()) = 0 and DateDiff(Month, RefDate, getdate()) = 3 then (ISNULL(inv.Amount, 0) - ISNULL(inv.PaidAmount, 0)) else 0 end)  as  [' + Cast(Month(getdate()) -3 as nvarchar(25)) + '/' + Cast(Year(getdate()) as nvarchar(25)) + '],
            SUM(Case when DATEDIFF(Year, RefDate, getdate()) = 0 and DateDiff(Month, RefDate, getdate()) = 4 then (ISNULL(inv.Amount, 0) - ISNULL(inv.PaidAmount, 0)) else 0 end)  as  [' + Cast(Month(getdate()) -4 as nvarchar(25)) + '/' + Cast(Year(getdate()) as nvarchar(25)) + '],
            SUM(Case when DATEDIFF(Year, RefDate, getdate()) = 0 and DateDiff(Month, RefDate, getdate()) = 5 then  (ISNULL(inv.Amount, 0) - ISNULL(inv.PaidAmount, 0)) else 0 end)  as  [' + Cast(Month(getdate()) -5 as nvarchar(25)) + '/' + Cast(Year(getdate()) as nvarchar(25)) + '],
            SUM(Case when DATEDIFF(Year, RefDate, getdate()) = 0 and DateDiff(Month, RefDate, getdate()) = 6 then  (ISNULL(inv.Amount, 0) - ISNULL(inv.PaidAmount, 0)) else 0 end)  as  [' + Cast(Month(getdate()) -6 as nvarchar(25)) + '/' + Cast(Year(getdate()) as nvarchar(25)) + '],
            SUM(Case when DATEDIFF(Year, RefDate, getdate()) = 0 and DateDiff(Month, RefDate, getdate()) = 7 then  (ISNULL(inv.Amount, 0) - ISNULL(inv.PaidAmount, 0)) else 0 end)  as  [' + Cast(Month(getdate()) -7 as nvarchar(25)) + '/' + Cast(Year(getdate()) as nvarchar(25)) + '],
            SUM(Case when DATEDIFF(Year, RefDate, getdate()) = 0 and DateDiff(Month, RefDate, getdate()) = 8 then  (ISNULL(inv.Amount, 0) - ISNULL(inv.PaidAmount, 0)) else 0 end)  as  [' + Cast(Month(getdate()) -8 as nvarchar(25)) + '/' + Cast(Year(getdate()) as nvarchar(25)) + '],
            SUM(Case when DATEDIFF(Year, RefDate, getdate()) = 0 and DateDiff(Month, RefDate, getdate()) = 9 then  (ISNULL(inv.Amount, 0) - ISNULL(inv.PaidAmount, 0)) else 0 end)  as  [' + Cast(Month(getdate()) -9 as nvarchar(25)) + '/' + Cast(Year(getdate()) as nvarchar(25)) + '],
            SUM(Case when DATEDIFF(Year, RefDate, getdate()) = 0 and DateDiff(Month, RefDate, getdate()) = 10 then  (ISNULL(inv.Amount, 0) - ISNULL(inv.PaidAmount, 0)) else 0 end)  as  [' + Cast(Month(getdate()) -10 as nvarchar(25)) + '/' + Cast(Year(getdate()) as nvarchar(25)) + '],
            SUM(Case when DATEDIFF(Year, RefDate, getdate()) > 0 and DateDiff(Month, RefDate, getdate()) = 11 then  (ISNULL(inv.Amount, 0) - ISNULL(inv.PaidAmount, 0)) else 0 end)  as  [' + Cast(Month(DATEADD(month, -11, getdate())) as nvarchar(25)) + '/' + Cast(Year(getdate()) - 1 as nvarchar(25)) + '],
            SUM(Case when DATEDIFF(Year, RefDate, getdate()) > 0 and DateDiff(Month, RefDate, getdate()) = 11 then  (ISNULL(inv.Amount, 0) - ISNULL(inv.PaidAmount, 0)) else 0 end)  as  [' + Cast(Month(DATEADD(month, -12, getdate())) as nvarchar(25)) + '/' + Cast(Year(getdate()) - 1 as nvarchar(25)) + '],
            SUM(Case when DATEDIFF(Year, RefDate, getdate()) > 0 and DateDiff(Month, RefDate, getdate()) > 12 then  (ISNULL(inv.Amount, 0) - ISNULL(inv.PaidAmount, 0)) else 0 end)[Older],

        
            SUM(ISNULL(inv.Amount, 0))[Amount], SUM(ISNULL(inv.PaidAmount,0))[PaidAmount] , SUM(ISNULL(tc.CreditAmount, 0))[Credit Amount], 
    
            (SUM(ISNULL(inv.Amount, 0)) - SUM(ISNULL(inv.PaidAmount,0)))[Outstandings], deb.Terms, ISNULL(deb.creditlimit, 0)[creditlimit] 
        from 
            [INNO_ERPDB].dbo.[Acc_Invoice] inv 
        left join 
        (
            select 
                crede.Acc_InvoiceId,  SUM(crede.Amount)[CreditAmount] 
            from
                [INNO_ERPDB].dbo.Acc_CreditNoteDe crede 
            group by 
                crede.Acc_InvoiceId 
        )tc 
        on 
            tc.Acc_InvoiceId = inv.id 
        left join 
            [INNO_ERPDB].dbo.[ACC_DEBTOR] deb 
        on 
            inv.Acc_DebtorId = deb.Id 
        left join 
            [INNO_ERPDB].dbo.[Acc_SalesMan] sp 
        on 
            inv.Acc_salesmanID = sp.Id 
        left join 
            inno_IISG_INNO_Debtor_Match iisginno 
        on 
            iisginno.INNO_Debtor_Code = deb.Code 
        where 

            deb.Active = ''Y''
        and 
            (inv.Acc_StatusID = 2 or inv.Acc_StatusID = 5) 
        
        and
            sp.Code = ''YONG''
        group by 
            deb.code, IIF(deb.Code = iisginno.INNO_Debtor_Code, iisginno.Unique_Debtor_Code, deb.Code), 
            ISNULL(deb.CompName, iisginno.Debtor_Name), sp.Code, sp.[description], 
            deb.Terms, deb.creditlimit 
            order by 
            ISNULL(deb.CompName, iisginno.Debtor_Name)'

        EXEC(@sql)

I get an error Msg 102, Level 15, State 1, Line 59 Incorrect syntax near 'Deb'.

when I remove this query, it work fine

SUM(Case when DATEDIFF(Year, RefDate, getdate()) > 0 and DateDiff(Month, 
RefDate, getdate()) = 11 then  (ISNULL(inv.Amount, 0) - 
ISNULL(inv.PaidAmount, 0)) else 0 end)  as  [' + 
Cast(Month(DATEADD(month, -12, getdate())) as nvarchar(25)) + '/' + 
Cast(Year(getdate()) - 1 as nvarchar(25)) + '],

I cant figure out and no idea what that line of query error. Please help

Can you please provide the schema of your table with minimum fields so that your error can be reproduced? probably with some dummy data — TBA, Nov 09 '21 at 03:44
`print (@sql)` then copy message and paste in new `SSMS` editor. It will show the `syntax error`. — Nagib Mahfuz, Nov 09 '21 at 04:56
As per prior comment, take the basic debugging setp of printing the resultant query out. Don't create an enormous dynaic string and expect it to actually work without printing and inspecting it. In fact.... why are you even doing it this way, why not just run the SQL directly? — Nick.Mc, Nov 09 '21 at 06:14
I want a dynamic column header like 11/2021, 10/2021 and so on. If I remove all this as [' + Cast(Month(DATEADD(month, -12, getdate())) as nvarchar(25)) + '/' + Cast(Year(getdate()) - 1 as nvarchar(25)) + '], it will no error. I run the query in SSMS and it didn't has any error. — Bubble Bub, Nov 09 '21 at 06:59
[https://stackoverflow.com/questions/2502734/my-varcharmax-field-is-capping-itself-at-4000-what-gives](https://stackoverflow.com/questions/2502734/my-varcharmax-field-is-capping-itself-at-4000-what-gives) — Павел Сивоплясов, Nov 09 '21 at 14:17

Ageing SQL query show incorrect syntax

0 Answers0