December 12, 2014 at 8:53 am
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.
I appreciate it Lynn, but like I mentioned the tables are already created with the DDL the way I explained, so why would I need to do a CREATE statement? I am sincere in this question, still fresh. If the tables already have the data from the legacy host, I SHOULD be able to run your solution without the CREATE / INSERT statements, correct? When I do, I ge the 24 returns I show above, not the 18 I'd expect. Thought on this?
December 12, 2014 at 10:01 am
mhildebrand (12/12/2014)
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.
I appreciate it Lynn, but like I mentioned the tables are already created with the DDL the way I explained, so why would I need to do a CREATE statement? I am sincere in this question, still fresh. If the tables already have the data from the legacy host, I SHOULD be able to run your solution without the CREATE / INSERT statements, correct? When I do, I ge the 24 returns I show above, not the 18 I'd expect. Thought on this?
So that WE can setup a test environment in a sandbox database to work on your problem. Don't want to give us DDL and sample data in a readily usable format, don't. Just don't expect to get the help you could if you did. No DDL, sample data, expected results; no tested code working code in return.
December 12, 2014 at 10:04 am
What was expected is
Create tables
CREATE TABLE dbo.VoucherGLDist (
VGLVoucher INT Not Null,
VGLJEBatch INT Not Null,
VGLAmount Money Not Null)
GO
CREATE TABLE dbo.Voucher (
VCHVendor INT Not Null,
VCHVoucherNbr INT Not Null,
VCHInvoiceNbr Varchar (20) Not Null)
Populate tables
INSERT dbo.VoucherGLDist (VGLVoucher,VGLJEBatch,VGLAmount)
VALUES(1, 21174, 1168.5),
(2, 21174, 1257.88),
(3, 21174, 109.9),
(4, 21174, 88),
(5, 21174, 370.8),
(6, 21174, 200),
(7, 21174, 156.97),
(8, 21174, 24.25),
(8, 21174, 4.25),
(8, 21174, 13.75),
(9, 21174, 25),
(10, 21174, 601.37),
(11, 21174, 12.94),
(12, 21174, 22.5),
(13, 21174, 1430.63),
(14, 21174, 723.64),
(15, 21174, 169.62),
(16, 21174, 75),
(16, 21174, 200),
(17, 21174, 809),
(18, 21174, 48.63),
(18, 21174, 3368.69),
(18, 21174, 25),
(18, 21174, 150.58)
INSERT dbo.Voucher (VCHVendor,VCHVoucherNbr,VCHInvoiceNbr)
VALUES(2400, 1, 'JD0302'),
(2360, 2, '32868'),
(2251, 3, 'Exp. Reimb. 030614'),
(2184, 4, 'Exp. Reimb. 030514'),
(2137, 5, '47009147'),
(2114, 6, 'Avon Walk Donation -'),
(2031, 7, '13085'),
(1986, 8, 'IN 491 1400000 1666'),
(1986, 8, 'IN 491 1400000 1653'),
(1986, 8, 'IN 491 14000001639'),
(1831, 9, 'Paralegal Annual Rep'),
(1664, 10, 'Exp. Reimb. 03062014'),
(1561, 11, '30715'),
(1560, 12, '24835'),
(1395, 13, 'Exp. Reimb. 030614'),
(1349, 14, 'Acct. 0771'),
(1225, 15, '363735'),
(738, 16, '14s032ww'),
(738, 16, '14s025ww'),
(48, 17, '1-284-39861'),
(37, 18, 'Exp. Reimb. 030614'),
(37, 18, 'Exp. Reimb. 022814'),
(37, 18, 'Exp. Reimb. 022614')
Expected Results
VCHVendor Amount Invoices
2400 1168.5 JD0302
2360 1257.88 32868
2251 109.9 Exp. Reimb. 030614
2184 88 Exp. Reimb. 030514
2137 370.8 47009147
2114 200 Avon Walk Donation -
2031 156.97 13085
1986 42.25 IN 491 1400000 1666, IN 491 1400000 1653, IN 491 14000001639
1831 25 Paralegal Annual Rep
1664 601.37 Exp. Reimb. 03062014
1561 12.94 30715
1560 22.5 24835
1395 1430.63 Exp. Reimb. 030614
1349 723.64 Acct. 0771
1225 169.62 363735
738 275 14s032ww, 14s025ww
48 809 1-284-39861
37 3592.9 Exp. Reimb. 030614, Exp. Reimb. 022814, Exp. Reimb. 022614, Exp. Reimb. 030614
Which would have resulted in a solution like this
WITH vv (VCHVendor,VGLVoucher)
AS (SELECT DISTINCT VCHVendor,VGLVoucher
FROM VoucherGLDist vgl
JOIN Voucher v ON v.VCHVoucherNbr = vgl.VGLVoucher
WHERE vgl.VGLJEBatch = 21174)
SELECTvv.VCHVendor, SUM(vgl.VGLAmount) AS [VGLAmount],
STUFF((SELECT ','+ i.VCHInvoiceNbr
FROM Voucher i
WHERE i.VCHVendor = vv.VCHVendor
FOR XML PATH ('')),1,1,'') AS [Invoices]
FROMVoucherGLDist vgl
JOIN vv ON vv.VGLVoucher = vgl.VGLVoucher
WHEREvgl.VGLJEBatch = 21174
GROUP BY vv.VCHVendor
Far away is close at hand in the images of elsewhere.
Anon.
December 12, 2014 at 10:23 am
Slow your role Lynn, I thought I was giving you what you asked for, OK? Remember, I am new here, I have less experience than you obviously, I am not trying to make your day difficult or not get help on this solution. Are we agreed on that?
So that WE can setup a test environment in a sandbox database to work on your problem. Don't want to give us DDL and sample data in a readily usable format, don't. Just don't expect to get the help you could if you did. No DDL, sample data, expected results; no tested code working code in return.
This is not the kind of response a new person on this forum finds helpful, you are basically giving me the finger when I thought I was giving you what you needed. I asked a simple question, in innocence and newness and you jumped on me as if I was raping your sister. Not cool. What am I not giving you, based on my following assumptions:
1) I thought the DDL was the INT, VARCHAR for each field, i guess I am wrong; please and kindly direct me to what you need
2) My confusion, where I am jut asking for an reason kindly, you partially answered ws to build a sandbox. OK, so in the DEV environment in my world outside of the forum where I spend most of my time, do I need to create new tables with the 40,000 + lines of data that already exist from the data conversion
December 12, 2014 at 10:32 am
David,
That looks closer than what I had gotten so far, Lynn has been very helpful - but I think I am misunderstanding what Lynn is asking for. The only issue I am seeing now is this. As I mention, that items that Igave you are from a very specific batch in tables that already existed outside of this forum. There are way more than the 18 rows (40,000+) in the entire tables, but only 18 in this batch (21174), that is why I used it. However, when I run your solution it appears I am getting invoices outside of this batch number, I would paste my results but the invoice field is populated with hundreds of comma separated values that won't fit here. Suffice it to say it looks kind of like this:
Vendor Amount Invoice
37 3592.90 83510, 1-10-2011, 1-17/21/2011 (and so on.....for about 300 invoices)
December 12, 2014 at 10:34 am
mhildebrand (12/12/2014)
Slow your role Lynn, I thought I was giving you what you asked for, OK? Remember, I am new here, I have less experience than you obviously, I am not trying to make your day difficult or not get help on this solution. Are we agreed on that?So that WE can setup a test environment in a sandbox database to work on your problem. Don't want to give us DDL and sample data in a readily usable format, don't. Just don't expect to get the help you could if you did. No DDL, sample data, expected results; no tested code working code in return.
This is not the kind of response a new person on this forum finds helpful, you are basically giving me the finger when I thought I was giving you what you needed. I asked a simple question, in innocence and newness and you jumped on me as if I was raping your sister. Not cool. What am I not giving you, based on my following assumptions:
1) I thought the DDL was the INT, VARCHAR for each field, i guess I am wrong; please and kindly direct me to what you need
2) My confusion, where I am jut asking for an reason kindly, you partially answered ws to build a sandbox. OK, so in the DEV environment in my world outside of the forum where I spend most of my time, do I need to create new tables with the 40,000 + lines of data that already exist from the data conversion
I showed you exactly what you should post in my second response where I posted DDL for the tables, INSERT INTO statements for the sample data based on the data you had posted in an unusable format. I even told you that in that post.
David Burrows has again shown you this above.
I could also point you to the first article I reference below in my signature block but since you are new I thought I'd show in that second post instead.
December 12, 2014 at 10:36 am
mhildebrand (12/12/2014)
This is not the kind of response a new person on this forum finds helpful, you are basically giving me the finger when I thought I was giving you what you needed. I asked a simple question, in innocence and newness and you jumped on me as if I was raping your sister. Not cool. What am I not giving you, based on my following assumptions:1) I thought the DDL was the INT, VARCHAR for each field, i guess I am wrong; please and kindly direct me to what you need
2) My confusion, where I am jut asking for an reason kindly, you partially answered ws to build a sandbox. OK, so in the DEV environment in my world outside of the forum where I spend most of my time, do I need to create new tables with the 40,000 + lines of data that already exist from the data conversion
You're right, that's not the kind of response expected for a new member of these forums. However, that was the third time that he asked for it and he gave an example on how you were expected to post what is needed. You've been given with correct answers but you say they don't work.
The problem is that without DDL, sample data, expected results based on that sample data (everything in a way that we can just copy/paste it and execute it) and exact errors that you encounter, we're just blind folded and can only give guesses.
Could you please post what is needed to help you correctly?
December 12, 2014 at 10:49 am
First of all, sample data should not be production data. It needs to be data that is representative of the problem domain. If there are multiple batches in the data then your sample data should reflect that so that we can properly work the problem.
Based on the sample data you have posted it is difficult to know what may be happening when you use the code provided which works with one batch.
December 12, 2014 at 11:06 am
OK, scolding received, apologies to Lynn for not understanding well enough to give you what you need - I still do not think I do, but Luis stepped in so I will try it again. I ran the three pieces code verbatim that David provided below and still got hundreds of invoices that were not part of the batch 21174 test group. I believe this post will provide all the items I had lacked before. I created the tables as temp, as these tables actually exist in my database and I do not want them touched.
CREATE TABLE #VoucherGLDist (
VGLVoucher INT Not Null,
VGLJEBatch INT Not Null,
VGLAmount Money Not Null)
GO
CREATE TABLE #Voucher (
VCHVendor INT Not Null,
VCHVoucherNbr INT Not Null,
VCHInvoiceNbr Varchar (20) Not Null)
INSERT dbo.VoucherGLDist (VGLVoucher,VGLJEBatch,VGLAmount)
VALUES (1, 21174, 1168.5),
(2, 21174, 1257.88),
(3, 21174, 109.9),
(4, 21174, 88),
(5, 21174, 370.8),
(6, 21174, 200),
(7, 21174, 156.97),
(8, 21174, 24.25),
(8, 21174, 4.25),
(8, 21174, 13.75),
(9, 21174, 25),
(10, 21174, 601.37),
(11, 21174, 12.94),
(12, 21174, 22.5),
(13, 21174, 1430.63),
(14, 21174, 723.64),
(15, 21174, 169.62),
(16, 21174, 75),
(16, 21174, 200),
(17, 21174, 809),
(18, 21174, 48.63),
(18, 21174, 3368.69),
(18, 21174, 25),
(18, 21174, 150.58)
---
INSERT dbo.Voucher (VCHVendor,VCHVoucherNbr,VCHInvoiceNbr)
VALUES (2400, 1, 'JD0302'),
(2360, 2, '32868'),
(2251, 3, 'Exp. Reimb. 030614'),
(2184, 4, 'Exp. Reimb. 030514'),
(2137, 5, '47009147'),
(2114, 6, 'Avon Walk Donation -'),
(2031, 7, '13085'),
(1986, 8, 'IN 491 1400000 1666'),
(1986, 8, 'IN 491 1400000 1653'),
(1986, 8, 'IN 491 14000001639'),
(1831, 9, 'Paralegal Annual Rep'),
(1664, 10, 'Exp. Reimb. 03062014'),
(1561, 11, '30715'),
(1560, 12, '24835'),
(1395, 13, 'Exp. Reimb. 030614'),
(1349, 14, 'Acct. 0771'),
(1225, 15, '363735'),
(738, 16, '14s032ww'),
(738, 16, '14s025ww'),
(48, 17, '1-284-39861'),
(37, 18, 'Exp. Reimb. 030614'),
(37, 18, 'Exp. Reimb. 022814'),
(37, 18, 'Exp. Reimb. 022614')
---
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,1,'') AS [Invoices]
FROM VoucherGLDist vgl
JOIN vv ON vv.VGLVoucher = vgl.VGLVoucher
WHERE vgl.VGLJEBatch = 21174
GROUP BY vv.VCHVendor
December 12, 2014 at 11:18 am
mhildebrand (12/12/2014)
OK, scolding received, apologies to Lynn for not understanding well enough to give you what you need - I still do not think I do, but Luis stepped in so I will try it again. I ran the three pieces code verbatim that David provided below and still got hundreds of invoices that were not part of the batch 21174 test group. I believe this post will provide all the items I had lacked before. I created the tables as temp, as these tables actually exist in my database and I do not want them touched.CREATE TABLE #VoucherGLDist (
VGLVoucher INT Not Null,
VGLJEBatch INT Not Null,
VGLAmount Money Not Null)
GO
CREATE TABLE #Voucher (
VCHVendor INT Not Null,
VCHVoucherNbr INT Not Null,
VCHInvoiceNbr Varchar (20) Not Null)
INSERT dbo.VoucherGLDist (VGLVoucher,VGLJEBatch,VGLAmount)
VALUES (1, 21174, 1168.5),
(2, 21174, 1257.88),
(3, 21174, 109.9),
(4, 21174, 88),
(5, 21174, 370.8),
(6, 21174, 200),
(7, 21174, 156.97),
(8, 21174, 24.25),
(8, 21174, 4.25),
(8, 21174, 13.75),
(9, 21174, 25),
(10, 21174, 601.37),
(11, 21174, 12.94),
(12, 21174, 22.5),
(13, 21174, 1430.63),
(14, 21174, 723.64),
(15, 21174, 169.62),
(16, 21174, 75),
(16, 21174, 200),
(17, 21174, 809),
(18, 21174, 48.63),
(18, 21174, 3368.69),
(18, 21174, 25),
(18, 21174, 150.58)
---
INSERT dbo.Voucher (VCHVendor,VCHVoucherNbr,VCHInvoiceNbr)
VALUES (2400, 1, 'JD0302'),
(2360, 2, '32868'),
(2251, 3, 'Exp. Reimb. 030614'),
(2184, 4, 'Exp. Reimb. 030514'),
(2137, 5, '47009147'),
(2114, 6, 'Avon Walk Donation -'),
(2031, 7, '13085'),
(1986, 8, 'IN 491 1400000 1666'),
(1986, 8, 'IN 491 1400000 1653'),
(1986, 8, 'IN 491 14000001639'),
(1831, 9, 'Paralegal Annual Rep'),
(1664, 10, 'Exp. Reimb. 03062014'),
(1561, 11, '30715'),
(1560, 12, '24835'),
(1395, 13, 'Exp. Reimb. 030614'),
(1349, 14, 'Acct. 0771'),
(1225, 15, '363735'),
(738, 16, '14s032ww'),
(738, 16, '14s025ww'),
(48, 17, '1-284-39861'),
(37, 18, 'Exp. Reimb. 030614'),
(37, 18, 'Exp. Reimb. 022814'),
(37, 18, 'Exp. Reimb. 022614')
---
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,1,'') AS [Invoices]
FROM VoucherGLDist vgl
JOIN vv ON vv.VGLVoucher = vgl.VGLVoucher
WHERE vgl.VGLJEBatch = 21174
GROUP BY vv.VCHVendor
Nope, all the data is once again for a single batch. The code you have been given so far works for a single batch. We need sample data for multiple batches along with the expected results based on that new sample data.
December 12, 2014 at 11:23 am
OK, missed that sir, I will do what I can to get it asap. I REALLY DO appreciate your help, may not sound like it, but I do. 🙂
December 12, 2014 at 11:30 am
Help me out here. You posted several rows with the same voucher number on both tables. That would create a cartesian product and give incorrect results. Is this correct? You might need to change your tables to generate the correct information or post other columns present in your actual tables and not present on the ones you posted.
December 12, 2014 at 11:38 am
Luis, in our environment we may have 1 voucher that pays several vendors and several invoices among those vendors. The reason this all started is I wanted to concatenate the rows by vendor and sum the amount, listing the invoices we paid. The voucher number I list was only to create the join, in my original query, and perhaps that was wrong idea? I found the stuff/ for xml path and thought I was on the right path, if there is a different method, I am open. Maybe the vendor number instead of voucher?
December 12, 2014 at 11:42 am
Until you provide us with the DDL for the tables, sample data to fill those tables, and expected results we can't really help you.
Remember, what you post needs to represent your problem domain. With that we can help you solve your problem.
December 12, 2014 at 11:49 am
mhildebrand (12/12/2014)
Luis, in our environment we may have 1 voucher that pays several vendors and several invoices among those vendors. The reason this all started is I wanted to concatenate the rows by vendor and sum the amount, listing the invoices we paid. The voucher number I list was only to create the join, in my original query, and perhaps that was wrong idea? I found the stuff/ for xml path and thought I was on the right path, if there is a different method, I am open. Maybe the vendor number instead of voucher?
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?
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply