Me First!!!
SQL to get Customers who have not made a purchase within the date range:
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]
FROM [Entities] ent
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
)
)
ORDER BY ent.[Name]
We can use that with {REPORT SQL DETAILS:X}
either raw as above, or save it as a Script and supply the @@handle
…
##RAW
[Customer NOT Purchased:1,1,1,1]
>>Name|Phone|VIP|Business
{REPORT SQL DETAILS:
SELECT ent.[Name] as [EntityName]
FROM [Entities] ent
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]>='2017-04-01'
AND t.[Date]<='2017-04-30'
)
)
ORDER BY ent.[Name]
:F.EntityName
}
##SQL Script with @@handler
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]
FROM [Entities] ent
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
)
)
ORDER BY ent.[Name]
[Customer NOT Purchased:1]
{REPORT SQL DETAILS:@@CustomerNotPurchasedList:'Customers','2017-04-01','2017-04-30':F.EntityName}
So now we need to convert the output of the above Report into a comma-separated list so we can feed it into {REPORT ENTITY DETAILS:X}
. We do that by prefixing it with @
and defining the separator as ,
(comma) …
[Customer NOT Purchased:1,1,1,1]
@{REPORT SQL DETAILS:@@CustomerNotPurchasedList:'Customers','2017-04-01','2017-04-30':F.EntityName:,}
>>Name|Phone|VIP|Business
{REPORT ENTITY DETAILS:E.Name,EC.Phone,EC.VIP Level,EC.Business:(EN=$1)}