November 11, 2015 at 2:58 pm
I have a query that outputs something like so:
VID | PID | AID | AMT | VNO
123 | XYZ | 000 | 100.00 | 2
123 | XYZ | 000 | 103.00 | 324
123 | XYZ | 011 | 102.00 | 324
123 | XYZ | 012 | 325.00 | 324
123 | XYZ | 011 | 416.00 | 324
123 | XYZ | 013 | 155.00 | 324
There are many different PID's (Project ID's). Each PID can have multiple VID's(Vendor ID's) and each VID can have multiple VNO's(Voucher Numbers). If possible, I would like to sum the the AMT(Cost Amount) when the when the VNO is the same for a distinct VID. Then, I need to grand total each PID, which is the total of all VNO's under all VID's for a distinct project.
Whew:w00t:...Your help is greatly appreciated.
November 12, 2015 at 9:03 am
Grasshopper,
If you post your expected result and the create table and enough insert statements to replicate your setup, it's a lot easier to help. See Jeff Moden's article[/url] Forum Etiquette: How to post data/code on a forum to get the best help
It explains how to post etc. It should be required reading for anyone new to this site. =)
November 12, 2015 at 9:12 am
Apologies Ninja Warrior. I'll work on getting it in a better format for the pros.
November 12, 2015 at 9:35 am
CREATE TABLE MyData (
VID INT,
PID CHAR(3),
AID INT,
AMT SMALLMONEY,
VNO INT
);
GO
INSERT INTO MyData(VID,PID,AID,AMT,VNO) VALUES
(123,'XYZ',000,100.00,2),
(123,'XYZ',000,103.00,324),
(123,'XYZ',011,102.00,324),
(123,'XYZ',012,325.00,324),
(123,'XYZ',011,416.00,324),
(123,'XYZ',013,155.00,324);
Here's the create table and insert statements... now, what is the output report supposed to look like? That part isn't clear. Could you give an example of the totals etc? Thanks!
November 12, 2015 at 10:02 am
The total for VID '123' with a VNO '2' should equal 100.00
The total for VID '123' with VNO '324' should equal 1101.00 (103.00 + 102.00 + 325.00 + 416.00 + 155.00)
The total for PID 'XYZ' should equal the sum of those total above 1201.00 (100.00 + 1101.00)
Hope this makes more sense.
November 13, 2015 at 9:21 pm
Something like this? (You'd have to import the report into your project. It's using a connection to my dummy database, but you'd have to change it to point to your database.
The only confusing part is when you have to add the totals row... the rest was pretty easy.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply