I have been trying to make a report to give me the last order no and the time of the selected department. I use {Department:Dept. Name} ticket tag to separate the depts and also {Order No:####} for my order numbers.
Result should be a single value : For example: 21:00 | 0001
I have been bursting my head and trying for hours but no luck. I would really appreciate if someone can give me a hand. I really dont have much SQL knowledge if not I am sure that would do it.
Why are you using ticket tags to separate departments instead of using departments? You would have to redefine all standard reports. Also why are you trying to get the last order in a report, I would think thats generally needed for some sort of recall not a report.
If you were able to use departments for this SQL would be the way to go. You can easily get the last order based on department and limit it to a single result. Such as:
SELECT Top 1 Orders.CreatedDateTime, Orders.OrderNumber
FROM Orders INNER JOIN
Departments ON Orders.DepartmentId = Departments.Id
WHERE Departments.Name = 'Cafe'
ORDER BY Orders.Id DESC
Then use {REPORT SQL DETAILS:<handler or sql>;<parameters,1,2>:<fields>:<expressions>:<format>} to define what you’re looking to get in your report.
Thanks for the further info on your config. Since you’re using ticket tags and you want the last order we need to join the tables and filter for the tag. Below should work, just change your ticket tag name to match whatever you’re comparing like “Departments”.
[Last Order Report:1,1]
{REPORT SQL DETAILS:SELECT TOP 1 Orders.Id
,CONVERT(varchar,Orders.CreatedDateTime,100) as DateTime
FROM Tickets
INNER JOIN Orders ON Tickets.Id = Orders.TicketId
CROSS APPLY OPENJSON(TicketTags)
WITH (
TagName varchar(50) '$.TN'
,TagValue varchar(50) '$.TV'
)
WHERE TagName = 'PickupID'
ORDER BY Tickets.Id DESC, Orders.Id DESC
:F.Id,F.DateTime::{0}|{1}}