IF OBJECT_ID(N'tempdb..#TempTableName') IS NOT NULL
BEGIN
DROP TABLE #TempTable
END
dECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)
declare @fromDate datetime
set @fromDate = '2018-04-01'
select * into #TempTable from
(SELECT LEFT(CONVERT(VARCHAR, e.ACCOUNTINGDATE, 120), 7) as TransDate, e.SUBLEDGERVOUCHERDATAAREAID as company, m.MAINACCOUNTID as ID,
(a.ACCOUNTINGCURRENCYAMOUNT)AS BL
FROM GENERALJOURNALACCOUNTENTRY a
JOIN GENERALJOURNALENTRY e ON e.[PARTITION] = a.[PARTITION] AND e.RECID = a.GENERALJOURNALENTRY
join DIMENSIONATTRIBUTEVALUECOMBINATION davc on a.LEDGERDIMENSION = davc.RECID and e.[PARTITION] = davc.[PARTITION]
join MAINACCOUNT m on davc.MAINACCOUNT = m.RECID and davc.[PARTITION] = m.[PARTITION]
JOIN FISCALCALENDARPERIOD p ON p.[PARTITION] = e.[PARTITION] AND p.RECID = e.FISCALCALENDARPERIOD
WHERE p.type = 1-- 0=Opening,1=Operating,2=Closing
and e.accountingdate >= @FromDate and e.accountingdate < dateadd(dayofyear,12,@FromDate)
)AS A
--select * from #TempTable
SELECT @cols = COALESCE(@cols + ',' ,'' ) + QUOTENAME(TransDate)
FROM #TempTable
GROUP BY QUOTENAME(TransDate)
Order by QUOTENAME(TransDate)
SELECT @query = N'
SELECT * FROM #TempTable PIVOT (SUM(BL) FOR TransDate
IN (' + @cols + N') ) AS pvt order by company'
EXEC sp_executesql @query;
dateadd(month,12,@FromDate)
Works fine if i'm comparing 'months' but if I compare 'days' all get is a single columnIF OBJECT_ID(N'tempdb..#TempTableName') IS NOT NULL
BEGIN
DROP TABLE #TempTable
END
dECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)
declare @fromDate datetime
set @fromDate = '2018-04-01'
select * into #TempTable from
(SELECT LEFT(CONVERT(VARCHAR, e.ACCOUNTINGDATE, 120), 10) as TransDate, e.SUBLEDGERVOUCHERDATAAREAID as company, m.MAINACCOUNTID as ID,
(a.ACCOUNTINGCURRENCYAMOUNT)AS BL
FROM GENERALJOURNALACCOUNTENTRY a
JOIN GENERALJOURNALENTRY e ON e.[PARTITION] = a.[PARTITION] AND e.RECID = a.GENERALJOURNALENTRY
join DIMENSIONATTRIBUTEVALUECOMBINATION davc on a.LEDGERDIMENSION = davc.RECID and e.[PARTITION] = davc.[PARTITION]
join MAINACCOUNT m on davc.MAINACCOUNT = m.RECID and davc.[PARTITION] = m.[PARTITION]
JOIN FISCALCALENDARPERIOD p ON p.[PARTITION] = e.[PARTITION] AND p.RECID = e.FISCALCALENDARPERIOD
WHERE p.type = 1-- 0=Opening,1=Operating,2=Closing
and e.accountingdate >= @FromDate and e.accountingdate < dateadd(day,12,@FromDate)
)AS A
--select * from #TempTable
SELECT @cols = COALESCE(@cols + ',' ,'' ) + QUOTENAME(TransDate)
FROM #TempTable
GROUP BY QUOTENAME(TransDate)
Order by QUOTENAME(TransDate)
SELECT @query = N'
SELECT * FROM #TempTable PIVOT (SUM(BL) FOR TransDate
IN (' + @cols + N') ) AS pvt order by company'
EXEC sp_executesql @query;
dateadd(day,12,@FromDate)
to compare with dates.declare @fromDate datetime = '2018-04-01', @cols varchar(100), @query nvarchar(max)
drop table if exists #TempTable
;with e as
(
SELECT 100 BL, '2018-04-01' accountingdate, LEFT(CONVERT(VARCHAR, '2018-04-01', 120), 7) TransDate, 'A' company union
SELECT 120 BL, '2018-04-15' accountingdate, LEFT(CONVERT(VARCHAR, '2018-04-15', 120), 7) TransDate, 'A' company
)
select * into #TempTable from e
WHERE e.accountingdate >= @FromDate and e.accountingdate < dateadd(day,12,@FromDate)
SELECT @cols = COALESCE(@cols + ',' ,'' ) + QUOTENAME(TransDate)
FROM #TempTable
GROUP BY QUOTENAME(TransDate)
Order by QUOTENAME(TransDate)
SELECT @query = N'
SELECT * FROM #TempTable PIVOT (SUM(BL) FOR TransDate
IN ('+@cols+N') ) AS pvt order by company '
EXEC sp_executesql @query;
Not sure what you mean can you clarify with example?
Thank you.