OraFAQ Forum: SQL & PL/SQL » split a number or dates by slab (merged)

Home » SQL & PL/SQL » SQL & PL/SQL » split a number or dates by slab (merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

split a number or dates by slab (merged) [message #632955]

Sun, 08 February 2015 20:15

mahbbu
Messages: 6
Registered: August 2005

Junior Member

how can i split a number by a slab range.
say, number is 16.and in another table has slab definition like 1st slab 5, 2nd slab 4. now i need a query which will show result in two columns slab, value as:

1-5
2-4
3-7

[Updated on: Tue, 10 February 2015 02:31] by Moderator

Report message to a moderator

Re: split a number by slab [message #632957 is a reply to message #632955]

Sun, 08 February 2015 20:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/u/75531/
i'm oracle professional.no one can help me yet about oracle.cause they are all beginers
So it was said. So let it be written. So let it be done!

I am too much of a beginner to assist you.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Report message to a moderator

Generating date between given ranges [message #632988 is a reply to message #632955]

Mon, 09 February 2015 13:12

shanto150
Messages: 5
Registered: January 2015
Location: Dhaka

Junior Member

I have 2 table that table1 and Table2, I wants bellow's resutl how can I do this please help me please
/forum/fa/12493/0/

Attachment: ss.jpg
(Size: 42.48KB, Downloaded 1491 times)

Report message to a moderator

Re: Generating Numbers between given ranges [message #632989 is a reply to message #632988]

Mon, 09 February 2015 13:13

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

You need to use SUM function in its analytic form.

[Updated on: Mon, 09 February 2015 13:14]

Report message to a moderator

Re: Generating Numbers between given ranges [message #632990 is a reply to message #632989]

Mon, 09 February 2015 13:26

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

I have a feeling shanto150 and mahbbu is same person and this topic is same as split a number by slab

SY.

Report message to a moderator

Re: Generating Numbers between given ranges [message #632991 is a reply to message #632989]

Mon, 09 February 2015 13:26

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

1 row tables only exist in homework assignments

Report message to a moderator

Re: Generating Numbers between given ranges [message #632992 is a reply to message #632990]

Mon, 09 February 2015 13:55

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Solomon Yakobson wrote on Mon, 09 February 2015 20:26

I have a feeling shanto150 and mahbbu is same person and this topic is same as split a number by slab

SY.

You may be right. Smile

Report message to a moderator

Re: split a number by slab [message #632993 is a reply to message #632957]

Mon, 09 February 2015 17:09

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Probably the easiest way would be to place the ranges into a table:

N LOW HIGH
- --- ----
1   1    5
2   6    9
3  10 9999

Then you can simply select from the table (untested code)

SELECT N, LEAST(&param, HIGH) - LOW) + 1
FROM range_table
WHERE HIGH >= &param

If the ranges change each time you use them and you need to build them on the fly, then you could use a Nested Table Object that you load using a function / method, but this is a bit more complex.

Ross Leishman

Report message to a moderator

Re: split a number by slab [message #633004 is a reply to message #632993]

Tue, 10 February 2015 00:33

mahbbu
Messages: 6
Registered: August 2005

Junior Member

Quote:

Then you can simply select from the table (untested code)

Not working, anyway i'm focusing my topic in more brief way instead of short and snappy way.
it'll be great if anyone assist me to build an oracle query that
I have a table, Named as date_range_charge and example as follows

From_days | To_days | Charge
  1            4         0
  5            9        10
  10           14       20
  15          999       25

Now I will give two parameters in query like &param1= '10-JAN-2015' and &param2='25-JAN-2015'. based on 2 date parameters value and above table value the expected result will be:

Start_date | End_date |   Days| Amount
10-JAN-2015  13-JAN-2015    4      0
14-JAN-2015  18-JAN-2015    5     50
19-JAN-2015  23-JAN-2015    5    100
24-JAN-2015  25-JAN-2015    2     50

-Regards
Mahbub Shohag

Report message to a moderator

Re: split a number by slab [message #633005 is a reply to message #633004]

Tue, 10 February 2015 01:40

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

I merged both the topics as it appears they are only one but clearly specify what are your requirements with the test case you will post.

Report message to a moderator

Re: split a number by slab [message #633008 is a reply to message #633005]

Tue, 10 February 2015 02:33

mahbbu
Messages: 6
Registered: August 2005

Junior Member

please assist me to build an oracle query for below expected result that
I have 2 tables, Named as **detention_charge_slot** and **detention_invoice** example as follows

    create table detention_charge_slot
            (slot_no number(5),
            from_days number(10),
            to_days number(10),
            charge_amount number(10,2));

    insert into detention_charge_slot
            values (1,1,4,0);
            
            insert into detention_charge_slot
            values (2,5,9,10);
            
            insert into detention_charge_slot
            values (3,10,14,20);
            
            insert into detention_charge_slot
            values (4,15,999,25);

    create table detention_invoice
            (invoice_no number(10),
            invoice_dt date,
            delivery_dt date);
    insert into detention_invoice
            values(1,'10-JAN-2015','25-JAN-2015');

Expected result for invoice_no=1


    Start_date | End_date |   Days| Charge_Amount
    10-JAN-2015  13-JAN-2015    4      0
    14-JAN-2015  18-JAN-2015    5     10
    19-JAN-2015  23-JAN-2015    5     20
    24-JAN-2015  25-JAN-2015    2     25

Report message to a moderator

Re: split a number by slab [message #633009 is a reply to message #633008]

Tue, 10 February 2015 02:37

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Where is your problem in doing so?
Did you try to do something?
If so what?
Did you read about SUM analytic function in Database SQL Reference?
You still did not tell us what is your Oracle version.

Report message to a moderator