Home » SQL & PL/SQL » SQL & PL/SQL » help in query (oracle 10g)  () 1 Vote
help in query [message #352991] Fri, 10 October 2008 07:39 Go to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
Hi ,
I have two tables.Test_branch,test_customer.
test_branch contains the branchwise information ie min and max
amount that branch can allocate.also contains the min and max cumulative amount for all the branches .
Now i need to pick only those customers which falls between min and max limits of that branch and cumulative total(min and max) should also be satisfied.
Till here it is ok.
Crux is ..(if u go through the data i test prepared for this),I need to pick those customers of the branch which nears the cumulative max sum for that branch ie.
CUSTID, BRANCHID, PURCHASE_AMT
1 1 10
1 1 20
1 1 30
1 1 110
(not 41,42,1 as 10+20+30+110>10+20+30+41+42+1)



create table TEST_BRANCH
(
  CUMULATIVE_MAX_SUM NUMBER,
  CUMULATIVE_MIN_SUM NUMBER,
  BRANCHID           NUMBER,
  BRANCHMAX          NUMBER,
  BRANCHMIN          NUMBER
)
;

create table TEST_CUSTOMER
(
  CUSTID       NUMBER,
  BRANCHID     NUMBER,
  PURCHASE_AMT NUMBER
)
;


insert into TEST_BRANCH (CUMULATIVE_MAX_SUM, CUMULATIVE_MIN_SUM, BRANCHID, BRANCHMAX, BRANCHMIN)
values (300, 250, 1, 100, 200);
insert into TEST_BRANCH (CUMULATIVE_MAX_SUM, CUMULATIVE_MIN_SUM, BRANCHID, BRANCHMAX, BRANCHMIN)
values (300, 250, 2, 80, 150);


insert into TEST_CUSTOMER (CUSTID, BRANCHID, PURCHASE_AMT)
values (1, 1, 20);
insert into TEST_CUSTOMER (CUSTID, BRANCHID, PURCHASE_AMT)
values (2, 1, 30);
insert into TEST_CUSTOMER (CUSTID, BRANCHID, PURCHASE_AMT)
values (3, 1, 10);
insert into TEST_CUSTOMER (CUSTID, BRANCHID, PURCHASE_AMT)
values (4, 1, 110);
insert into TEST_CUSTOMER (CUSTID, BRANCHID, PURCHASE_AMT)
values (5, 1, 42);
insert into TEST_CUSTOMER (CUSTID, BRANCHID, PURCHASE_AMT)
values (6, 1, 1);
insert into TEST_CUSTOMER (CUSTID, BRANCHID, PURCHASE_AMT)
values (7, 1, 41);


Regards,
Navneet
Re: help in query [message #353346 is a reply to message #352991] Mon, 13 October 2008 07:57 Go to previous messageGo to next message
aniruddha_jathar
Messages: 5
Registered: September 2008
Junior Member
Navneet,

Your requirement is not at all clear. Can you explain what is required more clearly?
Re: help in query [message #353459 is a reply to message #352991] Mon, 13 October 2008 23:34 Go to previous messageGo to next message
Amulyadeep
Messages: 6
Registered: October 2008
Junior Member
Hi Navneet,

Please try

select distinct a.custid, a.branchid, b.cu_max_sum,b.cum_min_sum,b.branchid
from test_branch b inner join test_customer a
on a.branch_id = b.branch_id
order by cumulative_max_sum desc
Re: help in query [message #353504 is a reply to message #352991] Tue, 14 October 2008 01:10 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Still Not answered because of not having clarity in requirement .

Explain id detail if you still face the problem Or Post the solution if you managed to get to get it ( useful for other's reference )

Thumbs Up
Rajuvan.
Re: help in query [message #353519 is a reply to message #353504] Tue, 14 October 2008 01:47 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
I m sorry if I could not explain my question properly.
my question is, suppose sum total of amount for particular branch is defined not be greater than say 200. Now I have many customers in that branch with diffrent amounts. I need to pick randomly customers in such a way that their amount sum nears 200.
for example
cust id branch amount
1 1 10
2 1 60
3 1 120
4 1 30
5 1 9
6 1 110

If I do the running total then I would end up with customerids 1,2,3 (sum total=190<200) but I need 1,2,3,9 coz(sum total=199<200) this sum is nearer to 200 than previous one.
I think it is clearer now.Request you to please provide some hint.

regards,
Navneet
Re: help in query [message #353539 is a reply to message #353519] Tue, 14 October 2008 02:33 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
I am sorry but i was unable to understand it
Re: help in query [message #353547 is a reply to message #353519] Tue, 14 October 2008 02:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Slightly clearer, but not much.

If I understand you correctly, you want the set of customers for each branch that has the largest total amount that is less than 200
Is this correct?
Re: help in query [message #353550 is a reply to message #353547] Tue, 14 October 2008 02:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assuming I'm right about wat you want, this should do the trick.

I'll let you figure out how to convert it to work for multiple branches yourself.
create table test_001 (cust_id number, branch number, amount number);


insert into test_001 values (1, 1, 10);
insert into test_001 values (2, 1, 60);
insert into test_001 values (3, 1, 120);
insert into test_001 values (4, 1, 30);
insert into test_001 values (5, 1, 9);
insert into test_001 values (6, 1, 110);

commit;

select cust_list, sum(amount)
from   test_001 t1
      ,(select sys_connect_by_path(cust_id,',')||',' cust_list
        from   test_001
        connect by nocycle prior cust_id < cust_id)
where instr(cust_list,','||to_char(t1.cust_id)||',') > 0
group by cust_list
having sum(amount) <200
order by sum(amount) desc;
Re: help in query [message #353558 is a reply to message #352991] Tue, 14 October 2008 03:14 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member



Nice One JRowbottom !!!

Thumbs Up
Rajuvan.
Re: help in query [message #353560 is a reply to message #353550] Tue, 14 October 2008 03:16 Go to previous message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
Thanks JRowbottom,
I have got the idea now. But in your query, customer ids are coming as comma seperted list. i need them in saperate rows.by the way i will try to modify the query accordingly and let u know the result.
Thanks again for help

Regards,
Navneet
Previous Topic: Transaction Management Oracle
Next Topic: Displaying Seconds as Counter
Goto Forum:
  


Current Time: Fri Dec 09 15:17:05 CST 2016

Total time taken to generate the page: 0.29207 seconds