February 2, 2015 at 3:33 pm
Hello everyone,
i am pretty new and still learning SQL for my work.
i have a query to pull all the data greater than 5,000,000 as below which worked sucessfully
select a.bf_fund_cd fund, a.bf_bdob_cd object, b.lev5, b.bf_orgn_cd,sum(a.data) data
from cp_o_pbuser.bf_data a
inner join cp_o_pbuser.bf_orgn_cnsl_tbl b
on a.bf_orgn_cd = b.bf_orgn_cd
WHERE a.bf_tm_perd_cd in (select bf_tm_perd_chld_cd from cp_o_pbuser.bf_tm_perd_cnsl_tbl where bf_tm_perd_select_cd='FINALTOT')
and a.bf_bdob_cd in ('R_9001')
having sum(a.data)>5000000
group by a.bf_fund_cd, a.bf_bdob_cd, b.lev5,b.bf_orgn_cd
order by a.bf_bdob_cd, b.lev5
here is the result
SS_26300_1 - Lev5 (everything rollup to this one)
SS_26460_2 - $ 5,000,000 -->bf_orgn_cd (showed)
SS_26461_2 - $6,000,000 -->bf_orgn_cd (showed)
SS_26473_2 - $5,500,000 -->bf_orgn_cd (showed)
SS_26475_2 -$4,000,000 -->bf_orgn_cd (not showed, i want to this record to appear too)
However, according to the new change, i will need to pull out all the data even though they are smaller than 5,000,000 in the lev5 colunm. ex: there is one record missing in SS_26300_1 with SS_26475_2 in BF_ORGN_CD and the data is 4,000,000 which i need to include in my query.
i really appreciate of your time and help.
Sincerely,
February 2, 2015 at 3:41 pm
If you want to see all values, remove the "having" condition:
having sum(a.data)>5000000
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 2, 2015 at 3:55 pm
thank you for your reply. i think i didnt express it clearly.
But if i removed the having sum(a.data)>5000000, i will also get all the results smaller than 5000000.
for example :
SS_21111_1 -Lev5 (dont need to show) ---> less than 5,000000
SS_21112_2 -$1,500,000(dont need to show)
SS_21113_2 -$1,750,000(dont need to show)
SS_26300_1 - Lev5 (everything rollup to this one) greater than 5,000,000 (everything needs to show)
SS_26460_2 - $ 5,000,000 -->bf_orgn_cd (showed)
SS_26461_2 - $6,000,000 -->bf_orgn_cd (showed)
SS_26473_2 - $5,500,000 -->bf_orgn_cd (showed)
SS_26475_2 -$4,000,000(i want to this one to show)
thank you veyr much again.
Kenny
February 2, 2015 at 4:53 pm
You have to remember that I do not know what your data is.
GROUP BY a.bf_fund_cd, a.bf_bdob_cd, b.lev5,b.bf_orgn_cd
...
SS_21111_1 -Lev5 (dont need to show) ---> less than 5,000000
SS_21112_2 -$1,500,000(dont need to show)
SS_21113_2 -$1,750,000(dont need to show)
SS_26300_1 - Lev5 (everything rollup to this one) greater than 5,000,000 (everything needs to show)
SS_26460_2 - $ 5,000,000 -->bf_orgn_cd (showed)
SS_26461_2 - $6,000,000 -->bf_orgn_cd (showed)
SS_26473_2 - $5,500,000 -->bf_orgn_cd (showed)
SS_26475_2 -$4,000,000(i want to this one to show)
What value/string is the "bf_fund_cd"?
What value/string is the "bg_bdob_cd"?
What value/string is "lev5"?
What value/string is the "bf_orgn_cd"?
Do these levels add up to each other? What level needs to reach $5M to show all records?
Some sample data and results, with how the result was calculated, would be really helpful.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 2, 2015 at 5:13 pm
Thank you very much.
Right now, i will need to show all the data which is over 5,000,000 in lev5.
Please check if you can see the attachment.
Thank you.
February 3, 2015 at 12:10 am
Hi Kenny,
Why do you need the record with $4000000 to show? What is the reason for this?
Regards,
William
February 3, 2015 at 8:59 am
Thank you very much for all your kind replies.
I am pretty new in SQL and if my post dosent make sense, i am sorry about this.
I need to show all the lev6 records if lev5 surpasses 5M. i hope it makes sense to you.
Once again, i really appreciate of your helps.
Sincerely,
February 3, 2015 at 9:18 am
I don't quite understand what you want. You said in an earlier post that the amount for $4000000 is required to show as well. If the rule is to show data only where sum is greater than $5000000 then you don't have a problem. So, do you need to show the record if it's $4M or $5M?
February 3, 2015 at 10:25 am
Again, you need to explain "lev5" to us. We do not know your data.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 3, 2015 at 10:29 am
Thanks William,
in my result, it shows
SS_26300_1 - Lev5 (everything rollup to this one) greater than 5,000,000 (everything needs to show)
SS_26460_2 - $ 5,000,000 -->bf_orgn_cd (showed)
SS_26461_2 - $6,000,000 -->bf_orgn_cd (showed)
SS_26473_2 - $5,500,000 -->bf_orgn_cd (showed)
SS_26475_2 -$4,000,000(i want to this one to show) --> it didnt show because the value is below 5M.
According to my boss, he wants to show all the rollups SS_26460_2,SS_26461_2,SS_26473_2 and SS_26475_2 to SS_26300_1.
thanks.
February 3, 2015 at 5:59 pm
hello all,
thanks again for all your helps.
Finally i used a stupid way to retrieve all the data i want as below. it took about 8 seconds to get all the results out.
Do you have any way to make this query more efficient? Thank you
select a.bf_fund_cd fund, a.bf_bdob_cd object, b.lev5,a.bf_orgn_cd, sum(a.data) data
from cp_o_pbuser.bf_data a
inner join cp_o_pbuser.bf_orgn_cnsl_tbl b
on a.bf_orgn_cd = b.bf_orgn_cd
WHERE a.bf_tm_perd_cd in (select bf_tm_perd_chld_cd from cp_o_pbuser.bf_tm_perd_cnsl_tbl where bf_tm_perd_select_cd='FINALTOT')
and a.bf_bdob_cd in ('R_9001')
and (a.bf_fund_cd , a.bf_bdob_cd , b.lev5) in
(
select a.bf_fund_cd fund, a.bf_bdob_cd object, b.lev5
from cp_o_pbuser.bf_data a
inner join cp_o_pbuser.bf_orgn_cnsl_tbl b
on a.bf_orgn_cd = b.bf_orgn_cd
WHERE a.bf_tm_perd_cd in (select bf_tm_perd_chld_cd from cp_o_pbuser.bf_tm_perd_cnsl_tbl where bf_tm_perd_select_cd='FINALTOT')
and a.bf_bdob_cd in ('R_9001')
having sum(a.data)>5000000
group by a.bf_fund_cd, a.bf_bdob_cd, b.lev5
)
group by a.bf_fund_cd, a.bf_bdob_cd, b.lev5,a.bf_orgn_cd
order by b.lev5
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply