asked on SQL - Pivot table query
Hi EE,
I have this query:
The line
Any assistance is welcome.
Thank you.
I have this query:
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;
The line
dateadd(month,12,@FromDate)
Any assistance is welcome.
Thank you.
SQLMicrosoft SQL Server
Last Comment
Ryan Chong
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Hi Ryan,
I got it:
Thank you
I got it:
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), 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;
Thank you
emmm, I could see it wrongly. So disregard my first comment, but below a POC which should working fine, by applying:
Full test scripts:
dateadd(day,12,@FromDate)
Full test scripts:
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.