You should probably let the SQL know which table the Date field is coming from.
And you will want the MAX(Tickets.Date) which is an aggregate, so you need to do a GROUP BY on the other fields for it work.
Why not? Copy the SQL and paste it in forum. Then add 3 bacticks before and after the code, like this.
```
a bunch
of sql code
or whatever
```
Anyway, something like this - not tested, give me a moment …
EDIT: not working - pulling same date for all rows… this complicates things.
EDIT2: working now!
DECLARE @entityType varchar(50) = @1
DECLARE @startDateIn varchar(25) = @2
DECLARE @endDateIn varchar(25) = @3
DECLARE @startDate datetime = convert(varchar(25),@startDateIn,126)
DECLARE @endDate datetime = convert(varchar(25),@endDateIn,126)
SELECT ent.[Name] as [EntityName], ent.[CustomData], convert (varchar (10), MAX(tkt.[Date]), 103)
FROM [Entities] ent -- give your tables a short alias like ent or tkt
JOIN [TicketEntities] ted on ted.[EntityId]=ent.[Id]
JOIN [Tickets] tkt on tkt.[Id] = ted.[Ticket_Id]
WHERE ent.[Id] IN (
SELECT DISTINCT e.[EntityId]
FROM [TicketEntities] e
JOIN [EntityTypes] et on et.[Id]=e.[EntityTypeId]
WHERE 1=1
AND et.[Name]=@entityType
AND e.[EntityId] NOT IN (
SELECT DISTINCT te.[EntityId]
FROM [TicketEntities] te
JOIN [Tickets] t on t.[Id]=te.[Ticket_Id]
WHERE 1=1
AND t.[Date]>=@startDate
AND t.[Date]<=@endDate
)
)
GROUP BY ent.[Name], ent.[CustomData] -- we do a MAX() way up there, so we need to GROUP BY other fields that we are selecting
ORDER BY ent.[Name]
DECLARE @entityType varchar(50) = @1
DECLARE @startDateIn varchar(25) = @2
DECLARE @endDateIn varchar(25) = @3
DECLARE @startDate datetime = convert(varchar(25),@startDateIn,126)
DECLARE @endDate datetime = convert(varchar(25),@endDateIn,126)
SELECT ent.[Name] as [EntityName], ent.[CustomData], convert (varchar (10), MAX(t.[Date]), 103) as Fecha
FROM [Entities] ent, [Tickets] t
WHERE ent.[Id] IN (
SELECT DISTINCT e.[EntityId]
FROM [TicketEntities] e
JOIN [EntityTypes] et on et.[Id]=e.[EntityTypeId]
WHERE 1=1
AND et.[Name]=@entityType
AND e.[EntityId] NOT IN (
SELECT DISTINCT te.[EntityId]
FROM [TicketEntities] te
JOIN [Tickets] t on t.[Id]=te.[Ticket_Id]
WHERE 1=1
AND t.[Date]>=@startDate
AND t.[Date]<=@endDate
)
)
GROUP BY ent.[Name], ent.[CustomData]
ORDER BY ent.[Name]
I forgot the group by. Thats the problem! Thanks for helping me!
Edit: After seeing your Edit. Im not sure cause i have only one date… i will add some more to test that.cause in my query the results is only for 2017-04-06 but that is correct.
Revised, working … this shows the last date the Customer made a Purchase was assigned to a Ticket.
DECLARE @entityType varchar(50) = @1
DECLARE @startDateIn varchar(25) = @2
DECLARE @endDateIn varchar(25) = @3
DECLARE @startDate datetime = convert(varchar(25),@startDateIn,126)
DECLARE @endDate datetime = convert(varchar(25),@endDateIn,126)
SELECT ent.[Name] as [EntityName], ent.[CustomData], convert (varchar (10), MAX(tkt.[Date]), 103)
FROM [Entities] ent -- give your tables a short alias like ent or tkt
JOIN [TicketEntities] ted on ted.[EntityId]=ent.[Id]
JOIN [Tickets] tkt on tkt.[Id] = ted.[Ticket_Id]
WHERE ent.[Id] IN (
SELECT DISTINCT e.[EntityId]
FROM [TicketEntities] e
JOIN [EntityTypes] et on et.[Id]=e.[EntityTypeId]
WHERE 1=1
AND et.[Name]=@entityType
AND e.[EntityId] NOT IN (
SELECT DISTINCT te.[EntityId]
FROM [TicketEntities] te
JOIN [Tickets] t on t.[Id]=te.[Ticket_Id]
WHERE 1=1
AND t.[Date]>=@startDate
AND t.[Date]<=@endDate
)
)
GROUP BY ent.[Name], ent.[CustomData] -- we do a MAX() way up there, so we need to GROUP BY other fields that we are selecting
ORDER BY ent.[Name]
That is entirely possible. I use only ISO 8601 exclusively (yyyy-MM-dd) so I don’t have any issues with Start/End, but you are using non-standard date format (dd/MM/yyyy). Date formatting is a picky thing which is why I stick to the standard. My Regional Settings are configured that way as well.
… or maybe make them DATE only (no TIME) and limit it to varchar(10):
DECLARE @startDate date = convert(varchar(10),@startDateIn,103)
DECLARE @endDate date = convert(varchar(10),@endDateIn,103)
Just play with it until it works for you.
P.S. in this case, use the quoted parameters for '{Start}' and '{End}' because we don’t quote them in the SQL (ie. @1, @2, @3 are not quoted in the SQL, so we feed in quoted parameters).
I tried all what you are saying, but with the same result.
I also try in sql directly, to see if something happens.
DECLARE @entityType varchar(50) = 'Customer'
DECLARE @startDateIn varchar(25) = '12/04/2017'
DECLARE @endDateIn varchar(25) = '12/04/2017'
DECLARE @startDate date = convert(varchar(10),@startDateIn,103)
DECLARE @endDate date = convert(varchar(10),@endDateIn,103)
SELECT ent.[Name] as [EntityName], ent.[CustomData], convert (varchar (10), MAX(tkt.[Date]), 103) as Fecha
FROM [Entities] ent -- give your tables a short alias like ent or tkt
JOIN [TicketEntities] ted on ted.[EntityId]=ent.[Id]
JOIN [Tickets] tkt on tkt.[Id] = ted.[Ticket_Id]
WHERE ent.[Id] IN (
SELECT DISTINCT e.[EntityId]
FROM [TicketEntities] e
JOIN [EntityTypes] et on et.[Id]=e.[EntityTypeId]
WHERE 1=1
AND et.[EntityName]=@entityType
AND e.[EntityId] NOT IN (
SELECT DISTINCT te.[EntityId]
FROM [TicketEntities] te
JOIN [Tickets] t on t.[Id]=te.[Ticket_Id]
WHERE 1=1
AND t.[Date]>=@startDate
AND t.[Date]<=@endDate
)
)
GROUP BY ent.[Name], ent.[CustomData] -- we do a MAX() way up there, so we need to GROUP BY other fields that we are selecting
ORDER BY Fecha, ent.[Name]
I use different formats of dates, like 12/04/2017, 20170412, 2017-04-12. In the 3 cases, i get the correct results.
Maybe is something in Samba and how it works with dates.
Is there a way to see, how the samba is sending the date to the report?
The only case i get an error in the SQL is when i dont use any separator in the string. (and in the format of my country that is ddMMyyyy)
In the other hand, in Samba, i cant see any report.
I think Samba send the numbers without the separators, and thats why im not able tu use any report that include this tags. (i tested other reports)
so maybe there is a problem with the {Start} {End} tags?
I have the last Build that i installed yesterday.
EDIT: Run more test… im almost pretty sure that the problem are the TAGS.
I use different formats of dates manually instead of the tags in the reports, and works great… but when i use the tag, its not working.
I am confused as well. There has never been any issues with the Start/End tags. I don’t understand what it is doing… it does not make much sense.
On one hand it appears to work fine (in the more complex query), but with the simple queries I suggested, it does not work. It either displays nothing, or it displays the actual Tags.
There seems to be a conversion problem going on here.
i did the same test as you did, and i have the same result.
I dont know what else can i do. i will keep testing to see if i can find something to make it work.
Thanks!
Try the embedded SQL version, like this - keep in mind the Preview might not work, because it has no Start/End to work with - you probably need to use the Reports screen to see results:
[Customer NOT Purchased LD embedded:1,1,0.3,1, 2]
>>Name|Phone|VIP|Business|LastDate
{REPORT SQL DETAILS:
SELECT ent.[Name] as [EntityName], ent.[CustomData], convert (varchar (10), MAX(tkt.[Date]), 103) as [LastDate]
FROM [Entities] ent -- give your tables a short alias like ent or tkt
JOIN [TicketEntities] ted on ted.[EntityId]=ent.[Id]
JOIN [Tickets] tkt on tkt.[Id] = ted.[Ticket_Id]
WHERE ent.[Id] IN (
SELECT DISTINCT e.[EntityId]
FROM [TicketEntities] e
JOIN [EntityTypes] et on et.[Id]=e.[EntityTypeId]
WHERE 1=1
AND et.[Name]='Customers'
AND e.[EntityId] NOT IN (
SELECT DISTINCT te.[EntityId]
FROM [TicketEntities] te
JOIN [Tickets] t on t.[Id]=te.[Ticket_Id]
WHERE 1=1
AND t.[Date]>='{Start}'
AND t.[Date]<='{End}'
)
)
GROUP BY ent.[Name], ent.[CustomData] -- we do a MAX() way up there, so we need to GROUP BY other fields that we are selecting
ORDER BY ent.[Name]
:F.EntityName
,F.CustomData(Name=Phone)Value
,F.CustomData(Name=VIP Level)Value
,F.CustomData(Name=Business)Value
,F.LastDate
}
{Start} {End} {UserId} and {UserName} tags will get processed when used inside the SQL Script. That probably does not work because you’re using these tags as SQL Handler Parameters. I don’t remember I implemented something to process handler parameters.
