Search code examples
sqlgroup-bysubqueryconditional-statements

Subqueries Condition (SQL)

So i want to create a view using the below script the problem is i want to show the view for 2020 grouped by quarter as you can see i have 2 subqueries using to_char(ACTION_DATE,'YYYY') = 2020 I want to return quarter results ONLY if FileNO's in both subqueries are from the Same Quarter.

    SELECT Quarter,
    count(FileNO), avg(total_time),
    sum( case when Total_Time <= :T Then 1 Else 0 End ) Achieved,
    sum( case when Total_Time <= :T Then 1 Else 0 End ) / Count(FileNO) * 100 Precentage_Acheived
from 
 (SELECT to_Char(ACTION_DATE,'YYYYQ') as Quarter, FileNO, SUM(work_time) as total_time
    FROM F.MV
   WHERE 
   (FileNO, APP_no) IN
                 (SELECT FileNO, APP_no
                    FROM F.MV
                   WHERE    to_char(ACTION_DATE,'YYYY') = 2020
                            AND TASK_NAME = 'Lifting'
                            AND TO_TASK_NAME = 'Finish')
        AND DEPT_NAME = 'TempDep'
        AND WF_TASK_NAME = 'Lifting'
        and to_char(ACTION_DATE,'YYYY') = 2020
GROUP BY to_Char(ACTION_DATE,'YYYYq'),FileNO
)
GROUP BY Quarter

Correct if i use to_char(ACTION_DATE,'YYYYQ') = 20201 for both subquieres enter image description here

but result iam getting with to_char(ACTION_DATE,'YYYY') = 2020 is: enter image description here

Thanks

Solution

  • I think the innermost correlation clause you want is:

    (FileNO, APP_no) IN
             (SELECT mv2.FileNO, mv2.APP_no
              FROM F.MV mv2
              WHERE to_char(mv2.ACTION_DATE, 'YYYYQ') = to_char(mv.ACTION_DATE, 'YYYYQ')