IF OBJECT_ID(N'tempdb..#TempTableName') IS NOT NULL
BEGIN
DROP TABLE #TempTable
dECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)
declare @fromDate datetime
set @fromDate = '2017-4-1'
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(month,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;
Msg 102, Level 15, State 1, Line 33Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.