CODEWITHSUNDEEP
sqloracle-database
DB08
DB08

Reputation: 181

Value need from range, based on Priority in SQL ,

There is 3 cases: (In all cases value needs to be picked up based on priority)

Table
Temp1

state zip_start zip_end Priority Value
NY 100 200 1 A
NY 150 250 3 c
NY null null 2 B

Data

state zip
NY 201
NY 400

OUTPUT :

state zip_start zip_end Priority Value zip
NY null null 2 B 201
NY null null 2 B 400

I am trying with below code , but It's not picking the data based on priority:

SELECT ZIP,ZIP_START,ZIP_END,VALUE,PRIORITY,STATE,IX FROM 
(
    SELECT TMP1.*,
    ROW_NUMBER () OVER (PARTITION  BY STATE,ZIP ORDER BY PRIORITY ) IX

    FROM 

        (
            WITH CASE_1 AS 
                (   SELECT 
                    temp1.*
                    ,DATA.ZIP 
                    FROM TEMP1 
                    LEFT JOIN
                    "DATA" ON DATA.STATE  = temp1.STATE 
                    WHERE DATA.ZIP BETWEEN TEMP1.ZIP_START AND TEMP1 .ZIP_END
                ),
                CASE_2 AS
                (
                    SELECT 
                    temp1.*
                    ,DATA.ZIP 
                    FROM "DATA" 
                    LEFT JOIN
                    TEMP1 ON DATA.STATE  = temp1.STATE 
                    WHERE  (ZIP_START IS NULL OR ZIP_START = '')
                    AND (ZIP_END IS NULL OR ZIP_END = '')
                    AND Not EXISTS 
                    (SELECT 1 FROM CASE_1 WHERE CASE_1.zip=DATA.zip
                    AND CASE_1.STATE=DATA.STATE)
                )
            SELECT * FROM CASE_1
            UNION
            SELECT * FROM CASE_2
        )TMP1
)   TMP2
WHERE TMP2.IX = 1;

Upvotes: 0

Views: 75

Answers (2)

CarlosSR
CarlosSR

Reputation: 1195

CREATE TABLE TEMP1 (
  STATE VARCHAR(10),
  ZIP_START NUMBER,
  ZIP_END NUMBER,
  PRIORITY NUMBER,
  VAL VARCHAR(10));


INSERT INTO TEMP1 VALUES ('NY', 100,200,1,'A');
INSERT INTO TEMP1 VALUES ('NY', 150,250,3,'C');
INSERT INTO TEMP1 VALUES ('NY', null,null,2,'B');


CREATE TABLE DATATABLE (
  STATE VARCHAR(10),
  ZIP NUMBER
 );

INSERT INTO DATATABLE VALUES ('NY', 201);
INSERT INTO DATATABLE VALUES ('NY', 400);

The main idea is to figure out first how many times your condition (zip in range between zip_start and end) is met. This is why we use count_match variable. Once you get if your data is priority 1,2 or 3, you match your data table with the temp table to get the value associated with that priority.

SELECT
t0.STATE,
t0.ZIP_START,
t0.ZIP_END,
CASE WHEN t0.COUNT_MATCH > 1 THEN 3
    WHEN t0.COUNT_MATCH = 1 THEN 1
    WHEN t0.COUNT_MATCH = 0 THEN 2 END AS PRIORITY,
t.VAL,
t0.ZIP
FROM
(
SELECT
  t1.STATE,
  MIN(t2.ZIP_START) AS ZIP_START,
  MAX(t2.ZIP_END) AS ZIP_END,
  COUNT(t2.STATE) AS COUNT_MATCH,
  t1.ZIP
FROM DATATABLE t1
  LEFT JOIN TEMP1 t2 ON (t1.STATE = t2.STATE AND t1.ZIP>=t2.ZIP_START AND t1.ZIP <= t2.ZIP_END)
GROUP BY
  t1.STATE, t1.ZIP) t0
LEFT JOIN TEMP1 t ON (t0.STATE = t.STATE AND CASE WHEN t0.COUNT_MATCH > 1 THEN 3
    WHEN t0.COUNT_MATCH = 1 THEN 1
    WHEN t0.COUNT_MATCH = 0 THEN 2 END = t.PRIORITY)
;

Upvotes: 0

MT0
MT0

Reputation: 168470

From Oracle 12, you can use a LATERAL join and filter when the zip is within range or when one-or-other end of the range is NULL the ORDER BY priority and FETCH the FIRST matched ROW ONLY:

SELECT t.*, d.zip
FROM   data d
       CROSS JOIN LATERAL (
         SELECT *
         FROM   temp1 t
         WHERE  d.state = t.state
         AND    (t.zip_start <= d.zip OR t.zip_start IS NULL)
         AND    (t.zip_end   >= d.zip OR t.zip_end   IS NULL)
         ORDER BY priority
         FETCH FIRST ROW ONLY
       ) t

In earlier versions, you can join the two tables and then use the ROW_NUMBER analytic function to find the best match:

SELECT state, zip_start, zip_end, priority, value, zip
FROM   (
  SELECT t.*,
         d.zip,
         ROW_NUMBER() OVER (PARTITION BY d.ROWID ORDER BY t.priority) AS rn
  FROM   data d
         INNER JOIN temp1 t
         ON  (   d.state = t.state
             AND (t.zip_start <= d.zip OR t.zip_start IS NULL)
             AND (t.zip_end   >= d.zip OR t.zip_end   IS NULL))
)
WHERE  rn = 1;

Which, for the sample data:

CREATE TABLE Temp1 (state, zip_start, zip_end, Priority, Value) AS
SELECT 'NY', 100,  200,  1, 'A' FROM DUAL UNION ALL
SELECT 'NY', 150,  250,  3, 'c' FROM DUAL UNION ALL
SELECT 'NY', null, null, 2, 'B' FROM DUAL;

CREATE TABLE Data (state, zip) AS
SELECT 'NY', 201 FROM DUAL UNION ALL
SELECT 'NY', 400 FROM DUAL;

Both output:

STATE ZIP_START ZIP_END PRIORITY VALUE ZIP
NY null null 2 B 201
NY null null 2 B 400

db<>fiddle here

Upvotes: 0

Related Questions