August 31, 2011 at 9:45 am
Hi,
I'm building a query related to account-agency
This is the query that I want to show:
accountcode / description / balance-1 / balance-2
10000 / A-description / 1.00 / 2.00
11000 / B-description / 0 / 0
11100 / c-description / 0 / 0
This is my query is working fine, if I check only one agency
use AGENCYFORM
GO
Select BB.ORDENV,BB.acountcode,BB.Description,
(select cc.balance from dbo.MOV AS cc
where
cc.accountcode=BB.acountcode and
cc.month='03' and
cc.form='F' and
cc.codeagency='05961'
) as
'05961'
from dbo.T_PLANT1 AS BB
I'm now interested that codeagency be dynamic, but this code is in another table, a could be hundreds of agencies, so static using this code is not an option.
what's your opinion?
Thanks in advance for any idea,
August 31, 2011 at 11:46 am
luismarinaray (8/31/2011)
use AGENCYFORMGO
Select BB.ORDENV,BB.acountcode,BB.Description,
(select cc.balance from dbo.MOV AS cc
where
cc.accountcode=BB.acountcode and
cc.month='03' and
cc.form='F' and
cc.codeagency='05961'
) as
'05961'
from dbo.T_PLANT1 AS BB
I'm now interested that codeagency be dynamic, but this code is in another table, a could be hundreds of agencies, so static using this code is not an option.
Hi,
You will need to join the table in the outer query to the table that has the agency code and then reference it from the inner query...like so:
use AGENCYFORM
GO
Select BB.ORDENV
, BB.acountcode
, BB.Description
, (
select cc.balance
from dbo.MOV AS cc
where cc.accountcode=BB.acountcode
and cc.month='03'
and cc.form='F'
and cc.codeagency= AC.codeagency
) as balance
from dbo.T_PLANT1 AS BB
inner join <table_with_agency_code> as AC on ...
I have highlighted the parts that I have changed. Hope this helps.
Edit: The above gives a typical solution to be able run this query without hard-coding the agency code.
However, you are not really explaining what the relationship between accounts and agencies are. Can a single account have many agencies? If so, do you want the latest balance for that account or the sum/average?
You will have to give more information if the above response is not what you were looking for...
August 31, 2011 at 11:55 am
If there is nothing in the outer query that limits the account code, you can use a CROSS APPLY instead.
SELECT p.ORDENV, p.acountcode, p.Description, m.CodeAgency, m.Balance
FROM dbo.T_PLANT1 AS p
CROSS APPLY (
SELECT m.balance
FROM dbo.MOV AS m
WHERE m.accountcode=p.acountcode
and m.month='03'
and m.form='F'
) AS m
If you want a single record for each Account Code with separate columns for each Code Agency, you should look up the excellent article on Pivots, Cross-Tabs, and Dynamic Pivots on this site.
It appears that you are using sequential aliases, e.g. AA, BB, CC, .... This is a BAD IDEA, because it makes your queries hard to read and update. Your aliases should relate to the table name in SOME WAY.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 31, 2011 at 11:55 am
I'm afraid you'll have to post more details in order for me to understand what you're looking for. For a start, your sample code does not match your sample results.
If Martin did not solve your problem, please post SQL that creates a sample table and fills it with sample data, and make sure that your code returns the results you want for that data, then we can work on figuring out how to make it dynamic.
Be forewarned though that SQL server does not do recursion well, and SQL does not do well with producing results containing a dynamic number of columns. If you need to do this, especially if you need dynamic columns, you should see if it's possible to do it in whatever application is receiving the results.
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
August 31, 2011 at 1:19 pm
Hi,
Impressed by all your responses..thanks...
I will try to answer it by order,
1.- Yes, a single account can have many agencies or perhaps only one or none.
2.- I'm interested in latest balance
3.- OK. taking notes about sequential aliases, thanks for the recommendation
4.-
I'm using this code but the database is inherited and not very much optimized
I have dbo.mov for all transactions of accounts, agencies, balances
I have dbo.plant as a table accounts
I have dbo.Agencies as a table for agencies
Our "ideal" solution would be a view organized in the following manner
Accounts Description Agency1 Agency2 AgencyN
Balance1 Balance 2 BalanceN
I have played with pivots but I remain unsatisfied with the results,
because agencies table not remain in a fixed number, so I was keeping an eye what other options I could have.
I don't know if i have explained very well, I hope so, however, Thanks a lot
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply