group by problem - 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

General SQL Server Forums

New to SQL Server Programming

group by problem

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-03-05 : 00:13:56

In table fb_posted_trn_dtl table,

country currency amt
swiss usd ***
swiss chf ***
swiss eur ***

the output should be
country currency amt
swiss chf ***

for the below query the o/p is coming as:

currency country fb amt
CHF Swiss RSLSOLFB 3928.29000000
CHF Swiss RSLSOLFB 982.74000000
CHF Swiss RSLSOLFB 91.49000000

SELECT max(case when country='SWISS' and currency_code in('EUR','CHF','USD')
then 'CHF'
else currency_code end) as currency,
com.country,
com.fb_id,
(sum(CASE WHEN drcr_flag='DR' THEN isnull(base_amount,0) ELSE 0 END))
+ (sum(CASE WHEN drcr_flag='CR' THEN isnull(base_amount,0)* (-1) ELSE 0 END)) as ous_amount,
'outstanding'
FROM fin_ods..Fbp_posted_trn_dtl dtl(nolock) inner join fin_ods..Company_Fb_mst com(nolock)
ON dtl.fb_id=com.fb_id
WHERE account_code in(
SELECT DISTINCT account_code FROM fin_ods..as_opaccount_dtl(nolock)
WHERE ctrl_acctype ='CUSTRECACC'
)
and dtl.fb_id ='RSLSOLFB'-- @FB_Id_In
and posting_date <= '20071231'--dbo.date_format(@To_Posting_date_in,'YYYYMMDD')
AND com.country = 'Swiss'--@Country_Name_In
Group by country,dtl.currency_code,com.fb_id

can any one correct the query??

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-03-05 : 00:28:04

Try

SELECT case when country='SWISS' and currency_code in('EUR','CHF','USD')
then 'CHF'
else currency_code end as currency,
com.country,
com.fb_id,
(sum(CASE WHEN drcr_flag='DR' THEN isnull(base_amount,0) ELSE 0 END))
+ (sum(CASE WHEN drcr_flag='CR' THEN isnull(base_amount,0)* (-1) ELSE 0 END)) as ous_amount,
'outstanding'
FROM fin_ods..Fbp_posted_trn_dtl dtl(nolock) inner join fin_ods..Company_Fb_mst com(nolock)
ON dtl.fb_id=com.fb_id
WHERE account_code in(
SELECT DISTINCT account_code FROM fin_ods..as_opaccount_dtl(nolock)
WHERE ctrl_acctype ='CUSTRECACC'
)
and dtl.fb_id ='RSLSOLFB'-- @FB_Id_In
and posting_date <= '20071231'--dbo.date_format(@To_Posting_date_in,'YYYYMMDD')
AND com.country = 'Swiss'--@Country_Name_In
Group by country,case when country='SWISS' and currency_code in('EUR','CHF','USD')
then 'CHF'
else currency_code end ,com.fb_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources