Using Ticket Tag in Custom Reports:How to Sum Ticket Tag Value?

Version 4 V4 Question
20

Wrap the whole query with an aggregate-type query:

SELECT SUM([GuestCount]) as [GuestCountTotal]
FROM
(

SELECT 
CASE isnull(charindex('{"TN":"Guest","TT":1,"TV":"',[TicketTags]),0)
WHEN 0 THEN 0
ELSE
 (
CASE ISNUMERIC(substring([TicketTags],charindex('{"TN":"Guest","TT":1,"TV":"',[TicketTags])+len('{"TN":"Guest","TT":1,"TV":"'),2))
       WHEN 1 THEN convert(int,substring([TicketTags],charindex('{"TN":"Guest","TT":1,"TV":"',[TicketTags])+len('{"TN":"Guest","TT":1,"TV":"'),2))
       ELSE convert(int,substring([TicketTags],charindex('{"TN":"Guest","TT":1,"TV":"',[TicketTags])+len('{"TN":"Guest","TT":1,"TV":"'),1))
       END
       )
END as [GuestCount]
FROM [Tickets]

) gcsum
2 Likes