Skip to Content
Jun 29, 2016 at 06:39 PM

Count of invoices in pivot by year and month is not working


This is counting the number of invoices incorrectly. I do not want DocNum as that is the total. I want to show the individual lines using T0.[U_DVIInvName] which is a field on the line. It shows about 20 a month which is wrong there should be about 3K. It works with out the pivot, but not when I pivot. Any suggestions. SELECT * FROM ( SELECT year(T1.DocDate) as [Year], month(T1.Docdate)as [Month] , Count (T0.[U_DVIInvName]) as 'Invoice Count' FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry Group by T1.DocDate ) as s PIVOT (Count( s. [Invoice Count]) FOR S.[Month] IN ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12]) ) as P Order By Year