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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy