Solved

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

Posted on 2013-07-01
5
303 Views
Last Modified: 2013-07-22
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
0
Comment
Question by:cookiejar
  • 2
  • 2
5 Comments
 
LVL 13

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 250 total points
Comment Utility
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
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
Comment Utility
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
 

Author Comment

by:cookiejar
Comment Utility
Alex,

Thank you. This is what I need.
0
 

Author Comment

by:cookiejar
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now