How to PIVOT various VAT breakdown options with Invoice Summary data

• BOR15K

  SSCertifiable

  Points: 5894

  More actions

  October 11, 2018 at 3:15 am

  #379141

  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!

• DesNorton

  SSC-Insane

  Points: 24386

  More actions

  October 11, 2018 at 4:16 am

  #2009015

  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;


  ---

  How to post data/code on a forum to get the best help.

• BOR15K

  SSCertifiable

  Points: 5894

  More actions

  October 11, 2018 at 4:54 am

  #2009027

  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).

• DesNorton

  SSC-Insane

  Points: 24386

  More actions

  October 11, 2018 at 5:13 am

  #2009034

  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.

  ---

  How to post data/code on a forum to get the best help.

• BOR15K

  SSCertifiable

  Points: 5894

  More actions

  October 12, 2018 at 2:30 am

  #2009296

  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.

• DesNorton

  SSC-Insane

  Points: 24386

  More actions

  October 12, 2018 at 2:39 am

  #2009297

  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;

  ---

  How to post data/code on a forum to get the best help.

• BOR15K

  SSCertifiable

  Points: 5894

  More actions

  October 12, 2018 at 3:04 am

  #2009300

  Thank you. This is the one.