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