How to PIVOT various VAT breakdown options with Invoice Summary data

  • Countries like Canada have certain consumption taxes to be applied, when selling products, e.g. GST, HST, QST -  (if one is interested here is the link ).
    We have internal VAT table to accommodate the possible combinations of those taxes as well as so called Zero Rated tax - simply implying the product is exempt from taxes.
    The VAT table looks like that:

    WITH VAT AS (
    SELECT 'GST'    AS Label,
            'A'        AS VAT_Code,
            5.000    AS [Percentage]
    UNION ALL
    SELECT 'GST',    'B',    5.000
    UNION ALL
    SELECT 'PST'    ,'B',    7.000
    UNION ALL
    SELECT 'HST',    'K',    15.000
    UNION ALL
    SELECT 'HST',    'L',    15.000
    UNION ALL
    SELECT 'GST',    'M',    5.000
    UNION ALL
    SELECT 'PST',    'M',    8.000
    UNION ALL
    SELECT 'HST',    'N',    15.000
    UNION ALL
    SELECT 'HST',    'O',    13.000
    UNION ALL
    SELECT 'HST',    'P',    15.000
    UNION ALL
    SELECT 'GST',    'Q',    5.000
    UNION ALL
    SELECT 'QST',    'Q',    9.975
    UNION ALL
    SELECT 'Zero Rated',    'Z',    0.000
    )
    SELECT * FROM VAT

    Each sale can have three possible combinations of VAT Code:
    Case 1 Z (Zero Rated) tax only, e.g. international flight tickets
    Case 2 VAT Code other than Z
    Case 3 a combination of Case 1 and Case 2. There never will be two non-Z VAT Codes in a same sale, e.g. there never will be B and K VAT Codes in a same sale.

    Below is a partial summary of the sale from an invoice table:

    WITH Invoice AS (
    SELECT 655.75 AS fare_value,
         373.84 AS tax_value,
         'Q'   AS vat_code,
         'Some other columns will also be added, e.g. currency code' AS remarks
    UNION ALL
    SELECT 5379.75,1427.48, 'Z','BTW rate of 0%'
    )
    SELECT * FROM Invoice

    Looking into VAT table for code Q, I know there are two elements to VAT: 5% and 9.975%, so
    I need to show two additional columns (but in case of VAT Code K or L there will be only one column) :
    one for GST, which will be fare_value x 5 / 100 
    and one for QST - fare_value x 9.975 / 100
    But obviously for Zero Rated data those two columns shall remain empty or with a zero value

    The final result I need to achieve and to show at the bottom of an invoice for this example will be as per attached image.
    Secondary question is if I can have a control over the order of the VAT related columns: GST before QST or other way around?

    Much appreciated, as always!

  • First, let's put the sample data into tables ...
    IF OBJECT_ID(N'tempdb..#VAT', N'U') IS NOT NULL
    BEGIN
    DROP TABLE #VAT;
    END;
    GO
    IF OBJECT_ID(N'tempdb..#INVOICE', N'U') IS NOT NULL
    BEGIN
    DROP TABLE #INVOICE;
    END;
    GO

    WITH VAT(Label,VAT_Code,[Percentage]) AS (
    SELECT 'GST',  'A',  5.000
    UNION ALL
    SELECT 'GST',  'B',  5.000
    UNION ALL
    SELECT 'PST'  ,'B',  7.000
    UNION ALL
    SELECT 'HST',  'K',  15.000
    UNION ALL
    SELECT 'HST',  'L',  15.000
    UNION ALL
    SELECT 'GST',  'M',  5.000
    UNION ALL
    SELECT 'PST',  'M',  8.000
    UNION ALL
    SELECT 'HST',  'N',  15.000
    UNION ALL
    SELECT 'HST',  'O',  13.000
    UNION ALL
    SELECT 'HST',  'P',  15.000
    UNION ALL
    SELECT 'GST',  'Q',  5.000
    UNION ALL
    SELECT 'QST',  'Q',  9.975
    UNION ALL
    SELECT 'Zero Rated',  'Z',  0.000
    )
    SELECT *
    INTO #VAT
    FROM VAT;
    GO

    WITH Invoice AS (
    SELECT 655.75 AS fare_value,
       373.84 AS tax_value,
       'Q'  AS vat_code,
       'Some other columns will also be added, e.g. currency code' AS remarks
    UNION ALL
    SELECT 5379.75,1427.48, 'Z','BTW rate of 0%'
    )
    SELECT *
    INTO #INVOICE
    FROM Invoice;
    GO

    Now, let's use some dynamic SQL to build the output
    DECLARE
      @pvtCols nvarchar(MAX)
    , @calcCols nvarchar(MAX)
    , @Query  nvarchar(MAX);

    SET @pvtCols = STUFF( (SELECT N', ' + QUOTENAME(v.Label)
            FROM #VAT AS v
            --INNER JOIN #INVOICE AS i ON i.vat_code = v.VAT_Code
            GROUP BY v.Label
            ORDER BY v.Label
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
           , 1, 2, '');

    SET @calcCols = STUFF( (SELECT N', ' + QUOTENAME(v.Label) + N' = CAST(fare_value * ' + QUOTENAME(v.Label) + N' /100 AS decimal(18,2))'
            FROM #VAT AS v
            --INNER JOIN #INVOICE AS i ON i.vat_code = v.VAT_Code
            GROUP BY v.Label
            ORDER BY v.Label
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
           , 1, 2, '');

    SET @Query = N'
    SELECT fare_value, tax_value, vat_code, remarks, ' + @calcCols + '
    FROM (
      SELECT i.fare_value, i.tax_value, i.vat_code, i.remarks -- Static cols
        , v.Label               -- Pivot Cols
        , v.Percentage             -- Aggregate Cols
      FROM #INVOICE AS i
      INNER JOIN #VAT AS v
       ON i.vat_code = v.VAT_Code
      ) AS srcData
    PIVOT (
       MAX(Percentage)
       FOR Label in (' + @pvtCols + ')
      ) AS pvtOutput;
    ';

    --PRINT (@Query);
    EXEC sp_executesql @Query;

  • Thank you @desnorton,

    When I execute your solution it also includes columns HST and PST, which are not part of Q VAT Code (it has GST and QST only).

  • BOR15K - Thursday, October 11, 2018 4:54 AM

    Thank you @desnorton,

    When I execute your solution it also includes columns HST and PST, which are not part of Q VAT Code (it has GST and QST only).

    Then uncomment the JOIN in SET @pvtCols and SET @calcCols.  It will only provide the columns that are used.

  • DesNorton - Thursday, October 11, 2018 5:13 AM

    BOR15K - Thursday, October 11, 2018 4:54 AM

    Thank you @desnorton,

    When I execute your solution it also includes columns HST and PST, which are not part of Q VAT Code (it has GST and QST only).

    Then uncomment the JOIN in SET @pvtCols and SET @calcCols.  It will only provide the columns that are used.

    Sorry I wasn't clear I presume, I only need two columns to be shown: GST  and QST. your solution also shows me Zero Rated column, which I do not need. Instead I need either NULL or zero values for in GST / QST columns
    for any charge exempt from VAT.

  • BOR15K - Friday, October 12, 2018 2:30 AM

    DesNorton - Thursday, October 11, 2018 5:13 AM

    BOR15K - Thursday, October 11, 2018 4:54 AM

    Thank you @desnorton,

    When I execute your solution it also includes columns HST and PST, which are not part of Q VAT Code (it has GST and QST only).

    Then uncomment the JOIN in SET @pvtCols and SET @calcCols.  It will only provide the columns that are used.

    Sorry I wasn't clear I presume, I only need two columns to be shown: GST  and QST. your solution also shows me Zero Rated column, which I do not need. Instead I need either NULL or zero values for in GST / QST columns
    for any charge exempt from VAT.

    To exclude "Zero-Rated" then exclude it in the @pvtCols and @calcCols.
    The code is also modified to return 0 instead of NULL.
    DECLARE
    @pvtCols nvarchar(MAX)
    , @calcCols nvarchar(MAX)
    , @Query nvarchar(MAX);

    SET @pvtCols = STUFF( (SELECT N', ' + QUOTENAME(v.Label)
       FROM #VAT AS v
       INNER JOIN #INVOICE AS i ON i.vat_code = v.VAT_Code
       WHERE v.VAT_Code <> 'Z'
       GROUP BY v.Label
       ORDER BY v.Label
       FOR XML PATH(''), TYPE
       ).value('.', 'NVARCHAR(MAX)')
       , 1, 2, '');

    SET @calcCols = STUFF( (SELECT N', ' + QUOTENAME(v.Label) + N' = CAST(ISNULL([fare_value] * ' + QUOTENAME(v.Label) + N' /100, 0) AS decimal(18,2))'
       FROM #VAT AS v
       INNER JOIN #INVOICE AS i ON i.vat_code = v.VAT_Code
       WHERE v.VAT_Code <> 'Z'
       GROUP BY v.Label
       ORDER BY v.Label
       FOR XML PATH(''), TYPE
       ).value('.', 'NVARCHAR(MAX)')
       , 1, 2, '');

    SET @Query = N'
    SELECT fare_value, tax_value, vat_code, remarks, ' + @calcCols + '
    FROM (
    SELECT i.fare_value, i.tax_value, i.vat_code, i.remarks -- Static cols
      , v.Label     -- Pivot Cols
      , v.Percentage     -- Aggregate Cols
    FROM #INVOICE AS i
    INNER JOIN #VAT AS v
      ON i.vat_code = v.VAT_Code
    ) AS srcData
    PIVOT (
      MAX(Percentage)
      FOR Label in (' + @pvtCols + ')
    ) AS pvtOutput;
    ';

    --PRINT (@Query);
    EXEC sp_executesql @Query;

  • Thank you. This is the one.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply