
1. Re: Calculating Units
Rahul_India Aug 14, 2014 3:50 PM (in response to saluteSalem)Please post the DDLs and DMLs

2. Re: Calculating Units
Etbin Aug 14, 2014 5:32 PM (in response to saluteSalem)Maybe
with stock as
(select 'cetal' medicine,1 max_unit,2 mid_unit,3 min_unit,400 max_to_min_factor,20 mid_to_min_factor from dual union all
select 'latec',1,2,3,20,2 from dual
)
MEDICINE MAX_UNIT MID_UNIT MIN_UNIT MAX_TO_MIN_FACTOR MID_TO_MIN_FACTOR cetal 1 2 3 400 20 select :items min_units,
:medicine medicine,
trunc(:items / max_to_min_factor) max_units,
trunc(mod(:items,max_to_min_factor) / mid_to_min_factor) mid_units,
mod(:items,max_to_min_factor / mid_to_min_factor) min_units
from stock
where medicine = :medicine
MIN_UNITS MEDICINE MAX_UNITS MID_UNITS MIN_UNITS 999 cetal 2 9 19 Regards
Etbin

3. Re: Calculating Units
saluteSalem Aug 14, 2014 5:55 PM (in response to Etbin)i think you did not get me ,
i want to know the quantity of the medicine in the stock ,
in this case it would be :
0 carton.
2 packages .
1 tab .
because the package has 2 tabs .
of course there other medicines with different attributes , may be it could be
2 carton .
4 packages .
1 tab .

4. Re: Calculating Units
Frank Kulash Aug 14, 2014 10:22 PM (in response to saluteSalem)Hi,
saluteSalem wrote:
i think you did not get me ,
i want to know the quantity of the medicine in the stock ,
in this case it would be :
0 carton.
2 packages .
1 tab .
because the package has 2 tabs .
of course there other medicines with different attributes , may be it could be
2 carton .
4 packages .
1 tab .
Sorry, I don't understand how you get the desired results from the given sample data. Explain, step by step, how you figure those are the right results from that data.
It would help if you posted maybe 5 or 10 rows of sample data instead of just 1, along with the results you want from each.

5. Re: Re: Calculating Units
Etbin Aug 15, 2014 12:48 AM (in response to saluteSalem)Just changing data, column order and column names
with stock as
(select 'cetal' medicine,1 max_unit,2 mid_unit,3 min_unit,400 max_to_min_factor,20 mid_to_min_factor from dual
)
select :medicine medicine,
:items tabs_quantity,
trunc(:items / max_to_min_factor) cartons,
trunc(mod(:items,max_to_min_factor) / mid_to_min_factor) packages,
mod(:items,max_to_min_factor / mid_to_min_factor) tabs
from stock
where medicine = :medicine
MEDICINE TABS_QUANTITY CARTONS PACKAGES TABS cetal 999 2 9 19 Regards
Etbin

6. Re: Calculating Units
saluteSalem Aug 15, 2014 3:46 AM (in response to Frank Kulash)the situation is :
 i have "5" tabs in the stock .
 the package contains "2" tabs , so the "5" tabs in the stock is "2" packages and "1" tab .
 so , i have "0" cartons , "2" packages , and "1" tab in the stock .  i think it 's clear .

there is the situation from the beginning :
insert into stock
(medicine_name , quantity , max_unit , mid_unit , min_unit , max_to_min_factor , mid_to_min_factor )
values
('cetal' ,5 , 1 , 2 , 3 , 20 , 2) ;
in the stock , this "5" tabs , should be ("0" cartons , 2 packages , 1 tab) .

insert into stock
(medicine_name , quantity , max_unit , mid_unit , min_unit , max_to_min_factor , mid_to_min_factor )
values
('buspar' ,7 , 1 , 2 , 3 , 20 , 2) ;
 0 cartons , 3 packages , 1 tab .

insert into stock
(medicine_name , quantity , max_unit , mid_unit , min_unit , max_to_min_factor , mid_to_min_factor )
values
('gramycin' ,20, 1 , 2 , 3 , 20 , 2) ;
 1 carton , 0 packages , 0 tabs .

is it clear now ?

7. Re: Calculating Units
saluteSalem Aug 15, 2014 3:55 AM (in response to Etbin)in reply to etbin ,
i intended to put the quantity as "7" , because when you divide "7 / 20" , the result is less than "1" . and there is no "mod" .
there could be numbers which i could divide on the max_to_min_factor , and the result will be more than "1" or less .

8. Re: Re: Calculating Units
Etbin Aug 15, 2014 4:36 AM (in response to saluteSalem)Sorry, I don't know where I was looking at (the copypaste dwarf did it again )
with stock as
(select 'cetal' medicine,5 quantity,1 max_unit,2 mid_unit,3 min_unit,20 max_to_min_factor,2 mid_to_min_factor from dual union all
select 'buspar',7,1,2,3,20,2 from dual union all
select 'gramycin',20,1,2,3,20,2 from dual
)
select medicine,
quantity,
trunc(quantity / max_to_min_factor) cartons,
trunc(mod(quantity,max_to_min_factor) / mid_to_min_factor) packages,
mod(quantity,mid_to_min_factor) tabs
from stock
MEDICINE QUANTITY CARTONS PACKAGES TABS cetal 5 0 2 1 buspar 7 0 3 1 gramycin 20 1 0 0 Regards
Etbin

9. Re: Calculating Units
chris227 Aug 15, 2014 5:04 AM (in response to saluteSalem)select
trunc( 5 / max_to_min_factor) cartons
,trunc( mod(5, max_to_min_factor) / mid_to_min_factor) packages
,5  trunc( mod(5, max_to_min_factor) / mid_to_min_factor)
* mid_to_min_factor tab
from stock
where medicine = 'cetal'
CARTONS PACKAGES TAB 0 2 1 
10. Re: Re: Re: Calculating Units
saluteSalem Aug 24, 2014 4:54 PM (in response to Etbin)Sorry Etbin
but , there is something wrong ,
i have an item which has a "Quantity" of "20" tabs (min_unit) , and " 10 as a max_to_min_factor " , and " 3 as a mid_to_min_factor " ,
i've tried this query , as you wrote :
select trunc(quantity / 10) cartons,
trunc(mod(quantity,10) / V_MID_FACTOR) packages,
mod(quantity,3) tabs
from ITEM_DETAIL
WHERE item = 1 ;
and i got
cartons packages tabs
2 0 2
which is completely wrong , i have to get
2 0 0
what is wrong with the code ?
thank you

11. Re: Re: Re: Re: Calculating Units
Etbin Aug 25, 2014 1:19 AM (in response to saluteSalem)Sorry (maybe this time ...)
with stock as
(select 'cetal' medicine,5 quantity,1 max_unit,2 mid_unit,3 min_unit,20 max_to_min_factor,2 mid_to_min_factor from dual union all
select 'buspar',7,1,2,3,20,2 from dual union all
select 'gramycin',20,1,2,3,10,3 from dual
)
select medicine,
quantity,
trunc(quantity / max_to_min_factor) cartons,
trunc(mod(quantity,max_to_min_factor) / mid_to_min_factor) packages,
quantity 
max_to_min_factor * trunc(quantity / max_to_min_factor) 
mid_to_min_factor * trunc(mod(quantity,max_to_min_factor) / mid_to_min_factor) tabs
from stock
MEDICINE QUANTITY CARTONS PACKAGES TABS cetal 5 0 2 1 buspar 7 0 3 1 gramycin 20 2 0 0 Regards
Etbin

12. Re: Calculating Units
saluteSalem Aug 25, 2014 7:44 AM (in response to Etbin)This is Etbin i know .
thank you so much