having clause 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

having clause problem

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-05-12 : 04:24:59

Hi friends can anybody correct the below query:
Actually after the group by Zero value should not get display but it is displayed:
select comp.cmp_id,

acct.account_id,

emp.sub_analysis_id,

fb.finbook_id,

period.period_wid,

sum(stg.NetBalance) as Amount

from travel_advance_tb stg (nolock)

inner join Fin_Collection_company_dim comp (nolock)

on stg.company_code = comp.company_code

inner join corp_account_dim_tb acct (nolock)

on stg.account_code = acct.account_code

inner join corp_emp_dim_tb emp (nolock)

on stg.company_code = emp.company_code

and stg.sub_analysis_code = emp.sub_analysis_code

inner join corp_fb_dim_tb fb (nolock)

on stg.company_code = fb.company_code

and stg.fb_id = fb.fb_id

inner join sal_period_dim_tb period (nolock)

on stg.month_wid=period.month_wid

where stg.sub_analysis_code='1780'

group by comp.cmp_id,account_id,sub_analysis_id,finbook_id,period_wid

having sum(stg.NetBalance)!=0

output is:
cmp_id account_id sub_analysis_id finbook_id period_wid
1 1 124 4 35 -6759.00000000
1 1 124 4 36 -1475.00000000
1 1 124 4 37 8234.00000000
1 1 124 4 38 -540.00000000
1 1 124 4 39 540.00000000
1 1 124 4 40 -1425.00000000
1 1 124 4 41 1425.00000000
1 1 124 4 42 -24018.00000000
1 1 124 4 43 47018.00000000
1 1 124 4 44 -23000.00000000
1 1 124 4 46 -6093.00000000
1 1 124 4 47 6093.00000000
1 2 124 4 30 12474.00000000
1 2 124 4 31 -12474.00000000
1 3 124 4 37 6695.52000000
1 3 124 4 38 -7196.00000000
1 3 124 4 39 500.00000000
1 3 124 4 41 0.48000000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-12 : 04:32:22

Didnt get that. DId you mean to say that alst row should not be displayed? The value there is not 0 but you have decimal value.Can you elaborate on what output you're expecting?

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-05-12 : 04:34:26

yes visakh16 all row should not be displayed when the sum(netbalance)=0

quote:
Originally posted by visakh16

Didnt get that. DId you mean to say that alst row should not be displayed? The value there is not 0 but you have decimal value.Can you elaborate on what output you're expecting?

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-05-12 : 04:35:31

sorry (netbalance)!= 0

quote:
Originally posted by sent_sara

yes visakh16 all row should not be displayed when the sum(netbalance)=0

quote:
Originally posted by visakh16

Didnt get that. DId you mean to say that alst row should not be displayed? The value there is not 0 but you have decimal value.Can you elaborate on what output you're expecting?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-12 : 04:37:36

quote:
Originally posted by sent_sara

yes visakh16 all row should not be displayed when the sum(netbalance)=0

quote:
Originally posted by visakh16

Didnt get that. DId you mean to say that alst row should not be displayed? The value there is not 0 but you have decimal value.Can you elaborate on what output you're expecting?

But its not 0 but you have a decimal value. Are you looking at this?

select comp.cmp_id, 

acct.account_id,

emp.sub_analysis_id,

fb.finbook_id,

period.period_wid,

sum(stg.NetBalance) as Amount





from travel_advance_tb stg (nolock)



inner join Fin_Collection_company_dim comp (nolock)

on stg.company_code = comp.company_code



inner join corp_account_dim_tb acct (nolock)

on stg.account_code = acct.account_code



inner join corp_emp_dim_tb emp (nolock)

on stg.company_code = emp.company_code

and stg.sub_analysis_code = emp.sub_analysis_code



inner join corp_fb_dim_tb fb (nolock)

on stg.company_code = fb.company_code

and stg.fb_id = fb.fb_id



inner join sal_period_dim_tb period (nolock)

on stg.month_wid=period.month_wid

where stg.sub_analysis_code='1780'

group by comp.cmp_id,account_id,sub_analysis_id,finbook_id,period_wid

having floor(sum(stg.NetBalance)!=0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources