troubleshooting Question

SQL - Pivot table query

Avatar of Zack
ZackFlag for Australia asked on
SQLMicrosoft SQL Server
4 Comments1 Solution8 ViewsLast Modified:
Hi EE,

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)
Works fine if i'm comparing 'months' but if I compare 'days' all get is a single column

Any assistance is welcome.

Thank you. 
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”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.

-Mike Kapnisakis, Warner Bros