Consolidating data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Consolidating data

Author

Topic

anderskd
Starting Member

25 Posts

Posted - 2006-10-19 : 15:27:09

Hello,
I've been racking my brain to find a good solution for this one...

I am working on a project that consolidates amounts paid to vendors from multiple systems. I have a table with multiple entries for each vendor (or FEDID number).
Basically I need this data consolidated up by FEDID. And I need to pull the attributes (names, addresses, states, ect) fields for one of the vendors.
So I can run a simple query:
SELECT sum(amount), FEDID FROM #VEND_SPEND GROUP BY FEDID

I can also add a max value to pull a name:
SELECT sum(amount), FEDID, MAX(VENDNAME) FROM #VEND_SPEND GROUP BY FEDID

--This works fine, but the trouble comes when I need to pull address information for that specific name that was pulled.
SELECT sum(amount), FEDID, MAX(VENDNAME), MAX(VENDADDR), MAX(CITY), MAX(STATE) FROM #VEND_SPEND GROUP BY FEDID

--You can see that it pulled the address data from different vendors with the same fedid (city from one Pheonix, state from another NY - which is an incorrect address).

So here's the question:
How can I sum the amount data. And pull the attributes (NAME, ADDRESS, CITY, STATE) for ONE corresponding record without getting the values mixed up. (ie, if it pulled the information for FEDEX Inc; I just want to make sure the addresses, names and state all come from that specific entry).

I hope this makes sense, I even tried pulling the fedid and max name in one pass, then joining back the data based on those two fields concatenated. I still get random invalid entries due to many entries having the same fedid and name, but different addresses (the dataset is around 20,000 with 12,000 unique fedids)

Here is the test data for the examples above:
CREATE TABLE #VEND_SPEND
(FEDID CHAR(10),
ACCTSYS CHAR(3),
AMOUNT DECIMAL(18, 2),
VENDNAME CHAR(15),
VENDADDR CHAR(15),
CITY CHAR(15),
STATE CHAR(2))

INSERT INTO #VEND_SPEND VALUES ('101', 'A',12.00, 'FEDEX', '123 TEST ST', 'CHICAGO', 'IL')
INSERT INTO #VEND_SPEND VALUES ('102', 'A',400.00, 'UPS', '1017 1ST', 'LOS ANGELES', 'CA')
INSERT INTO #VEND_SPEND VALUES ('101', 'B',30.00, 'FEDEX INC', '840 FIRST', 'PHOENIX', 'AZ')
INSERT INTO #VEND_SPEND VALUES ('101', 'C',500.00, 'FEDEX', '81ST AVE', 'NEW YORK', 'NY')
INSERT INTO #VEND_SPEND VALUES ('101', 'D',80.00, 'FED EX', '123 TEST ST', 'CHI', 'IL')

CPA and computer dork...how nerdy is that?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-19 : 15:57:24

I'm afraid SQL Server is doing exactly the right thing here - your data simply doesn't make sense.

Think about it, your data is basically like this - if you knew three people and they all had the exact same telephone number (equivalent of your non-unique FEDID) then which one would you get when you called that number? You can think about that all day long but there are no rules that coudl be applied to get the right person when you call the number. Well just like everyone you want to call has to have a unique phone number, all your vendors will have to have a unique ID, otherwise there is no way to get the correct data for a given vendor (because there is no such thing as a given vendor, without a unique vendor ID).

In database terms its called a primary key and until your table has one, you cannot query it the way you want.

anderskd
Starting Member

25 Posts

Posted - 2006-10-19 : 17:02:33

snSQL, I understand the primary key issue, it is actually set on FEDID and ACCTSYS fields. I just made a temp table with a small aomount of test data for simplicity. I also understand that for one FEDID there should be only one NAME, ADDRESS, ECT.
But I need to consolidate this data by fedid and grab one set of attributes; they just have to come from the same record.

So for FEDEX, I need to grab one name, one address, one city, and one state. I'm indifferent as to which set I grab, the issue is that they can't come from different records. So fedex doesn't have a city of Phoenix and State of NY. Which would be very invalid.

Let me know if I can supply additional information....

CPA and computer dork...how nerdy is that?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-19 : 18:02:54

Well shame on me for even answering a post that says of data "Which would be very invalid", as if there are degrees of validity!!!

But, here is how you can do what you want


SELECT sum(amount) AS Total, FEDID
	, (SELECT TOP 1 VENDNAME FROM #VEND_SPEND 
		WHERE FEDID = V.FEDID ORDER BY FEDID, ACCTSYS) VENDNAME
	, (SELECT TOP 1 VENDADDR FROM #VEND_SPEND 
		WHERE FEDID = V.FEDID ORDER BY FEDID, ACCTSYS) VENDADDR
	, (SELECT TOP 1 CITY FROM #VEND_SPEND 
		WHERE FEDID = V.FEDID ORDER BY FEDID, ACCTSYS) CITY
	, (SELECT TOP 1 STATE FROM #VEND_SPEND 
		WHERE FEDID = V.FEDID ORDER BY FEDID, ACCTSYS) STATE
FROM #VEND_SPEND V GROUP BY FEDID

anderskd
Starting Member

25 Posts

Posted - 2006-10-19 : 18:20:37

snSQL,
That is what I was looking for.

I did have another solution, where I concatinate all of the attribute fields; with keys that separate the fields (like ~NAME:FEDEX~CITY:Phoenix~STATE:AZ~...). Take the FEDID and max(consolidated attributes). Then use substring to unseparate the attributes and put them back in their own field.

You're way is much easier. Thanks again.

CPA and computer dork...how nerdy is that?

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-10-19 : 18:47:35

Try out this as well:

SELECT O.FEDID, O.VENDNAME, O.VENDADDR, O.CITY, O.STATE,
	 (SELECT SUM(AMOUNT) FROM #VEND_SPEND I WHERE I.FEDID = O.FEDID) TotalAmount
FROM #VEND_SPEND O
WHERE ACCTSYS = (SELECT MIN(ACCTSYS) FROM #VEND_SPEND I WHERE I.FEDID = O.FEDID)

-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter."

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-19 : 18:56:01

In fact don't just try it - do it the way PSamsig suggests, it should be a lot faster that way.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources