ORACLE 11g - Create two subsets in a query and update with derived calculation

Create a query to
select * from Items table
where name = 'LUMBER' AND LUMBER NUM_OF_EMP <  LUMBER TARGET
AND WHERE NAME = 'HARDWARE' and HARDWARE CODE =  LUMBER CODE
AND HARDWARE NUM_OF_EMP > HARDWARE TARGET

2 ONCE I find subset, find the minimum between  the following differences
DIFF = MIN(LUMBER TARGET MINUS LUMBER NUM_OF-EMP,
HARDWARE TARGET MINUSHARDWARE NUM_OF_EMP)

3 Substract the DIFF from HARDWARE NUM_OF_EMP
Add the DIFF to LUMBER NUM_OF_EMP + DIFF
Update the HARDWARE NUM_EMP = HARDWARE NUM_EMP - DIFF
Update LUMBER NUM_OF_EMP =  LUMBER NUM_OF_EMP + DIFF

ITEMS Table
NAME VARCHAR2(100)
CODE  NUMBER
NUM_OF_EMP NUMBER
TARGET NUMBER

NAME                             CODE             NUM_OF_EMP            TARGET
TOOLS                              70                          10                           20    
HARDWARE                     25                         17                          15
LUMBER                            21                            2                              8
PICTURE HANGERS         40                         16                             14
HARDWARE                     11                          32                              30
PAINT                                 22                         6                                4
TOOLS                               88                         22                              21
LUMBER                           25                          8                               11
LUMBER                          11                          43                              45


OUTPUT
HARDWARE                 25                       15                            15
LUMBER                       25                        10                                11
HARWARE                  11                           31                               30
LUMBER                     11                          44                               44
cookiejarAsked:
Who is Participating?
 
Alexander Eßer [Alex140181]Connect With a Mentor Software DeveloperCommented:
Are you sure, the numbers from your desired output are correct?!

select a.*,
       b.*,
       a.target - a.num_of_emp diff1,
       b.target - b.num_of_emp diff2,
       least(a.target - a.num_of_emp, b.target - b.num_of_emp) diff,
       b.num_of_emp - (least(a.target - a.num_of_emp, b.target - b.num_of_emp)) new_hardware_num_of_emp,
       b.num_of_emp + (least(a.target - a.num_of_emp, b.target - b.num_of_emp)) new_lumber_num_of_emp
  from items b,
       items a
 where a.name = 'LUMBER'
   and a.num_of_emp < a.target
   and b.name = 'HARDWARE'
   and b.code = a.code
   and b.num_of_emp > b.target;

Open in new window

0
 
PortletPaulConnect With a Mentor Commented:
regret to say I had real trouble following your description, but this is what I came up with - which is 'sort of close' I think, I could not match your expected results, but see if this helps.
/*
OUTPUT       expect  achieved
HARDWARE 25 | 15 15 | 20 15
LUMBER   25 | 10 11 |  6 11
HARWARE  11 | 31 30 | 24 30
LUMBER   11 | 44 44 | 41 45
*/

SELECT
      NAME
    , CODE
    , num_of_emp + coalesce(
                             LEAD(ldiff) over (ORDER BY code DESC, name)
                           , LAG(hdiff)  over (ORDER BY code DESC, name)
                           ) 
                 AS new_num_of_emp
    , TARGET
    , NUM_OF_EMP AS old_num_o_emp
FROM (
      SELECT
            NAME
          , CODE
          , NUM_OF_EMP
          , TARGET
          , target - num_of_emp ldiff
          , NULL AS hdiff
      FROM items
      WHERE name = 'LUMBER'
      AND num_of_emp < target
      AND code IN ( SELECT code FROM items WHERE name = 'HARDWARE' )
  
      UNION ALL
  
      SELECT
            NAME
          , CODE
          , NUM_OF_EMP
          , TARGET
          , NULL
          , target - num_of_emp
      FROM items
      WHERE name = 'HARDWARE'
      AND num_of_emp > target
      AND code IN ( SELECT code FROM items WHERE name = 'LUMBER' )
      )
ORDER BY
      CODE DESC
    , NAME
;

Open in new window

can be played with here: http://sqlfiddle.com/#!4/7386c/1
0
 
cookiejarAuthor Commented:
Alex,

Thank you. This is what I need.
0
 
cookiejarAuthor Commented:
Sir:
Will I need to make this a union statement because a  and b items are the same row.
I would a and b to be on individual rows?

select a.*,
       b.*,
       a.target - a.num_of_emp diff1,
       b.target - b.num_of_emp diff2,
       least(a.target - a.num_of_emp, b.target - b.num_of_emp) diff,
       b.num_of_emp - (least(a.target - a.num_of_emp, b.target - b.num_of_emp)) new_hardware_num_of_emp,
       b.num_of_emp + (least(a.target - a.num_of_emp, b.target - b.num_of_emp)) new_lumber_num_of_emp
  from items b,
       items a
 where a.name = 'LUMBER'
   and a.num_of_emp < a.target
   and b.name = 'HARDWARE'
   and b.code = a.code
   and b.num_of_emp > b.target;
0
 
PortletPaulCommented:
>>Will I need to make this a union statement because a  and b items are the same row.
I don't know

I was trying to match the "OUTPUT" you provided in the question
, and in that output they are not on the same row.

If the query by Alex140181 meets your needs then that's great .
0
All Courses

From novice to tech pro — start learning today.