@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