DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<H2>Irvin Report '+ CONVERT(varchar, DATEADD(day,-1,CAST(GETDATE() as date)), 103)+'</H1>'+
    N'<font face="Calibri (Body)" size="11"><table border= "1">' +
    N'<tr><th>Date</th><th>Store No</th><th>Store Name</th> <th>Location</th>' +
    N'<th>Quantity</th><th>Revenue Exc VAT</th><th>VAT Amount</th><th>Discount Amount</th><th>Revenue</th></tr>' +
    CAST
    ( (         SELECT
                "td/@align" = 'right',td = FORMAT(A.Date,'dd/MM/yyyy'),' ',
                "td/@align" = 'right',td = A.[Store No_], ' ',
                "td/@align" = 'right',td = A.StoreName, ' ',
                "td/@align" = 'right',td = A.Location, ' ',
                "td/@align" = 'right',td = FORMAT(SUM(A.Quantity),'#,###,##0.###\'),' ',
                "td/@align" = 'right',td = FORMAT(SUM(A.RevenueExcludingVAT),'#,###,##0.###\'),' ',
                "td/@align" = 'right',td = FORMAT(SUM(A.[VAT Amount]),'#,###,##0.###\'),' ',
                "td/@align" = 'right',td = FORMAT(SUM(A.[Discount Amount]),'#,###,##0.###\'),' ',
                "td/@align" = 'right',td = FORMAT(SUM(A.Revenue),'#,###,##0.###\')
                FROM
                (
                    SELECT
                        --CONCAT('1601',L.[Store No_],L.[POS Terminal No_],L.[Transaction No_],L.[Line No_]) [Transaction ID],
                        L.Date,
                        H.[Store No_],
                        S.[Name] [StoreName],
                        SM.Location,
                        CASE
                            WHEN SM.[Store Group]='TRONG NUOC' AND H.[Store No_] ='ST048' THEN 'MALL'
                            WHEN SM.[Store Group]='TRONG NUOC' AND H.[Store No_] ='ST069' THEN 'MALL'
                            WHEN SM.[Store Group]='TRONG NUOC' THEN 'DOM'
                        ELSE 'INT'
                        END [Location Group],
                        L.[Receipt No_],
                        L.[Item No_],
                        L.[Item Category Code] ItemCategory,
                        IC.Description ItemCategoryDesc,
                        PG.Code [Product Group Code],
                        PG.Description [Product Group Name],
                        D.Code [Division Code],
                        D.Description [Division Name],
                        REPLACE(I.[Full Description],CHAR(10),CHAR(20)) [ItemName],
                        B.[Barcode No_] [Barcode],
                        B.[Variant Code] [Variant Code],
                        Convert(decimal(18,0),IIF(H.[Sale Is Return Sale] = 1,-1,1) * IIF(L.Quantity < 0,-1,1) * L.Quantity) Quantity,
                        L.[Staff ID] [Cashier ID],
                        I.[Vendor No_] AS [VendorNo],
                        V.Name AS [VendorName],
                        CASE
                        WHEN S.[Store Gen_ Bus_ Post_ Gr_]='NUOC NGOAI'
                            THEN IIF(H.[Sale Is Return Sale] = 1,-1,1) * IIF(L.[Net Amount] < 0,-1,1) * Convert(decimal(18,2),L.[Net Amount])* dbo.GetExchRate(H.Date,H.[Trans_ Currency],H.[Store No_])
                            ELSE
                        IIF(H.[Sale Is Return Sale] = 1,-1,1) * IIF(L.[Net Amount] < 0,-1,1) * Convert(decimal(18,2),L.[Net Amount])
                        END [RevenueExcludingVAT],
                        CASE
                        WHEN S.[Store Gen_ Bus_ Post_ Gr_]='NUOC NGOAI'
                            THEN IIF(H.[Sale Is Return Sale] = 1,-1,1) * IIF(L.[VAT Amount] < 0,-1,1) * L.[VAT Amount]* dbo.GetExchRate(H.Date,H.[Trans_ Currency],H.[Store No_])
                            ELSE IIF(H.[Sale Is Return Sale] = 1,-1,1) * IIF(L.[VAT Amount] < 0,-1,1) * L.[VAT Amount]
                        END [VAT Amount],
                        CASE
                        WHEN S.[Store Gen_ Bus_ Post_ Gr_]='NUOC NGOAI'
THEN IIF(H.[Sale Is Return Sale] = 1,-1,1) * IIF(L.[Discount Amount] < 0,-1,1) * Convert(decimal(18,2),L.[Discount Amount]) * dbo.GetExchRate(H.Date,H.[Trans_ Currency],H.[Store No_])
                            ELSE IIF(H.[Sale Is Return Sale] = 1,-1,1) * IIF(L.[Discount Amount] < 0,-1,1) * Convert(decimal(18,2),L.[Discount Amount])
                        END [Discount Amount],
                        CASE
                        WHEN S.[Store Gen_ Bus_ Post_ Gr_]='NUOC NGOAI'
                            THEN IIF(H.[Sale Is Return Sale] = 1,-1,1) * IIF(L.[Total Rounded Amt_] < 0,-1,1)* Convert(decimal(18,2),L.[Total Rounded Amt_]) * dbo.GetExchRate(H.Date,H.[Trans_ Currency],H.[Store No_])
                            ELSE IIF(H.[Sale Is Return Sale] = 1,-1,1) * IIF(L.[Total Rounded Amt_] < 0,-1,1)* Convert(decimal(18,2),L.[Total Rounded Amt_])
                        END [Revenue]
                        FROM [DAT$Trans_ Sales Entry] L (nolock)
                        INNER JOIN [DAT$Transaction Header] H (nolock) ON L.[Store No_] = H.[Store No_]
                            AND L.[POS Terminal No_] = H.[POS Terminal No_]
                            AND L.[Transaction No_] = H.[Transaction No_]
                        INNER JOIN [DAT$Barcodes] B (nolock) ON L.[Item No_]=B.[Item No_]
                        AND (B.[Barcode No_] LIKE '218%' OR B.[Barcode No_] LIKE '219%')
                        AND L.[Variant Code]=B.[Variant Code]
                        INNER JOIN [Store Mapping] SM (NOLOCK) ON L.[Store No_]=SM.[Store No]
                        LEFT JOIN [DAT$Customer] C ON L.[Customer No_] = C.No_
                        INNER JOIN [DAT$Item] I (nolock) ON L.[Item No_] = I.No_
                        INNER JOIN [DAT$Store] S ON L.[Store No_] = S.No_
                        LEFT JOIN [DAT$Division] D ON I.[Division Code] = D.Code
                        LEFT JOIN [DAT$Item Category] IC ON I.[Item Category Code] = IC.Code
                        LEFT JOIN [DAT$Product Group] PG ON I.[Product Group Code] = PG.Code
                        LEFT JOIN [DAT$Staff] SF on L.[Sales Staff] = SF.ID
                        LEFT JOIN [DAT$Vendor] V (Nolock) ON I.[Vendor No_] = V.No_
                        LEFT JOIN [DAT$POS VAT Code] T (Nolock) ON L.[VAT Code] = T.[VAT Code]
                        LEFT JOIN [DAT$Unit of Measure] U (Nolock) ON L.[Unit of Measure] = U.Code
                        WHERE L.Date=DATEADD(day,-1,CAST(GETDATE() as date))
                        AND H.[Transaction Type]=2
                        AND V.No_='107333'
                ) A
                LEFT JOIN [DAT$Staff] SF on A.[Cashier ID] = SF.ID
                --GROUP BY A.Date,A.[Store No_], A.StoreName, A.Location, A.[Location Group]
                GROUP BY GROUPING SETS ((A.Date,A.[Store No_], A.StoreName, A.Location, A.[Location Group]), ())
                ORDER BY A.Location, A.[Location Group],A.[Store No_]

        FOR XML PATH('tr'), TYPE
    )
    AS NVARCHAR(MAX) ) +
    N'</table></font>' ;