set condition via flag column - 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 2008 Forums

Transact-SQL (2008)

set condition via flag column

Author

Topic

yingchai
Starting Member

33 Posts

Posted - 2011-10-17 : 03:35:30

Hi,

I have a source table below:


organization interco account curr amount organization interco account curr amount
3050000 2650000 2331200D USD 315.00 2650000 3050000 4132000D USD -15.00
3050000 3051000 2331200D USD -58421.43 3051000 3050000 4132000D USD -1115.16
3050000 3051000 2331200D USD -58421.43 3051000 3050000 4163100D USD -352000.00
3050000 3051000 2331200D USD -58421.43 3051000 3050000 4163200D USD -10.31

Below is my interco_flag table. It determines which account to match by the flag values to filter the records in source table.


account flag
2331100D 1
2331200D 2
4131000D 1
4132000D 2

Since the flag = 2 matches the account ID in the source table, the desired results will look like this:


organization interco account curr amount organization interco account curr amount
3050000 2650000 2331200D USD 315.00 2650000 3050000 4132000D USD -15.00
3050000 3051000 2331200D USD -58421.43 3051000 3050000 4132000D USD -1115.16

How can I construct the sql in order to match the account ID flag in the source table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 04:03:42

can you post what eaxctly was your initial query which gave you above results. I see multiple columns with same name so if you can provide correct source column names then I will be able to provide correct query.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-17 : 04:04:42

quote:
Since the flag = 2 matches the account ID in the source table

you can highlight which column of interco_flag table matches with which column of the source table ?

what about flag = 1 ?

KH
[spoiler]Time is always against us[/spoiler]

yingchai
Starting Member

33 Posts

Posted - 2011-10-17 : 10:40:14

Hi visakh16,

Below is the query for the source table:


select a.organization, a.interco, a.account, a.currency, a.amount, b.organization, b.interco, b.account, b.currency, b.amount
from AR_view as a left join AP_view as b on a.organization = b.interco and b.organization = a.interco
WHERE  (a.organization = '3050000')

Hi khtan,

The source table does not have flag values. The matching between the source table and the interco_flag table is the account column. The basic rules here is:
Rule 1 - Note that the account 2331200D and account 4132000D exists in the first row in source table. These two accounts carries the same flag value (flag = 2) in the interco_flag table. Hence, show this result.

Rule 2 - Note that the account 2331200D and account 4163100D exists in the third row in source table. These two accounts do not have the same flag values in the interco_flag table. Hence, this result will not be shown.

Hope this will clarify you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 10:49:25

[code]
select a.organization, a.interco, a.account, a.currency, a.amount, b.organization, b.interco, b.account, b.currency, b.amount
from AR_view as a
left join AP_view as b on a.organization = b.interco and b.organization = a.interco
inner join (select max(case when rn=1 then account else null end) as accnt1,
max(case when rn=2 then account else null end) as accnt2
from
(
select row_Number() over (partition by flag order by account asc) as rn,account, flag
from interco_flag
)a
group by flag
)b
on b.accnt1 = a.account
and b.accnt2 = b.account
WHERE (a.organization = '3050000')
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yingchai
Starting Member

33 Posts

Posted - 2011-10-18 : 02:01:54

Hi visakh16,

Thanks for the query. I have one concern here which is if my interco_flag table is quite huge and have more than 2 sets of flags. In reality, the flags will increase from time to time.


account flag
2331100D 1
2331200D 2
2311310D 3
2331310D 4
4131000D 1
4132000D 2
4111210D 3
4133100D 4

Based on your query, I tried to change both the rn values to 4 but it returned empty results.

PLease advise.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 02:04:21

no need to change rn values. that will be 1,2 for all the flag values. retain it as it is and check

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yingchai
Starting Member

33 Posts

Posted - 2011-10-18 : 06:09:16

Thanks visakh16...it works perfectly...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 07:35:59

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources