Getting records for every year

0 votes
asked Apr 16, 2014 by krishna

I have two tables named Forms & Attachment.

Forms Table
User_Id (PK) | User_name | Submitted_date
Attachment table
User_id (FK) | Attachment_name | Submitted_date.

The Forms can have multiple attachments.

I want write query which will fetch Forms records which do not have attachments for every year since Forms is submitted. There must be attachment with form for every year since is submitted. I want find records which do not fall in this condition.

2 Answers

0 votes
answered Apr 16, 2014 by ryx5

Try this :

SELECT f.*
FROM Forms f left join Attachement a on f.User_id = a.User_id and TO_CHAR(f.Submitted_date,'YYYY') = TO_CHAR(a.Submitted_date,'YYYY')
WHERE a.Attachement_name IS NULL

This will try to join attachement with forms, so if there is no attachement it returns NULL, and then we retrieve only NULL joined attachement.

0 votes
answered Nov 29, 2018 by teagles

The only way I can think to do this is to populate an ETL style table with PLSQL prior to running the query:

create table form_years (user_id number(19), year varchar2(4));
DECLARE
BEGIN execute immediate 'TRUNCATE TABLE form_years'; FOR cur_form IN ( SELECT User_Id, TO_CHAR(submitted_date, 'yyyy') AS YEAR FROM Forms ) LOOP INSERT INTO form_years SELECT cur_form.User_Id, TO_CHAR(add_months(to_date('01-01-' || cur_form.year,'mm-dd-yyyy'), (rownum -1)*12), 'yyyy') FROM all_objects WHERE rownum <= (months_between(to_date('01-01-' || TO_CHAR(add_months(sysdate, 12), 'yyyy'),'dd-mm-yyyy'),to_date('01-01-' || cur_form.year,'mm-dd-yyyy')))/12; COMMIT; END LOOP;
END;
/

Then query with:

SELECT user_id
FROM Forms f
WHERE EXISTS (SELECT 0 FROM form_years LEFT OUTER JOIN Attachment ON Attachment.user_id = form_years.user_id AND form_years.year = TO_CHAR(Attachment.Submitted_date, 'yyyy') WHERE Attachment.user_id IS NULL AND f.user_id = form_years.user_id );
Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
...