Dynamic SQL table columns for dates in Aging report, SQL Server

 

How to create Dynamic SQL table columns for dates in Aging report JD Edwards, SQL Server 

DECLARE @SQL_STR VARCHAR(8000)

DECLARE @DueDate VARCHAR(80)

DECLARE @DueDate14 VARCHAR(80)

DECLARE @DueDate28 VARCHAR(80)

DECLARE @DueDateAfter28 VARCHAR(80)

SET @DueDate =  '['+'Due as of ' +CONVERT(VARCHAR(10), GETDATE()+1, 101)+'],'

SET @DueDate14 =  '['+'Due as of ' +CONVERT(VARCHAR(10), GETDATE()+14, 101)+'],'

SET @DueDate28 =  '['+'Due as of ' +CONVERT(VARCHAR(10), GETDATE()+28, 101)+'],'

SET @DueDateAfter28 =  '['+'Due After ' +CONVERT(VARCHAR(10), GETDATE()+28, 101)+'],'

SET @SQL_STR = 

'SELECT RPAN8 AS Supplier_Number, sup.ABALPH AS Supplier_name, 

RPPST AS Pay_Stat, RPPYIN AS Pay_Inst, RPAN8 AS Payee_Number, PR.ABALPH AS Payee_Name,

RPDCT AS Doc_Type, RPDOC AS Doc_Number, RPSFX AS ITM, RPKCO AS CO, RPVINV AS Invoice_Number,

[dbo].[JDT_JTG](RPDIVJ) AS Invoice_Date,

[dbo].[JDT_JTG](RPDDJ) AS Due_Date,

CASE WHEN [dbo].[JDT_JTG](RPDDJ) BETWEEN CONVERT(VARCHAR(10), GETDATE(), 101) and  CONVERT(VARCHAR(10), GETDATE()+1, 101)

     THEN RPAG/100

ELSE 0 END '+ @DueDate + '

CASE WHEN [dbo].[JDT_JTG](RPDDJ) BETWEEN CONVERT(VARCHAR(10), GETDATE()+2, 101) and  CONVERT(VARCHAR(10), GETDATE()+14, 101)

     THEN RPAG/100

ELSE 0 END '+ @DueDate14 + '

CASE WHEN [dbo].[JDT_JTG](RPDDJ) BETWEEN CONVERT(VARCHAR(10), GETDATE()+15, 101) and  CONVERT(VARCHAR(10), GETDATE()+28, 101)

     THEN RPAG/100

ELSE 0 END '+ @DueDate28 + '

CASE WHEN [dbo].[JDT_JTG](RPDDJ) > CONVERT(VARCHAR(10), GETDATE()+28, 101) 

     THEN RPAG/100

ELSE 0 END AS '+ @DueDateAfter28 + '

CASE WHEN [dbo].[JDT_JTG](RPDDJ) >= CONVERT(VARCHAR(10), GETDATE(), 101) 

     THEN RPAG/100

ELSE 0 END AS Total_Due

FROM MFDB.PRODDTA.F0411

LEFT OUTER JOIN MFDB.PRODDTA.F0101 Sup ON Sup.ABAN8 = RPAN8

LEFT OUTER JOIN MFDB.PRODDTA.F0101 PR ON PR.ABAN8 = RPAN8

WHERE RPFY = 22 

--AND rpan8 = 642564 

AND RPPST = ''A'';

'

--PRINT (@SQL_STR)

EXEC (@SQL_STR)