so waiting for the new version
@madiha, can you copy and paste the JSON string here and indicate which Tag within that string relates to the value you are trying to retrieve?
I don’t think it should matter where the Tag lands in the JSON string, so long as we have a definitive way to identify it, we should be able to parse it.
EDIT: never mind, it appears you are looking for the value shown below, correct?
"TN":"Guest","TT":0,"TV":"thisvaluehere"
If that is the case, notice how I omitted the opening brace {. If you do the same, it won’t matter where the value lands in the string - it could be first, middle, last…
EDIT2: unless the value after TT (0) can change? If that is the case, you’re correct, it won’t work as you’ve designed it, but you could get around that too.
Let me know how it works out…
well sorry to say but i dont know the function of this value can please state and i have followed the tutorial tracking number of persons
in the system its compulsory to state the number of pax when taking order
i have tried with omittng the brace but it still gave me error…because it wasnt finding from whole string
This should work if the Guest count < 10
SELECT
substring([TicketTags],charindex('{"TN":"Guest","TT":0,"TV":"',[TicketTags])+len('{"TN":"Guest","TT":0,"TV":"'),1) as [GuestCount]
FROM [Tickets]
WHERE [Date] > '{Start}' and [Date] < '{End}'
EDIT: this is better because it should support Guest count up to 99:
SELECT
CASE ISNUMERIC(substring([TicketTags],charindex('{"TN":"Guest","TT":0,"TV":"',[TicketTags])+len('{"TN":"Guest","TT":0,"TV":"'),2))
WHEN 1 THEN convert(int,substring([TicketTags],charindex('{"TN":"Guest","TT":0,"TV":"',[TicketTags])+len('{"TN":"Guest","TT":0,"TV":"'),2))
ELSE convert(int,substring([TicketTags],charindex('{"TN":"Guest","TT":0,"TV":"',[TicketTags])+len('{"TN":"Guest","TT":0,"TV":"'),1))
END as [GuestCount]
FROM [Tickets]
WHERE [Date] > '{Start}' and [Date] < '{End}'thanks but exe giving me “Conversion failed when converting the nvarchar value ‘:’ to data type int.”. i think i need to adjust the length…which part do i adjust. i have tried to play around but failed
Hard to say without seeing your data. Here, this is a little more foolproof, but I can’t know for sure unless you show the [TicketTags] field for the record that fails, which will be found after the last Row returned. Let me know if this works:
SELECT
CASE isnull(charindex('{"TN":"Guest","TT":0,"TV":"',[TicketTags]),0)
WHEN 0 THEN 0
ELSE
(
CASE ISNUMERIC(substring([TicketTags],charindex('{"TN":"Guest","TT":0,"TV":"',[TicketTags])+len('{"TN":"Guest","TT":0,"TV":"'),2))
WHEN 1 THEN convert(int,substring([TicketTags],charindex('{"TN":"Guest","TT":0,"TV":"',[TicketTags])+len('{"TN":"Guest","TT":0,"TV":"'),2))
ELSE convert(int,substring([TicketTags],charindex('{"TN":"Guest","TT":0,"TV":"',[TicketTags])+len('{"TN":"Guest","TT":0,"TV":"'),1))
END
)
END as [GuestCount]
FROM [Tickets]i hv tried the query it didnt give error but showed 0 values only
this is the data in the field:
[{“TN”:“Guest”,“TT”:1,“TV”:“3”},{“TN”:“PMT 16.42.993 [Cash]”,“TT”:0,“TV”:“50.00”},{“TN”:“TND”,“TT”:0,“TV”:“50.00”},{“TN”:“CHG”,“TT”:0,“TV”:“3.10”},{“TN”:“PMT 17.55.901 [Cash]”,“TT”:0,“TV”:“50.00”}]
and tag:
You’re getting zeros because of the value after "TT":1 … the query is looking for "TT":0
So you could change that portion of the string throughout the query.
I have a feeling that the TT value is to indicate the Tagging Type, which you have set to Numeric. Did you change that recently? I ask because, in your original query, you were searching for "TT":0 and now your data is showing "TT":1
oh ya you are right in this case why is the sql giving me result!!
after changing the 0 to 1 the query is running. thank you for the help one more thing how do i sum the count?
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
didnt know can run the query also i am just a beginner in sql…thanks @QMcKay will execute the query and update
Hello,
Can you help me with this ? I need to make a total of the people in the restaurant. I have the Person Count tutorial implemented.
I have this, but does not make a total
Person Count: {REPORT TICKET DETAILS:TT.Person Count:(TT=Person Count)}
Thx
Did you try {REPORT TICKET DETAILS:TT.Person Count.sum:(TT=Person Count)}
empty
Ok that was version 5 only feature. I couldn’t remember for sure.
1 Like
i am using sql to get total for guest
under custom report enter:
Total People:|@@GuestTotal
then goto automation -> scripts ->add script
name: any name
handler: @@GuestTotal same as define in custom report()
SELECT SUM([PeopleCount]) as [PeopleCountTotal]
FROM
(
SELECT
CASE isnull(charindex('{"TN":"People","TT":1,"TV":"',[TicketTags]),0)
WHEN 0 THEN 0
ELSE
(
CASE ISNUMERIC(substring([TicketTags],charindex('{"TN":"People","TT":1,"TV":"',[TicketTags])+len('{"TN":"People","TT":1,"TV":"'),2))
WHEN 1 THEN convert(int,substring([TicketTags],charindex('{"TN":"People","TT":1,"TV":"',[TicketTags])+len('{"TN":"People","TT":1,"TV":"'),2))
ELSE convert(int,substring([TicketTags],charindex('{"TN":"People","TT":1,"TV":"',[TicketTags])+len('{"TN":"People","TT":1,"TV":"'),1))
END
)
END as [PeopleCount]
FROM [Tickets]
Where Date > '{Start}' and Date < '{End}'
) gcsum
note: '{“TN”:“People”,“TT”: -----> people is my ticket tag defined so you will need to replace people with your tag
4 Likes
It worked like you. Thanks a lot, your a great guy ! 