December 1, 2014 at 3:44 pm
Use to think Iw as getting pretty good at this stuff then I linger around the forums. I have not found an answer to this so let me see if Io can post it. Here is my issue;
I have a table of 18 rows of unique vendors and total sums I owe them this period. We receive notice via invoice. however, we may be paying on two or three invoices in a period, so where I have 18 rows of vendors, I may have 21 rows of said invoiced for those vendors. I have read up on the Stuff / For XML and all I have been able to get so far is a list of all my vendors, with the total sum but instead of getting a comma delimited list of their own invoiced if they have more than one, each row comma delimits ALL the invoices and reports them so it looks something like this?
Johns Hams $152.00 1234,12345,123456,123,12,1
Kevins Bacon $49.99 1234,12345,123456,123,12,1
Cracklings $50.01 1234,12345,123456,123,12,1
...
When it should look like this:
Johns Hams $152.00 1234
Kevins Bacon $49.99 123,12,1
Cracklings $50.01 123456
I have three source tables, the main table that resembles the following:
(Eventually, I have to link THIS Table to a whole new non-legacy table, so I ask for a couple of extra fields)
GLVoucher
Amount
BatchNumber
VGLVoucher
Voucher
InvoiceNumber
Vendor
I can get to the Vendor name and Amount just by using a simple Sum and group by, I have even tried to put this into a temp table (called #summary) and joining, but the Stuff / For XML to concatenate the rows looked the best, but again, I am breaking it.
Any ideas?
December 1, 2014 at 4:58 pm
You're missing a WHERE clause in your correlated subquery to differentiate each vendor.
December 1, 2014 at 5:23 pm
I actually did not put any of the clauses in there. Here is what the series ACTUALLY looks like (I obviously left some of the table declarations out of my earlier, for eample VGLJEBatch is the BatchNumber I refered to:
SELECT
SUM(vgl.vglamount) as VGLamount, v.Vchvendor, vgl.VGLJEBatch into #summaryview
FROM
..VoucherGLDist vgl
left join
..Voucher v on v.VchVoucherNbr = vgl.VGLVoucher
where
VGLJEBatch = '21174'
group by v.VchVendor,vgl.VGLJEBatch
order by SUM(vgl.vglamount)
Then from this temp table #Summary, I tried this having never used STUFF or the for XML:
select vchvendor,
STUFF((select ', '+ v.vchinvoicenbr
from ..VoucherGLDist vgl
left join ..Voucher v on vgl.VGLVoucher = v.VchVoucherNbr
where v.VchVoucherNbr = vgl.VGLVoucher
for XML path ('')),1,1,'')Invoices
from #summaryview
December 5, 2014 at 9:59 am
Anyone? Anyone?
December 5, 2014 at 10:31 am
SELECTv.Vchvendor, SUM(vgl.vglamount) AS [VGLamount],
STUFF((SELECT ','+ b.vchinvoicenbr
FROMVoucherGLDist a
JOIN Voucher b ON b.VchVoucherNbr = a.VGLVoucher
WHERE b.VchVendor = v.Vchvendor
FOR XML PATH ('')),1,1,'') AS [Invoices]
FROMVoucherGLDist vgl
JOIN Voucher v ON v.VchVoucherNbr = vgl.VGLVoucher
WHEREvgl.VGLJEBatch = '21174'
GROUP BY v.VchVendor,vgl.VGLJEBatch
ORDER BY SUM(vgl.vglamount)
Far away is close at hand in the images of elsewhere.
Anon.
December 5, 2014 at 10:43 am
David Burrows (12/5/2014)
SELECTv.Vchvendor, SUM(vgl.vglamount) AS [VGLamount],
STUFF((SELECT ','+ b.vchinvoicenbr
FROMVoucherGLDist a
JOIN Voucher b ON b.VchVoucherNbr = a.VGLVoucher
WHERE b.VchVendor = v.Vchvendor
FOR XML PATH ('')),1,1,'') AS [Invoices]
FROMVoucherGLDist vgl
JOIN Voucher v ON v.VchVoucherNbr = vgl.VGLVoucher
WHEREvgl.VGLJEBatch = '21174'
GROUP BY v.VchVendor,vgl.VGLJEBatch
ORDER BY SUM(vgl.vglamount)
This looks good, threw it into SSMS and it is erring out, first it errs out on the order by, then line by line it fails at each association. (V.*, VGL.*), any thoughts?
December 5, 2014 at 10:57 am
mhildebrand (12/5/2014)
David Burrows (12/5/2014)
SELECTv.Vchvendor, SUM(vgl.vglamount) AS [VGLamount],
STUFF((SELECT ','+ b.vchinvoicenbr
FROMVoucherGLDist a
JOIN Voucher b ON b.VchVoucherNbr = a.VGLVoucher
WHERE b.VchVendor = v.Vchvendor
FOR XML PATH ('')),1,1,'') AS [Invoices]
FROMVoucherGLDist vgl
JOIN Voucher v ON v.VchVoucherNbr = vgl.VGLVoucher
WHEREvgl.VGLJEBatch = '21174'
GROUP BY v.VchVendor,vgl.VGLJEBatch
ORDER BY SUM(vgl.vglamount)
This looks good, threw it into SSMS and it is erring out, first it errs out on the order by, then line by line it fails at each association. (V.*, VGL.*), any thoughts?
Try using the correct database. The syntax is correct if vchinvoicenbr is a string column. If it's a numeric type column you need to use CAST or CONVERT before concatenating it.
December 5, 2014 at 11:16 am
mhildebrand (12/5/2014)
David Burrows (12/5/2014)
SELECTv.Vchvendor, SUM(vgl.vglamount) AS [VGLamount],
STUFF((SELECT ','+ b.vchinvoicenbr
FROMVoucherGLDist a
JOIN Voucher b ON b.VchVoucherNbr = a.VGLVoucher
WHERE b.VchVendor = v.Vchvendor
FOR XML PATH ('')),1,1,'') AS [Invoices]
FROMVoucherGLDist vgl
JOIN Voucher v ON v.VchVoucherNbr = vgl.VGLVoucher
WHEREvgl.VGLJEBatch = '21174'
GROUP BY v.VchVendor,vgl.VGLJEBatch
ORDER BY SUM(vgl.vglamount)
This looks good, threw it into SSMS and it is erring out, first it errs out on the order by, then line by line it fails at each association. (V.*, VGL.*), any thoughts?
Would help if you provided the complete error message(s) you are getting. Just saying it is erring out doesn't tell us anything.
December 11, 2014 at 10:54 am
First, let me thank all for attempting to help. I am still new to the forums so I have asked the question incorrectly. Let me try to ask it again in a way that has been most successfully used by other, bear with me 🙂
I believe the STUFF / FOR XML PATH was the correct solution, but I am not getting it. Here are my tables of data and I am looking for the data in table 3 (grouped by vendor, sum of all amounts, listing the inoices being paid):
Table VoucherGLDist
VGLVoucher VGLJEBatch VGLAmount
1 2117 $25.00
2 2117 $125.00
2 2117 $50.00
3 2117 $45.00
Table Voucher
VCHVendor VCHVoucherNbr VCHInvoiceNbr
Fed Ex 1 100w
Video Company 2 105-9
Marksman 3 s200
Video Company 2 106-5
Desired Result Table3
Vendor Amount Invoice
Fed Ex $25.00 100w
Video Company $175.00 105-9, 106-5
Marksman $45.00 s200
December 11, 2014 at 12:33 pm
A little better, but please take a close look at how I have posted my solution to your problem. You will see that I have created the tables, inserted the sample data using INSERT INTO statements, and when done I dropped the tables to cleanup my Sandbox database.
The solution is presented after the INSERTs and before the DROPs.
You should post the DDL (CREATE TABLE) statements, sample data (INSERT statements), and cleanup DDL to cleanup when everything when done.
create table dbo.VoucherGLDist (
VGLVoucher int,
VGLJEBatch int,
VGLAmount decimal(12,2)
);
create table dbo.Voucher (
VCHVendor varchar(64),
VCHVoucherNbr int,
VCHInvoiceNbr varchar(5)
);
go
insert into dbo.VoucherGLDist
values
(1,2117,25.00),
(2,2117,125.00),
(2,2117,50.00),
(3,2117,45.00);
insert into dbo.Voucher
values
('Fed Ex',1,'100w'),
('Video Company',2,'105-9'),
('Marksman',3,'s200'),
('Video Company',2,'106-5');
go
with BaseVoucherDist as (
select
vgld.VGLVoucher,
vgld.VGLJEBatch,
sum(vgld.VGLAmount) Amount
from
dbo.VoucherGLDist vgld
group by
vgld.VGLVoucher,
vgld.VGLJEBatch
),
BaseVouchers as (
select distinct
v.VCHVendor,
v.VCHVoucherNbr,
stuff((select ',' + v1.VCHInvoiceNbr
from dbo.Voucher v1
where v1.VCHVoucherNbr = v.VCHVoucherNbr
order by v1.VCHInvoiceNbr
for xml path(''),TYPE).value('.','varchar(max)'),1,1,'') Invoices
from
dbo.Voucher v
)
select
bv.VCHVendor,
bvd.Amount,
bv.Invoices
from
BaseVoucherDist bvd
inner join BaseVouchers bv
on (bvd.VGLVoucher = bv.VCHVoucherNbr);
go
drop table dbo.VoucherGLDist;
drop table dbo.Voucher;
go
December 11, 2014 at 1:20 pm
Lynn that is very close to what I am looking for, but here is what it gave me (when applied to the tables as they already exist (the vendor is actually a number here, for example):
VCHVendorAmountInvoices
24001168.5JD0302
23601257.8832868
2251109.9Exp. Reimb. 030614
218488Exp. Reimb. 030514
2137370.847009147
2114200Avon Walk Donation -
2031156.9713085
198624.25IN 491 1400000 1666
19864.25IN 491 1400000 1653
198613.75IN 491 14000001639
183125Paralegal Annual Rep
1664601.37Exp. Reimb. 03062014
156112.9430715
156022.524835
13951430.63Exp. Reimb. 030614
1349723.64Acct. 0771
1225169.62363735
7387514s032ww
73820014s025ww
488091-284-39861
3748.63Exp. Reimb. 030614
373368.69Exp. Reimb. 022814
3725Exp. Reimb. 022614
37150.58Exp. Reimb. 030614
There are 24 line items, when I am hoping for 18, I'd like to see like vendors concatenated into one row with comma separated list of invoices in the third column, so it would look like this (as desired):
VCHVendorAmountInvoices
24001168.5JD0302
23601257.8832868
2251109.9Exp. Reimb. 030614
218488Exp. Reimb. 030514
2137370.847009147
2114200Avon Walk Donation -
2031156.9713085
198642.25IN 491 1400000 1666, IN 491 1400000 1653, IN 491 14000001639
183125Paralegal Annual Rep
1664601.37Exp. Reimb. 03062014
156112.9430715
156022.524835
13951430.63Exp. Reimb. 030614
1349723.64Acct. 0771
1225169.62363735
73827514s032ww, 14s025ww
488091-284-39861
373592.9Exp. Reimb. 030614, Exp. Reimb. 022814, Exp. Reimb. 022614, Exp. Reimb. 030614
December 12, 2014 at 12:56 am
Look again at what I posted. We need you to post the DDL for the tables and to post your sample data as insert statements.
December 12, 2014 at 2:10 am
mhildebrand (12/11/2014)
Lynn that is very close to what I am looking for, but here is what it gave me (when applied to the tables as they already exist (the vendor is actually a number here, for example):VCHVendorAmountInvoices
24001168.5JD0302
23601257.8832868
2251109.9Exp. Reimb. 030614
218488Exp. Reimb. 030514
2137370.847009147
2114200Avon Walk Donation -
2031156.9713085
198624.25IN 491 1400000 1666
19864.25IN 491 1400000 1653
198613.75IN 491 14000001639
183125Paralegal Annual Rep
1664601.37Exp. Reimb. 03062014
156112.9430715
156022.524835
13951430.63Exp. Reimb. 030614
1349723.64Acct. 0771
1225169.62363735
7387514s032ww
73820014s025ww
488091-284-39861
3748.63Exp. Reimb. 030614
373368.69Exp. Reimb. 022814
3725Exp. Reimb. 022614
37150.58Exp. Reimb. 030614
There are 24 line items, when I am hoping for 18, I'd like to see like vendors concatenated into one row with comma separated list of invoices in the third column, so it would look like this (as desired):
VCHVendorAmountInvoices
24001168.5JD0302
23601257.8832868
2251109.9Exp. Reimb. 030614
218488Exp. Reimb. 030514
2137370.847009147
2114200Avon Walk Donation -
2031156.9713085
198642.25IN 491 1400000 1666, IN 491 1400000 1653, IN 491 14000001639
183125Paralegal Annual Rep
1664601.37Exp. Reimb. 03062014
156112.9430715
156022.524835
13951430.63Exp. Reimb. 030614
1349723.64Acct. 0771
1225169.62363735
73827514s032ww, 14s025ww
488091-284-39861
373592.9Exp. Reimb. 030614, Exp. Reimb. 022814, Exp. Reimb. 022614, Exp. Reimb. 030614
Based on your latest table defs and the data above my solution returns correct 18 rows 🙂
Note throughout your posts your data does not match the tables you specified!
Far away is close at hand in the images of elsewhere.
Anon.
December 12, 2014 at 8:33 am
Lynn Pettis (12/12/2014)
Look again at what I posted. We need you to post the DDL for the tables and to post your sample data as insert statements.
Lynn and David, really appreciate your help, let me see if this helps. The two source tables are already created (see below for the table DDL as req) with 40,000+ records, so I gave an example for my question. It seemed more practical. I believe I could skip a CREATE and INSERT table, as the data already exists, or am I missing something there?
TableVoucherGLDist
VGLVoucherINT, PK, Not Null
VGLJEBatch INT, Not Null
VGLAmount Money, Not Null
TableVoucher
VCHVendor INT, FK, Not Null
VCHVoucherNbr INT, PK, Not Null
VCHInvoiceNbr Varchar (20), Not Null
December 12, 2014 at 8:43 am
mhildebrand (12/12/2014)
Lynn Pettis (12/12/2014)
Look again at what I posted. We need you to post the DDL for the tables and to post your sample data as insert statements.Lynn and David, really appreciate your help, let me see if this helps. The two source tables are already created (see below for the table DDL as req) with 40,000+ records, so I gave an example for my question. It seemed more practical. I believe I could skip a CREATE and INSERT table, as the data already exists, or am I missing something there?
TableVoucherGLDist
VGLVoucherINT, PK, Not Null
VGLJEBatch INT, Not Null
VGLAmount Money, Not Null
TableVoucher
VCHVendor INT, FK, Not Null
VCHVoucherNbr INT, PK, Not Null
VCHInvoiceNbr Varchar (20), Not Null
Providing us with the DDL (CREATE TABLE statements) and the sample data (INSERT INTO statements) is for us to be able to assist you in working your problem and providing you with working and tested code.
I worked up some DDL and created the INSERT statements once, but it obviously wasn't fully correct. Not going to spend the time doing it again since I showed you what we needed.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply