Need help with a conversion query 1

[RicardoPereira]

I must convert "units" in "boxes" using one single query.
Here is an example,


I have this information in the database:

Box1 | Box2 | Box3 | Box4 | Units
=================================
10 2 0 10 12
0 0 0 10 225
0 3 0 10 6
0 0 5 10 24

The boxes here are in units. I must convert the Units in boxes.

The first line means that the Box1 has 10 units, Box2 has 2 units, Box3 has 0 units and the Box4 has 10 units.
What i need to produce is the equivalence in boxes using the Total units.
I must convert the Units in Boxes from the left to the rigth (Box1 to Box4).


This means that i will got:

Box1 | Box2 | Box3 | Box4 | Unit
=================================
1 1 0 0 0
0 0 0 0 225
0 2 0 0 0
0 0 4 0 1

The first line means that the 12 units that i have were equivalent to 1,2 boxes of type Box1 [(12/10)=1,2]. That means the it is 1 complete Box1. Next step is the remaining 0,2
0,2*10=2 (the 10 is from the Box1 to convert again to units)
Now i must convert this 2 in the next level (Box2) ... [(2/1)=2] ... this ends here because it is an integer. If i got an decimal value, i should convert the decimal to an integer an the remaining shoulbe be converted to Box3 ...
The remaining, in the end, after the convertion from the Box1 to Box4 are defined as Units


Anyone could helme with this?

Thanks,
Ricardo

 

[vongrunt]

Expected results for rows 2 and 4 look odd:

Box1 | Box2 | Box3 | Box4 | Unit
=================================
...
0 0 0 0 225
...
0 0 4 0 1

Can you explain the same logic applied on these two rows?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[RicardoPereira]

yes, you are correct. I forgot to remove the 10 value in the Box4.

Should be:

Box1 | Box2 | Box3 | Box4 | Units
=================================
0 0 0 0 225
0 0 5 0 24

 

[vongrunt]

Will this circus do the trick?

-- sample data
create table blah ( Box1 int, Box2 int, Box3 int, Box4 int, Units int )
insert into blah values (10, 2, 0, 0, 12)
insert into blah values ( 0, 0, 0, 0, 225)
insert into blah values ( 0, 3, 0, 0,  6)
insert into blah values ( 0, 0, 5, 0, 24)

-- actual query
select Box1, Box2, Box3, lval as Box4, Units-lval*Box4 as Units from (
select Box1, Box2, lval as Box3, isnull((Units-lval*Box3)/nullif(Box4, 0), 0) as lval, Box4, Units-lval*Box3 as Units from (
select Box1, lval as Box2, isnull((Units-lval*Box2)/nullif(Box3, 0), 0) as lval, Box3, Box4, Units-lval*Box2 as Units from (
select lval as Box1, isnull((Units-lval*Box1)/nullif(Box2, 0), 0) as lval, Box2, Box3, Box4, Units-lval*Box1 as Units from (	
	select isnull(Units/nullif(Box1, 0), 0) as lval, Box1, Box2, Box3, Box4, Units	from Blah
) L1 ) L2 ) L3 ) L4

-- remove sample data
drop table blah

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[RicardoPereira]

It's an amazing query

Thank you for the help. Five starts for you