@emre i hope this gives a better understanding
i have achieved the result by using sql and ticket tag but the result are not matching. can you help to know where things went wrong.
Table Sale Listing Via SQL:
Query:
SELECT T.TicketNumber,
MIN(CONVERT(nvarchar,T.DATE,105)) AS DATE,
MIN(CONVERT(nvarchar,T.DATE,108)) AS TIME,
COALESCE(MIN(CASE WHEN TE.EntityTypeId = 2 THEN TE.EntityName ELSE NULL END),‘-’) AS >[TABLE Name],
min(substring(cast(TicketTags as nvarchar(4000)),
CharIndex(‘“Sharing ‘,cast(TicketTags as nvarchar(4000)))+8,
CharIndex(’”’,cast(TicketTags as nvarchar(4000)),
CharIndex('"Sharing ‘,cast(TicketTags as nvarchar(4000)))+8
)- CharIndex(’"Sharing ',cast(TicketTags as nvarchar(4000)))-8)),
MIN(T.TotalAmount) AS TotalAmount
FROM Tickets AS T
LEFT OUTER JOIN TicketEntities AS TE ON TE.Ticket_Id = T.Id
WHERE DATE > ‘{Start}’ AND DATE < ‘{End}’ And TE.EntityName like ‘@1’
GROUP BY T.TicketNumber
ORDER BY DATE,TIME
Query2
SELECT
T.TicketNumber,
MIN(CONVERT(nvarchar,T.DATE,105)) AS DATE,
MIN(CONVERT(nvarchar,T.DATE,108)) AS TIME,
COALESCE(MIN(CASE WHEN TE.EntityTypeId = 2 THEN TE.EntityName ELSE NULL END),‘-’) AS [TABLE Name],
min(substring(cast(TicketTags as nvarchar(4000)),
CharIndex(‘“Sharing ‘,cast(TicketTags as nvarchar(4000)))+8,
CharIndex(’”’,cast(TicketTags as nvarchar(4000)),
CharIndex('"Sharing ‘,cast(TicketTags as nvarchar(4000)))+8
)- CharIndex(’"Sharing ',cast(TicketTags as nvarchar(4000)))-8)),
MIN(T.TotalAmount) AS TotalAmount
FROM Tickets AS T
LEFT OUTER JOIN TicketEntities AS TE ON TE.Ticket_Id = T.Id
WHERE DATE > ‘{Start}’ AND DATE < ‘{End}’ And (TE.EntityName between ‘1’ and ‘20’ or TE.EntityName like ‘Room’)
GROUP BY T.TicketNumber
ORDER BY DATE,TIME
Total:
SELECT ‘Totals’,‘’,‘’,‘’,‘’,‘’,
SUM(T.TotalAmount) AS TotalAmount
FROM Tickets AS T
LEFT OUTER JOIN TicketEntities AS TE ON TE.Ticket_Id = T.Id
WHERE T.DATE > ‘{Start}’ AND T.DATE < ‘{End}’ And TE.EntityName like ‘@1’
SELECT
‘Totals’,‘’,‘’,‘’,‘’,‘’,
SUM(T.TotalAmount) AS TotalAmount
FROM Tickets AS T
LEFT OUTER JOIN TicketEntities AS TE ON TE.Ticket_Id = T.Id
WHERE DATE > ‘{Start}’ AND DATE < ‘{End}’ And (TE.EntityName between ‘1’ and ‘20’ or TE.EntityName like ‘Room’)
Using Report Tag:
[Table Listing:2,2,2,2,2,2, 2]
Ticket No|Date|Time|Table|Sharing|Individual|Amount
1st Floor
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=1)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=2)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=3)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=4)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=5)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=6)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=7)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=8)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=9)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=10)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=11)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TEN.Tables=12)}
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TENC.Tables=Room)}
@@TableSaleByNoTotal
2nd Floor
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TENC.Tables=A)}
@@TableSaleTotal:A%
BB
{REPORT TICKET DETAILS:T.TicketNumber,T.Date,T.Time,EN.Tables,TT.Sharing,TT.Individual,T.TotalAmount:(TENC.Tables=B)}
@@TableSaleTotal:B%
Grant Total||||||{REPORT TICKET TOTAL}
i am calling sql function to get the subtotal