Selecting Correct data from query.

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 2005 Forums

Transact-SQL (2005)

Selecting Correct data from query.

Author

Topic

rtpnc
Starting Member

19 Posts

Posted - 2007-09-18 : 11:00:32

We are scratching our heads trying to come up with a solution. We thought we would ask the experts.

We have records that can have 20 possible extra fields that contain
how the records total owed is broken down.

Here is the data:

97 OAK SPRING DR	97278	1052008	0	00000000	OPEN	MAIL RETURNED	00000097278	104	MAYER FIRE DISTRICT	N	ROTER COUNTY	617000	00000077038	00000000000	77038	MAYER FIRE DISTRICT	70000	8740	00000000000	8740	SW COLLECTION CENTER FEE	0	00000008100	00000000000	8100	DISPOSAL FEE	0	00000003400	00000000000	3400		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0
0000 YAMAHA PRO VXR	1530	1052008	0	00000000	OPEN		00000001530	104	MAYER FIRE DISTRICT	N	ROTER COUNTY	617000	00000001249	00000000000	1249	MAYER FIRE DISTRICT	70000	142	00000000000	142	LATE LIST PENALTY	0	00000000139	00000000000	139		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0		0	00000000000	00000000000	0

The total for this is 988.08

But if we try to break down the bill into the parts that make the
total. We will get these results.

ROTER COUNTY 0.617 770.38
MAYER FIRE DIST 0.07 87.40
SW COLLECTION CENTER FEE 0 81.00
DISPOSAL FEE 0 34.00
ROTER COUNTY 0.617 12.49
MAYER FIRE DIST 0.07 1.42
LATE LIST PENALTY 0 1.39

Problem is that they dont want to see ROTER COUNTY twice nor MAYER
FIRE DIST and want a total for anything owed to roter county which
would be 782.87. We can sum the column fine but deciding if there
are two of the same column names in the results set, then showing
each different result and then displaying the not summed amount is our problem.

If you look at the data for LATE LIST PENALTY it is under SW
COLLECTION CENTER FEE. How do we not sum this column when the data
is different in the results set and show the correct not summed
amount.

This is just one example, the data is like this is any of the extra
20 fields.

I hope someone can understand what I just typed.

nathans
Aged Yak Warrior

938 Posts

Posted - 2007-09-18 : 13:56:22

Help us help you... follow the guidelines here:

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources