Difference in reports

Version 4 V4 Question
1

Dear @emre, I know that for sure I may have done some error, but I have been trying to find it out all day without luck… I think that the problem could be in the join with calculations…

screen:

e4ca5807d6809172.png1280×800 55.1 KB

SQL (first part of the report)

SELECT 

  min(substring(cast(TicketTags as nvarchar(4000)),
            CharIndex('{"TN":"Garçon Pizzaria","TT":0,"TV":"',cast(TicketTags as nvarchar(4000)))+37,
             CharIndex('"',cast(TicketTags as nvarchar(4000)),
                CharIndex('{"TN":"Garçon Pizzaria","TT":0,"TV":"',cast(TicketTags as nvarchar(4000)))+37
              )- CharIndex('{"TN":"Garçon Pizzaria","TT":0,"TV":"',cast(TicketTags as nvarchar(4000)))-37) 
        ) as [Garçon],
        
  SUM(CONVERT(INT,substring(cast(TicketTags as nvarchar(4000)),
            CharIndex('{"TN":"Pessoas","TT":1,"TV":"',cast(TicketTags as nvarchar(4000)))+29,
             CharIndex('"',cast(TicketTags as nvarchar(4000)),
                CharIndex('{"TN":"Pessoas","TT":1,"TV":"',cast(TicketTags as nvarchar(4000)))+29
              )- CharIndex('{"TN":"Pessoas","TT":1,"TV":"',cast(TicketTags as nvarchar(4000)))-29))) 
         as [Pessoas],        

  FORMAT(SUM(T.TotalAmount)-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,

  FORMAT(SUM(Cal.CalculationAmount),'C') as TotalTaxa

FROM Tickets AS T

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

WHERE Date > '{Start}' and Date < '{End}'

GROUP BY (substring(cast(TicketTags as nvarchar(4000)),
            CharIndex('{"TN":"Garçon Pizzaria","TT":0,"TV":"',cast(TicketTags as nvarchar(4000)))+37,
             CharIndex('"',cast(TicketTags as nvarchar(4000)),
                CharIndex('{"TN":"Garçon Pizzaria","TT":0,"TV":"',cast(TicketTags as nvarchar(4000)))+37
              )- CharIndex('{"TN":"Garçon Pizzaria","TT":0,"TV":"',cast(TicketTags as nvarchar(4000)))-37) 
        )

ORDER BY (substring(cast(TicketTags as nvarchar(4000)),
          CharIndex('{"TN":"Garçon Pizzaria","TT":0,"TV":"',cast(TicketTags as nvarchar(4000)))+37,
          CharIndex('"',cast(TicketTags as nvarchar(4000)),
          CharIndex('{"TN":"Garçon Pizzaria","TT":0,"TV":"',cast(TicketTags as nvarchar(4000)))+37
          )- CharIndex('{"TN":"Garçon Pizzaria","TT":0,"TV":"',cast(TicketTags as nvarchar(4000)))-37) 
          )

Report (second part of the screen)

[!Comisiones Pivot:2,2, 2, 2, 2]
>Garçon|[N]Pessoas|[C]Desconto|[C]Comisiones|[C]Total
{REPORT TICKET DETAILS:TT.Garçon Pizzaria,TT.Pessoas,CA.Desconto,CA.Taxa Serviço,TN.TotalAmount}

THANKS!!!

G.

2

The issue is your commissions… in the report it shows 507 the table 505. Its specifically your name… the others were fine… maybe that gives you starting point to find it.

3

exactly that is what I am saying, there are two records more in my SQL and I think that is form the calculations join, but I dont know how to filter it…

as you can see, the records that I am consulting are all from the TICKETS table…

Thanks!!!

G.

4

Scary looking piece of SQL. Difficult for someone else to test.

If you think the LEFT OUTER JOIN is the reason for picking up extra records, have you tried changing it to JOIN instead?

5

its worst!!!

9eab5ab37f6e738a.png1280×800 53.2 KB

G

6

seem to have found the issue… not a problem, just that the join brings me a ticket that has 2 calculations, one for the TAXA SERVIÇO and one for a DISCOUNT, and I was adding this two records when I should have filtered one…

THANKS!!!

G.

2 Likes