Query Part 3 - 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)

Query Part 3

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2003-01-20 : 22:11:09

Hi there

I have this table called maBU_lupFOChargeCodeAccCompYTDBudget which contains :

ChargeCode; AccCompID; AccCompDesc; BudgetAMOUNT
012500PE; 1; "A"; 1000
012500PE; 2; "B"; 2000
012500PE; 3; "C"; 3000
012500PE; 4; "D"; 4000
012507F3; 1; "A"; 1000
012507F3; 3; "C"; 3000
012607XX; 3; "C"; 3000

Also, I got the view which a distinct of ChargeCode in maBU_lupFOChargeCodeAccCompYTDBudget. I called this viwCompDesc .. and it has a sctcrure like this:

AccCompID; AccCompDesc;
1, "A";
2; "B";
3; "C";
4; "D";

By combining these 2 table/view, I want have a record like this below IF I filter by ChargeCode = 012507F3

AccCompID; AccCompDesc; BudgetAMOUNT
1, "A"; 1000
2; "B"; NULL
3; "C"; 3000
4; "D"; NULL

Or If I filter by ChargeCode = 012607XX .. it will be like (result that I want):

AccCompID; AccCompDesc; BudgetAMOUNT
1, "A"; NUL
2; "B"; NULL
3; "C"; 3000
4; "D"; NULL

So .. it basically using the full list (view) as a based if there is a value associated with AccCompID where ChargeCode = XXXX populated with value otherwise leave it NULL/0.

Any ideas to beat this query?

Hope this is clear.

Edited by - valdyv on 01/20/2003 22:49:08

Edited by - valdyv on 01/20/2003 22:54:28

Edited by - valdyv on 01/20/2003 22:55:51

Edited by - valdyv on 01/20/2003 22:59:31

Edited by - valdyv on 01/20/2003 23:00:29

Edited by - valdyv on 01/20/2003 23:03:20

SamC
White Water Yakist

3467 Posts

Posted - 2003-01-20 : 22:36:34

These tables don't look complex, but I don't understand what you are trying to do??

I think you want to develop a query that will produce the last result. I'm not clear on what the result should be. Here is my guess

1 - List all DISTINCT AccCompID, AccCompDesc
2 - display the max BudgetAMOUNT for all ChargeCodes (you list 1000 for A for a ChargeCode showing 500, so I assume you want the MAX?)
3 - display NULL for BudgetAMOUNT if the ChargeCode does not exist for AccCompID.

Also in your VIEW, there's a third column you mention that is not shown.

Describe what you're trying to do and someone (maybe I) will give it a try.

Sam

dewacorp.alliances

452 Posts

Posted - 2003-01-20 : 23:07:08

Hi SamC ... sorry I made this not clear. I have already updated the problem to make it more clear.

Val

SamC
White Water Yakist

3467 Posts

Posted - 2003-01-20 : 23:56:46

SELECT A.AccCompID, A.AccCompDesc, B.BudgetAMOUNT
FROM
( -- this first derived table contains the distinct AccCompIDs
SELECT DISTINCT AccCompID, AccCompDesc
FROM maBU_lupFOChargeCodeAccCompYTDBudget
) A
LEFT OUTER JOIN
( -- this second derived table finds the BudgetAMOUNTS for the ChargeCode
SELECT AccCompID, BudgetAMOUNT
FROM maBU_lupFOChargeCodeAccCompYTDBudget
WHERE ChargeCode = 012507F3
) B ON A.AccCompID = B.AccCompID

I haven't tried this, so it may need debugging.

The first derived table A is just the distinct AccCompIDs

LEFT OUTER JOIN will match table A to B which holds the BudgetAMOUNT(s) for the ChargeCode you are searching for. If there's no match, LEFT OUTER will provide a NULL value. (INNER JOIN will eliminate the row if there's no match).

HTH,

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources