Totals by Month/Year

I ussualy like to compare months sales, so for all there that do like to do that…

Report:

[Totales por Mes:2,1, 2, 2, 2, 2]
>Mes|Ano|Neto|Taxa|Desc|Total
@@TotalesxMes
>TOTAL||@@GranTotal

SQL:

@@Totalesxmes
SELECT
  DATENAME(MONTH, [Date]) as MES,
  DATEPART(YEAR, [Date]) as ANO,
  FORMAT(SUM(case 
      when Cal.CalculationTypeId = 1 then 0
	  else T.TotalAmount end)-SUM(Cal.CalculationAmount),'C') as NetoTicket,
  FORMAT(SUM(case 
      when Cal.CalculationTypeId = 4 then Cal.CalculationAmount
	  else 0 end),'C') as Taxa,
  FORMAT(SUM(case 
      when Cal.CalculationTypeId = 1 then Cal.CalculationAmount 
	  else 0 end),'C') as Desconto,
  FORMAT(SUM(case 
      when Cal.CalculationTypeId = 1 then 0
	  else T.TotalAmount end),'C') as TotalTicket

FROM Tickets AS T

LEFT OUTER JOIN Calculations as Cal on Cal.TicketId = T.Id

GROUP BY DATENAME(MONTH, [Date]),DATEPART(YEAR, [Date])

ORDER BY DATEPART(YEAR, [Date]), min(DATEPART(MM, [Date]))

and

SELECT 
  FORMAT(SUM(case 
      when Cal.CalculationTypeId = 1 then 0
	  else T.TotalAmount end)-SUM(Cal.CalculationAmount),'C') as NetoTicket,
  FORMAT(SUM(case 
      when Cal.CalculationTypeId = 4 then Cal.CalculationAmount
	  else 0 end),'C') as Taxa,
  FORMAT(SUM(case 
      when Cal.CalculationTypeId = 1 then Cal.CalculationAmount 
	  else 0 end),'C') as Desconto,
  FORMAT(SUM(case 
      when Cal.CalculationTypeId = 1 then 0
	  else T.TotalAmount end),'C') as TotalTicket

FROM Tickets AS T

LEFT OUTER JOIN Calculations as Cal on Cal.TicketId = T.Id
2 Likes

dear @gerlandog
How do I filter it to see only certain year?

You could filter by year by using a WHERE clause after all JOIN statements, and before GROUP BY and ORDER BY. Something like:

WHERE DATEPART(YEAR, [Date]) = '2014'

The above hard-codes the year in the SQL, but you could have it be dynamic via the {Start} and {End} tags that are available in the Report filters, as such:

WHERE DATEPART(YEAR, [Date]) >= DATEPART(YEAR,'{Start}') AND DATEPART(YEAR, [Date]) <= DATEPART(YEAR,'{End}')

The above would theoretically allow you to combine more than 1 year (i.e start at 2013 and end at 2014). A simpler approach which would allow only a single year would be as follows:

WHERE DATEPART(YEAR, [Date]) = DATEPART(YEAR,'{Start}')

Again, the WHERE clause should be placed after JOIN statements, and before any GROUP or ORDER BY clauses …

FROM Tickets AS T

LEFT OUTER JOIN Calculations as Cal on Cal.TicketId = T.Id

WHERE DATEPART(YEAR, [Date])>=DATEPART(YEAR,'{Start}') AND DATEPART(YEAR, [Date])<=DATEPART(YEAR,'{End}')

GROUP BY DATENAME(MONTH, [Date]),DATEPART(YEAR, [Date])

ORDER BY DATEPART(YEAR, [Date]), min(DATEPART(MM, [Date]))
1 Like

@QMcKay Thank you so much