Oracle Cross Join based on first row

Question

I am using Oracle database capturing transactional data.

Table 1: The transaction data of users opening the survey form. A recipient with the email address could potentially open the survey form couple of times but did not fill in and submit. The same survey can be determined by the Survey_No.

INSERT INTO survey_open (SURVEY_NO,EMAIL,DATE) VALUES (1, john@email.com,5/21/2020);
INSERT INTO survey_open (SURVEY_NO,EMAIL,DATE) VALUES (1, john@email.com,5/19/2020);
INSERT INTO survey_open (SURVEY_NO,EMAIL,DATE) VALUES (1, john@email.com,5/10/2020);
INSERT INTO survey_open (SURVEY_NO,EMAIL,DATE) VALUES (1, john@email.com,5/3/2020);
INSERT INTO survey_open (SURVEY_NO,EMAIL,DATE) VALUES (1, john@email.com,5/1/2020);
INSERT INTO survey_open (SURVEY_NO,EMAIL,DATE) VALUES (2, john@email.com,3/1/2020);
INSERT INTO survey_open (SURVEY_NO,EMAIL,DATE) VALUES (2, john@email.com,3/3/2020);
INSERT INTO survey_open (SURVEY_NO,EMAIL,DATE) VALUES (3, john@email.com,2/1/2020);

Table 2: The transaction data of the users submitting the survey. They could resubmit their choices again and again. Again, the survey_no will give info on which survey the response was for.

INSERT INTO survey_fill (SURVEY_NO,EMAIL,DATE) VALUES (1, john@email.com,5/21/2020);
INSERT INTO survey_fill (SURVEY_NO,EMAIL,DATE) VALUES (1, john@email.com,5/15/2020);
INSERT INTO survey_fill (SURVEY_NO,EMAIL,DATE) VALUES (1, john@email.com,5/15/2020);
INSERT INTO survey_fill (SURVEY_NO,EMAIL,DATE) VALUES (1, john@email.com,5/15/2020);
INSERT INTO survey_fill (SURVEY_NO,EMAIL,DATE) VALUES (2, john@email.com,3/15/2020);
INSERT INTO survey_fill (SURVEY_NO,EMAIL,DATE) VALUES (2, john@email.com,3/15/2020);
INSERT INTO survey_fill (SURVEY_NO,EMAIL,DATE) VALUES (2, john@email.com,3/15/2020);
INSERT INTO survey_fill (SURVEY_NO,EMAIL,DATE) VALUES (3, john@email.com,3/1/2020);

The expected output that i am trying to get is to form a table that joins up the above two tables based on the nearest date that makes sense as a proxy to join. Which of the transaction on the survey_open table get tagged with a filled_date is not that important. Since the survey_id and email is many to many, i do not want to form a cross join.

Please **delete** all these image links, and instead include your sample data as _text_. — Tim Biegeleisen, May 21 '20 at 06:38
This seems to be trivial to do with a subquery to find the highest fill date for an survey_no with the date less or equal than open date. However, why do you have NULL for the entry with open date of 19 May? — micklesh, May 21 '20 at 07:40
@micklesh i am ok if i were to stamp the 21-May survey filled date with the record on 21-May or 19-May (Meaning to say 21-May will be NULL entry for click_date). Since in a report, i will just need to get the numbers. — user-DY, May 21 '20 at 07:59
right, I was a bit wrong to say this is simple subquery, need to think a bit on what is there, in the meantime [here's the dbfiddle with sample data](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=719f37e957b295df756d3426a0ac051f) if anyone would like to play with that too — micklesh, May 21 '20 at 08:27
I think this requires a recursive CTE. The rules for this type of thing are quite complicated. Why would you want multiple rows for one user/survey anyway? — Gordon Linoff, May 21 '20 at 12:32
Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. — philipxy, May 22 '20 at 04:07
Please don't ask us to write your code. Show what you are able to do. Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] — philipxy, May 22 '20 at 04:10

score 1 · Accepted Answer · answered May 21 '20 at 09:48

Try to number items in both tables

with o as(
  select so.*, row_number() over(partition by survey_no order by open_date) rn
  from survey_open so
), f as (
  select sf.*, row_number() over(partition by survey_no order by fill_date) rn
  from survey_fill sf
)
select o.survey_no
     , o.email
     , o.open_date
     , (select min(f.fill_date) 
         from f 
         where f.survey_no = o.survey_no 
           and f.fill_date >= o.open_date
           and f.rn >= o.rn) as fill_date
  from o 
order by o.survey_no, o.rn desc

Oracle Cross Join based on first row

1 Answers1