Home » SQL & PL/SQL » SQL & PL/SQL » Logic to create new column in Oracle database - Multiple conditions making it more challenging
| Logic to create new column in Oracle database - Multiple conditions making it more challenging [message #631648] |
Fri, 16 January 2015 12:15  |
 |
tableaudev2010
Messages: 6
Registered: January 2015
Location: United States
|
Junior Member |
|
|
Hello Geniuses,
I am a Tableau developer. Currently i am using an Oracle database as my data source. I have come up with a new requirement that is testing me to the core, lot of online tableau users suggested to accomplish the requirement in Oracle database itself rather than on Tableau, since it is easier it in Oracle as its little difficult in Tableau. I request you to go through my requirement and help me to fulfill the client requirement.
Background: The following are the columns with their data types needed for this requirement.
Column Name Data Type
year (Date) Number
quarter (Date) Number
Delivery String
Cost String
SMS String
Quality String
Overall MI Score String
Factory Code String
Delivery(D), Cost(C), SMS(S), Quality(Q) and Over all mi score(M) have numeric values ranging from 0 - 100, while year has values like 2012,2013 and 2014, quarter has values like 1,2,3 and 4.
Requirement:
I have to create a new Column called "Sanctions" and they should have values Level-1, Level-2, Level-3 and None. This should depend on ranges of 5 different columns Delivery(D), Cost(C), SMS(S), Quality(Q) and Over all mi score(M) for quarterly periods. The ranges are: if measure value is between 0 - 60 then red, 60 - 70 then yellow etc. I am trying to figure out how to write the code for below condition.
Condition -1:
If [M] >0 and [M] <60 for 4 to 7 consecutive quarters then "Level-1" OR If [M] is yellow or red for 4 to 7 quarters then "Level-1" OR If [D] is yellow or red for 4 or more consecutive quarters then "Level-1" OR If [C] is yellow or red for 4 or more consecutive quarters then "Level-1" OR If [S] is yellow for 4 or more quarters then "Level-1" OR If [S] is red or yellow for 4 or more quarters then "Level-1" OR If [Q] is yellow for 4 or more quarters then "Level-1" OR If [Q] is red or yellow for 4 or more quarters then "Level-1" but If [M] or [S] has two consecutive reds in quarters then "Level-2"
Condition -2:
If [M] is yellow or red for 8 to 11 consecutive quarters then "Level-2" OR If [M] is yellow for 8 to 11 consecutive quarters then "Level-2" OR If [M] is red for 2 to 7 consecutive quarters then "Level-2" OR If Dimension [ZT] is set to yes for at least one quarter then "Level-2" for that quarter OR If [S] is red for 3 to 7 consecutive quarters then "Level-2"
Condition -3:
If [M] is red or yellow for 12 or more consecutive quarters then "Level-3" OR If [M] is yellow for more than 11 consecutive quarters then "Level-3" OR If [M] is red for 8 or more consecutive quarters then "Level-3" OR If [s] is red for 8 or more consecutive quarters the "Level-3"
Finally the new column should have values Level 1 - 3 and None for all factory Codes for all quarterly periods for any year.
Example: Finally it should look like this when sorted
Factory Code 2012 Q1 2012 Q2 2012 Q3 2012 Q4 2013 Q1 2013 Q2 2013 Q3 2013 Q4 2014 Q1 2014 Q2 2014 Q3 2014 Q4
ABC Level-1 Level-1 Level-1 Level-1 Level-2
ANV Level-2 (Two consecutive reds)
FTY Level-1 Level-1 Level-1 Level-1 Level-2 Level-2 Level-2 Level-2 Level-3
I know there are many conditions involved in this, but i see this is the only way we can create the new column.
Thanks
Raj
[mod-edit: code tags added by bb]
[Updated on: Fri, 16 January 2015 14:58] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Logic to create new column in Oracle database - Multiple conditions making it more challenging [message #631667 is a reply to message #631653] |
Fri, 16 January 2015 14:37  |
|
tableaudev2010
Messages: 6
Registered: January 2015
Location: United States
|
Junior Member |
|
|
Hello,
I am updating the post with more information on the requirement, conditions and Sample data. Since i am new to this forum and Sql development, i request you to bear with me in understanding the requirement and help me to come up with the script.
Please find the data from my source table attached (export.csv), it has all the columns that are needed to create the new column
'Sanctions'. The following columns have to be considered for creating the new column.
Delivery (D), Cost (C), SMS (S), Quality (Q), Overall MI Adjusted Score (M), ZT, PS, Year and Quarter.
Delivery, Cost, SMS, Quality and Overall MI Adjusted Score have values ranging from 0-100. While ZT and PS columns have
value 1. And year and Quarter columns have numeric values.
The new Column 'Sanctions' should have 4 values. Level-1, Level-2, Level-3 and blank.
Now lets discuss the various conditions that have to be considered for creating the column. Each level have a separate condition. These conditions apply for every factory code for every quarter under each year.
Level-1 condition:
If Overall MI Adjusted Score (M) > 59 and Overall MI Adjusted Score (M) < 70 for 4 to 7 consecutive quarters then it should fall under Level-1
OR
If Overall MI Adjusted Score (M) > 59 and Overall MI Adjusted Score (M) < 70 or Overall MI Adjusted Score (M) > 0 and Overall MI Adjusted Score (M) < 60 for 4 to 7 consecutive quarters then it should fall under Level-1 (Combination of both ranges consecutively, but if there are consecutive quarters with (M) > 0 and < 60 then it is Level-2 by default)
OR
If Delivery (D) > 59 and Delivery (D) < 70 for 4 or more quarters then it should fall under Level-1
OR
If Delivery (D) > 59 and Delivery (D) < 70 or Delivery (D) > 0 and Delivery (D) < 60 for 4 or more consecutive quarters then it should fall under Level-1
OR
If Cost(C) > 59 and Cost(C) < 70 for 4 or more consecutive quarters then it should fall under Level-1
OR
If Cost(C) > 59 and Cost(C) < 70 or Cost(C) > 0 and Cost(C) < 60 for 4 or more consecutive quarters then it should fall under Level-1
Level-2 condition:
If Overall MI Adjusted Score (M) > 59 and Overall MI Adjusted Score (M) < 70 for 8 to 11 consecutive quarters then it should fall under Level-2
OR
If Overall MI Adjusted Score (M) > 59 and Overall MI Adjusted Score (M) < 70 or Overall MI Adjusted Score (M) > 0 and Overall MI Adjusted Score (M) < 60 for 8 to 11 consecutive quarters then it should fall under Level-2
OR
If Overall MI Adjusted Score (M) > 0 and Overall MI Adjusted Score (M) < 60 for 8 to 11 consecutive quarters then it should fall under Level-2
OR
If a factory code is set to value 1 for column ZT or PS for that quarter then it falls under Level-2 irrespective of any conditions.
OR
If SMS (S) > 0 and SMS (S) < 60 for 3 to 7 consecutive quarters then it should fall under Level-2
Level-3 condition
If Overall MI Adjusted Score (M) > 59 and Overall MI Adjusted Score (M) < 70 for more than 11 consecutive quarters then it should fall under Level-3
OR
If Overall MI Adjusted Score (M) > 59 and Overall MI Adjusted Score (M) < 70 or Overall MI Adjusted Score (M) > 0 and Overall MI Adjusted Score (M) < 60 for 12 or more consecutive quarters then it should fall under Level-3
OR
If Overall MI Adjusted Score (M) > 0 and Overall MI Adjusted Score (M) < 60 for 8 or more consecutive quarters then it should fall under Level-3
OR
If SMS (S) > 0 and SMS (S) < 60 for 8 or more consecutive quarters then it should fall under Level-3
These are the conditions for factory codes to fall under Levels 1,2 and 3.
Using all the above conditions, logic and data, i have to come up with a script to create new column in the table.
I am also attaching my required output should display like this (sample.csv).Please let me know if you need more information.
Thanks
Raj
[Edit: Attachment removed at OP's request]
[Updated on: Sun, 08 February 2015 00:59] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Logic to create new column in Oracle database - Multiple conditions making it more challenging [message #631670 is a reply to message #631668] |
Fri, 16 January 2015 16:08 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your files appear to be Excel files. We need simple ASCII text files. Better yet, just post the code in the post instead of attaching a file. I have provided a simplified partial example below to get you started. The first section is a create table and insert statements like we need from you. The second section displays some data from that. The subsequent sections show how to use LAG to get the values for the prior quarters and CASE to apply conditions and finally how to pivot the results. Each query is used as an inline view (sub-query in the from clause) in the next query. The final query is all that is needed, but I have displayed the results of the inner sub-queries for better understanding. This example assumes that a value of overall_mi_score between 1 and 60 for 4 or more consecutive quarters should have a value of "Level-1" for the derived sanction column. This is just a simple example. Obviously you will need to have more conditions in your case statement. You can find syntax and example of the case statement in the online documentation. If you have troubles, then please post your create table and insert statements, what you have tried, what results you got, and what results you want instead.
-- create table and insert statements like we need from you:
SCOTT@orcl12c> CREATE TABLE test_tab
2 (year NUMBER,
3 quarter NUMBER,
4 delivery NUMBER,
5 cost NUMBER,
6 sms NUMBER,
7 quality NUMBER,
8 overall_mi_score NUMBER,
9 factory_code VARCHAR2(12))
10 /
Table created.
SCOTT@orcl12c> INSERT ALL
2 INTO test_tab VALUES (2012, 1, 10, 20, 30, 40, 10, 'ABC')
3 INTO test_tab VALUES (2012, 2, 10, 20, 30, 40, 20, 'ABC')
4 INTO test_tab VALUES (2012, 3, 10, 20, 30, 40, 30, 'ABC')
5 INTO test_tab VALUES (2012, 4, 10, 20, 30, 40, 40, 'ABC')
6 INTO test_tab VALUES (2013, 1, 10, 20, 30, 40, 50, 'ABC')
7 INTO test_tab VALUES (2013, 2, 10, 20, 30, 40, 55, 'ABC')
8 INTO test_tab VALUES (2013, 3, 10, 20, 30, 40, 60, 'ABC')
9 INTO test_tab VALUES (2013, 4, 10, 20, 30, 40, 51, 'ABC')
10 INTO test_tab VALUES (2014, 1, 10, 20, 30, 40, 41, 'ABC')
11 INTO test_tab VALUES (2014, 2, 10, 20, 30, 40, 70, 'ABC')
12 INTO test_tab VALUES (2014, 3, 10, 20, 30, 40, 80, 'ABC')
13 INTO test_tab VALUES (2014, 4, 10, 20, 30, 40, 90, 'ABC')
14 SELECT * FROM DUAL
15 /
12 rows created.
-- If you have data like this:
SCOTT@orcl12c> SELECT factory_code, year, quarter, overall_mi_score m
2 FROM test_tab
3 ORDER BY factory_code, year, quarter
4 /
FACTORY_CODE YEAR QUARTER M
------------ ---------- ---------- ----------
ABC 2012 1 10
ABC 2012 2 20
ABC 2012 3 30
ABC 2012 4 40
ABC 2013 1 50
ABC 2013 2 55
ABC 2013 3 60
ABC 2013 4 51
ABC 2014 1 41
ABC 2014 2 70
ABC 2014 3 80
ABC 2014 4 90
12 rows selected.
-- then you can obtain the prior quarter values using LAG like this:
SCOTT@orcl12c> SELECT factory_code, year, quarter, overall_mi_score m,
2 LAG(overall_mi_score,1) OVER (PARTITION BY factory_code ORDER BY year, quarter) lag1,
3 LAG(overall_mi_score,2) OVER (PARTITION BY factory_code ORDER BY year, quarter) lag2,
4 LAG(overall_mi_score,3) OVER (PARTITION BY factory_code ORDER BY year, quarter) lag3
5 FROM test_tab
6 ORDER BY factory_code, year, quarter
7 /
FACTORY_CODE YEAR QUARTER M LAG1 LAG2 LAG3
------------ ---------- ---------- ---------- ---------- ---------- ----------
ABC 2012 1 10
ABC 2012 2 20 10
ABC 2012 3 30 20 10
ABC 2012 4 40 30 20 10
ABC 2013 1 50 40 30 20
ABC 2013 2 55 50 40 30
ABC 2013 3 60 55 50 40
ABC 2013 4 51 60 55 50
ABC 2014 1 41 51 60 55
ABC 2014 2 70 41 51 60
ABC 2014 3 80 70 41 51
ABC 2014 4 90 80 70 41
12 rows selected.
-- and you can figure the sanction using CASE like this:
SCOTT@orcl12c> COLUMN sanction FORMAT A8
SCOTT@orcl12c> SELECT factory_code, year, quarter, overall_mi_score m,
2 CASE WHEN overall_mi_score BETWEEN 1 AND 60 AND
3 lag1 BETWEEN 1 AND 60 AND
4 lag2 BETWEEN 1 AND 60 AND
5 lag3 BETWEEN 1 AND 60
6 THEN 'Level-1'
7 END AS sanction
8 FROM (SELECT t.*,
9 LAG(overall_mi_score,1) OVER (PARTITION BY factory_code ORDER BY year, quarter) lag1,
10 LAG(overall_mi_score,2) OVER (PARTITION BY factory_code ORDER BY year, quarter) lag2,
11 LAG(overall_mi_score,3) OVER (PARTITION BY factory_code ORDER BY year, quarter) lag3
12 FROM test_tab t)
13 ORDER BY factory_code, year, quarter
14 /
FACTORY_CODE YEAR QUARTER M SANCTION
------------ ---------- ---------- ---------- --------
ABC 2012 1 10
ABC 2012 2 20
ABC 2012 3 30
ABC 2012 4 40 Level-1
ABC 2013 1 50 Level-1
ABC 2013 2 55 Level-1
ABC 2013 3 60 Level-1
ABC 2013 4 51 Level-1
ABC 2014 1 41 Level-1
ABC 2014 2 70
ABC 2014 3 80
ABC 2014 4 90
12 rows selected.
-- and you can pivot the results like this:
SCOTT@orcl12c> SELECT factory_code,
2 MAX (CASE WHEN year = 2012 and QUARTER = 1 THEN sanction END) "2012 Q1",
3 MAX (CASE WHEN year = 2012 and QUARTER = 2 THEN sanction END) "2012 Q2",
4 MAX (CASE WHEN year = 2012 and QUARTER = 3 THEN sanction END) "2012 Q3",
5 MAX (CASE WHEN year = 2012 and QUARTER = 4 THEN sanction END) "2012 Q4",
6 MAX (CASE WHEN year = 2013 and QUARTER = 1 THEN sanction END) "2013 Q1",
7 MAX (CASE WHEN year = 2013 and QUARTER = 2 THEN sanction END) "2013 Q2",
8 MAX (CASE WHEN year = 2013 and QUARTER = 3 THEN sanction END) "2013 Q3",
9 MAX (CASE WHEN year = 2013 and QUARTER = 4 THEN sanction END) "2013 Q4",
10 MAX (CASE WHEN year = 2014 and QUARTER = 1 THEN sanction END) "2014 Q1",
11 MAX (CASE WHEN year = 2014 and QUARTER = 2 THEN sanction END) "2014 Q2",
12 MAX (CASE WHEN year = 2014 and QUARTER = 3 THEN sanction END) "2014 Q3",
13 MAX (CASE WHEN year = 2014 and QUARTER = 4 THEN sanction END) "2014 Q4"
14 FROM (SELECT factory_code, year, quarter, overall_mi_score m,
15 CASE WHEN overall_mi_score BETWEEN 1 AND 60 AND
16 lag1 BETWEEN 1 AND 60 AND
17 lag2 BETWEEN 1 AND 60 AND
18 lag3 BETWEEN 1 AND 60
19 THEN 'Level-1'
20 END AS sanction
21 FROM (SELECT t.*,
22 LAG(overall_mi_score,1) OVER (PARTITION BY factory_code ORDER BY year, quarter) lag1,
23 LAG(overall_mi_score,2) OVER (PARTITION BY factory_code ORDER BY year, quarter) lag2,
24 LAG(overall_mi_score,3) OVER (PARTITION BY factory_code ORDER BY year, quarter) lag3
25 FROM test_tab t))
26 GROUP BY factory_code
27 ORDER BY factory_code
28 /
FACTORY_CODE 2012 Q1 2012 Q2 2012 Q3 2012 Q4 2013 Q1 2013 Q2 2013 Q3 2013 Q4 2014 Q1 2014 Q2 2014 Q3 2014 Q4
------------ ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- -------
ABC Level-1 Level-1 Level-1 Level-1 Level-1 Level-1
1 row selected.
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Aug 28 02:09:04 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
