|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
December 15th, 2004, 02:27 PM
|
|||
|
|||
|
Stuck on this easy query for awhile
Hi,
I have this query: Code:
SELECT LB.WBS2, LB.WBS3, LedgerAR.WBS2 AS Expr1, LedgerAR.WBS3 AS Expr2, LB.AmtBud AS amtbud, SUM(LedgerAR.Amount * - 1) AS amt
FROM LB LEFT OUTER JOIN
LedgerAR ON LedgerAR.WBS1 = LB.WBS1 AND LedgerAR.WBS2 = LB.WBS2 AND LedgerAR.WBS3 = LB.WBS3
WHERE (LB.WBS1 = '001-298')
GROUP BY LB.WBS2, LB.WBS3, LedgerAR.WBS2, LedgerAR.WBS3, LB.AmtBud
it produces the following output: Code:
WBS2 WBS3 Expr1 Expr2 amtbud amt 0141 01 0141 01 3000 95 1217 010 1217 010 8000 7290 1217 080 4000 1217 090 1217 090 32000 65960 1217 110 1217 110 18000 34450 1217 120 1217 120 44000 38010 1217 140 2000 1217 170 1217 170 13500 935 1217 180 4500 1217 220 500 1217 260 1000 1217 500 1217 500 800 622.5 1222 160 4000 I want to sum the amtbud column like I did for the amt column. and group everything based on WBS2. However, I keep getting an outrageous amount for the amtbud. This is what is seems to be summing up: Code:
0141 01 0141 01 3000 0 0141 01 0141 01 3000 47.5 1217 090 1217 090 32000 0 1217 090 1217 090 32000 0 1217 090 1217 090 32000 0 1217 090 1217 090 32000 0 1217 090 1217 090 32000 0 1217 090 1217 090 32000 0 1217 090 1217 090 32000 0 1217 090 1217 090 32000 0 1217 090 1217 090 32000 0 etc.... Any help will be appreciated I am just stumped.
|
|
#2
December 15th, 2004, 03:51 PM
|
||||
|
||||
|
"sum the amtbud column ... and group everything based on WBS2"
Code:
select LB.WBS2
, sum(LB.AmtBud) as amtbud
, sum(LedgerAR.Amount * - 1) as amt
from LB
left outer
join LedgerAR
on LedgerAR.WBS1 = LB.WBS1
and LedgerAR.WBS2 = LB.WBS2
and LedgerAR.WBS3 = LB.WBS3
where LB.WBS1 = '001-298'
group
by LB.WBS2
|
|
#3
December 16th, 2004, 08:20 AM
|
|||
|
|||
|
Yeah, I have doing that but I don't think the database is structured correctly. There have been quite a few workarounds that I have to do and on top of that there is a change going on that forces different ids such as wbs2 to split off into separate ids. So, it is good to know that I initially had the query right.
Thanks again.
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Stuck on this easy query for awhile |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
