STUFF Function

• mhildebrand

  SSC-Addicted

  Points: 499

  More actions

  December 12, 2014 at 11:58 am

  #1764557

  One voucher paying several vendors and invoices is quite normal. The problem is that you have 2 tables, one defines a relationship between voucher, vendor and invoice and the other table has a relationship between voucher, batch (no idea what this is for) and amount.

  How do you know which amount is payed to which invoice? Knowing that will also give you the answer to which amount is payed to each vendor. Unfortunately, with the tables as you posted them, there's no way to know this.

  Does this makes sense to you? Am I making myself clear on the problem?

  Very clear, I come from accounting not a full IT background, so the money side makes sense; looking back at the table, I included the batch to only hold the data to my live / test data batch of 18 rows. For any other joining or need here, it is moot. Second, my client does not really care to see in this report how much was paid to each invoice, they have a journal report for that, this is a sum report which will just show the vendor, total paid and all invoices paid in that term. Make sense?

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  December 12, 2014 at 12:31 pm

  #1764560

  mhildebrand (12/12/2014)

  ---

  One voucher paying several vendors and invoices is quite normal. The problem is that you have 2 tables, one defines a relationship between voucher, vendor and invoice and the other table has a relationship between voucher, batch (no idea what this is for) and amount.

  How do you know which amount is payed to which invoice? Knowing that will also give you the answer to which amount is payed to each vendor. Unfortunately, with the tables as you posted them, there's no way to know this.

  Does this makes sense to you? Am I making myself clear on the problem?

  Very clear, I come from accounting not a full IT background, so the money side makes sense; looking back at the table, I included the batch to only hold the data to my live / test data batch of 18 rows. For any other joining or need here, it is moot. Second, my client does not really care to see in this report how much was paid to each invoice, they have a journal report for that, this is a sum report which will just show the vendor, total paid and all invoices paid in that term. Make sense?

  I understand the part on not showing how much was paid for each invoice. The thing is that you need that relationship to avoid duplicating the information.

  If you change a vendor in a voucher to have several vendors for the same voucher, you'll duplicate the total of the voucher for each vendor. I'm posting an extract to prove my point.

  CREATE TABLE Vouchergldist( Vglvoucher int NOT NULL,

  Vgljebatch int NOT NULL,

  Vglamount money NOT NULL );

  CREATE TABLE Voucher( Vchvendor int NOT NULL,

  Vchvouchernbr int NOT NULL,

  Vchinvoicenbr varchar( 20 )NOT NULL );

  INSERT INTO Dbo.Vouchergldist( Vglvoucher,

  Vgljebatch,

  Vglamount )

  VALUES ( 8, 21174, 24.25 ),

  ( 8, 21174, 4.25 ),

  ( 8, 21174, 13.75 );

  ---

  INSERT INTO Dbo.Voucher( Vchvendor,

  Vchvouchernbr,

  Vchinvoicenbr )

  VALUES

  ( 1986, 8, 'IN 491 1400000 1666' ),

  ( 1986, 8, 'IN 491 1400000 1653' ),

  ( 1987, 8, 'IN 491 14000001639' );

  WITH Vv( Vchvendor,

  Vglvoucher )

  AS ( SELECT DISTINCT Vchvendor,

  Vglvoucher

  FROM Vouchergldist Vgl

  JOIN Voucher V

  ON V.Vchvouchernbr = Vgl.Vglvoucher

  WHERE Vgl.Vgljebatch = 21174 )

  SELECT Vv.Vchvendor,

  SUM( Vgl.Vglamount ) AS Vglamount,

  STUFF((

  SELECT ', ' + I.Vchinvoicenbr

  FROM Voucher I

  WHERE I.Vchvendor = Vv.Vchvendor

  FOR XML PATH( '' )), 1, 2, '' ) AS Invoices

  FROM Vouchergldist Vgl

  JOIN Vv ON Vv.Vglvoucher = Vgl.Vglvoucher

  WHERE Vgl.Vgljebatch = 21174

  GROUP BY Vv.Vchvendor;

  GO

  DROP TABLE Vouchergldist;

  DROP TABLE Voucher;

  Resulting in

  

  If you knew which amount corresponds to which invoice, you could just add that condition to the join criteria and prevent that duplication.

  Something like this:

  CREATE TABLE Vouchergldist( Vglvoucher int NOT NULL,

  Vgljebatch int NOT NULL,

  Vglamount money NOT NULL,

  Vchinvoicenbr varchar( 20 )NOT NULL );

  CREATE TABLE Voucher( Vchvendor int NOT NULL,

  Vchvouchernbr int NOT NULL,

  Vchinvoicenbr varchar( 20 )NOT NULL );

  INSERT INTO Dbo.Vouchergldist( Vglvoucher,

  Vgljebatch,

  Vglamount,

  Vchinvoicenbr )

  VALUES ( 8, 21174, 24.25, 'IN 491 1400000 1666' ),

  ( 8, 21174, 4.25, 'IN 491 1400000 1653' ),

  ( 8, 21174, 13.75, 'IN 491 14000001639' );

  ---

  INSERT INTO Dbo.Voucher( Vchvendor,

  Vchvouchernbr,

  Vchinvoicenbr )

  VALUES

  ( 1986, 8, 'IN 491 1400000 1666' ),

  ( 1986, 8, 'IN 491 1400000 1653' ),

  ( 1987, 8, 'IN 491 14000001639' );

  WITH Vv( Vchvendor,

  Vglvoucher )

  AS ( SELECT DISTINCT Vchvendor,

  Vglvoucher

  FROM Vouchergldist Vgl

  JOIN Voucher V

  ON V.Vchvouchernbr = Vgl.Vglvoucher

  WHERE Vgl.Vgljebatch = 21174 )

  SELECT Vv.Vchvendor,

  SUM( Vgl.Vglamount ) AS Vglamount,

  STUFF((

  SELECT ', ' + I.Vchinvoicenbr

  FROM Voucher I

  WHERE I.Vchvendor = Vv.Vchvendor

  FOR XML PATH( '' )), 1, 2, '' ) AS Invoices

  FROM Vouchergldist Vgl

  JOIN Voucher Vv ON Vv.Vchvouchernbr = Vgl.Vglvoucher

  AND Vv.Vchinvoicenbr = Vgl.Vchinvoicenbr

  WHERE Vgl.Vgljebatch = 21174

  GROUP BY Vv.Vchvendor;

  GO

  DROP TABLE Vouchergldist;

  DROP TABLE Voucher;

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  December 15, 2014 at 3:11 am

  #1764671

  WITH vv (VCHVendor,VGLVoucher)

  AS (SELECT DISTINCT VCHVendor,VGLVoucher

  FROM VoucherGLDist vgl

  JOIN Voucher v ON v.VCHVoucherNbr = vgl.VGLVoucher

  WHERE vgl.VGLJEBatch = 21174)

  SELECT vv.VCHVendor, SUM(vgl.VGLAmount) AS [VGLAmount],

  STUFF((SELECT ','+ b.VCHInvoiceNbr

  FROM VoucherGLDist a

  JOIN Voucher b ON b.VCHVoucherNbr = a.VGLVoucher

  AND b.VCHVendor = vv.VCHVendor

  WHERE a.VGLJEBatch = 21174

  GROUP BY b.VCHVendor,b.VCHInvoiceNbr

  FOR XML PATH ('')),1,1,'') AS [Invoices]

  FROM VoucherGLDist vgl

  JOIN vv ON vv.VGLVoucher = vgl.VGLVoucher

  WHERE vgl.VGLJEBatch = 21174

  GROUP BY vv.VCHVendor

  Far away is close at hand in the images of elsewhere.
  Anon.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 15, 2014 at 3:58 am

  #1764680

  David Burrows (12/15/2014)

  ---

  WITH vv (VCHVendor,VGLVoucher)

  AS (SELECT DISTINCT VCHVendor,VGLVoucher

  FROM VoucherGLDist vgl

  JOIN Voucher v ON v.VCHVoucherNbr = vgl.VGLVoucher

  WHERE vgl.VGLJEBatch = 21174)

  SELECT vv.VCHVendor, SUM(vgl.VGLAmount) AS [VGLAmount],

  STUFF((SELECT ','+ b.VCHInvoiceNbr

  FROM VoucherGLDist a

  JOIN Voucher b ON b.VCHVoucherNbr = a.VGLVoucher

  AND b.VCHVendor = vv.VCHVendor

  WHERE a.VGLJEBatch = 21174

  GROUP BY b.VCHVendor,b.VCHInvoiceNbr

  FOR XML PATH ('')),1,1,'') AS [Invoices]

  FROM VoucherGLDist vgl

  JOIN vv ON vv.VGLVoucher = vgl.VGLVoucher

  WHERE vgl.VGLJEBatch = 21174

  GROUP BY vv.VCHVendor

  Curious, what if you did the following (using David Burrows code):

  WITH vv (VCHVendor,VGLVoucher)

  AS (SELECT DISTINCT VCHVendor,VGLVoucher

  FROM VoucherGLDist vgl

  JOIN Voucher v ON v.VCHVoucherNbr = vgl.VGLVoucher

  --WHERE vgl.VGLJEBatch = 21174

  )

  SELECT vv.VCHVendor, SUM(vgl.VGLAmount) AS [VGLAmount],

  STUFF((SELECT ','+ b.VCHInvoiceNbr

  FROM VoucherGLDist a

  JOIN Voucher b ON b.VCHVoucherNbr = a.VGLVoucher

  AND b.VCHVendor = vv.VCHVendor

  --WHERE a.VGLJEBatch = 21174

  GROUP BY a.VGLJEBatch,b.VCHVendor,b.VCHInvoiceNbr

  FOR XML PATH ('')),1,1,'') AS [Invoices]

  FROM VoucherGLDist vgl

  JOIN vv ON vv.VGLVoucher = vgl.VGLVoucher

  GROUP BY vgl.VGLJEBatch, vv.VCHVendor;