1

For a report of sales, i'm using mssql TRANSFORM / PIVOT operation through VBA - ADO

I need all monthes and years printed, even if there is no sales at all. First i've this query :

SQLq = "TRANSFORM SUM([Feuil$].[PRICE]) SELECT [Feuil$].[MONTH] " & _
" FROM [Feuil$]" & _
" WHERE [Feuil$].[CODEFC] = '" & CodeFC & "'" & _
" GROUP BY [Feuil$].[MONTH]" & _
" PIVOT [Feuil$].[YEAR] " & _
" IN(2015,2016,2017,2018,2019,2020,2021,2022,2023,2024,2025)"

This one works fine, having all years as column, even those with no sales, but if I have monthes without sales, i'll end up with missing rows

I tried to force the list of monthes using a JOIN statement but Office report a syntax error :

SQLq = "TRANSFORM SUM([Feuil$].[PRICE]) SELECT [Feuil$].[MONTH] " & _
" FROM [Feuil$]" & _
" RIGHT JOIN (1,2,3,4,5,6,7,8,9,10,11,12) AS [allmonthes] " & _
" ON ([Feuil$].[MONTH] = [allmonthes])" & _
" WHERE [Feuil$].[CODEFC] = '" & CodeFC & "'" & _
" GROUP BY [Feuil$].[MONTH]" & _
" PIVOT [Feuil$].[YEAR] " & _
" IN(2015,2016,2017,2018,2019,2020,2021,2022,2023,2024,2025)"

This one doesn't work, reporting an error on JOIN

I tried vALUES before the monthes list and other ideas not to avail... How should I change the SQL ? Is there a better idea in order to have all 12 monthes as rows even those without grouped values ?

Community
  • 1
  • 1
Proger_Cbsk
  • 362
  • 3
  • 12
  • What error do you get? Might be because of https://stackoverflow.com/questions/8370114/referring-to-a-column-alias-in-a-where-clause? – Ocaso Protal Apr 11 '18 at 09:02
  • I'd recommend trying to parametrise your SQL as well. I don't know where the value of `CodeFC` is coming from, but you could be opening yourself up for injection. – Thom A Apr 11 '18 at 09:05
  • @Ocaso The error is in the `JOIN` part of the statement, I use a wrong syntax for the wanted values `(1,2,3 ...` . in mysql i'd use a temporary table or somewhat to do the `RIGHT JOIN` but i dont know how to do it in the ADO "mssql" language. Maybe if i put also the values in the `ON` clause it could work... – Proger_Cbsk Apr 11 '18 at 11:19

0 Answers0