SQL Check If Exists Value Less than Min-CodePudding

TABLE1

STUDENT DATE    SUBJECT
------------------------
1   2018-11-13  HISTORY
1   2018-11-14  HISTORY
1   2018-11-15  HISTORY
1   2018-11-15  ART
1   2018-11-12  ENGLISH
1   2018-11-14  ENGLISH
2   2018-11-14  ENGLISH
2   2018-11-13  ENGLISH
2   2018-11-12  ART
3   2018-11-12  HISTORY
3   2018-11-15  ENGLISH
3   2018-11-14  SCIENCE
3   2018-11-14  ART

I have TABLE1 and wish to create TABLE2 which is:

For every STUDENT, find the minimum (DATE) when SUBJECT = HISTORY, then check if there are any values of DATE when SUBJECT != HISTORY that are BETWEEN:

WANTCOL = 1 IF DATE(SUBJECT != HISTORY) < MINIMUM(DATE) WHEN SUBJECT = HISTORY AND DATE(SUBJECT != HISTORY) > DATEADD(MONTHS, -6, MINIMUM(DATE) WHEN SUBJECT = HISTORY) OTHERWISE EQUALS TO 0

TABLE2 should be:

STUDENT WANTCOL
----------------
   1        1
   3        0

Here are some of my attempts

SELECT DISTINCT 
    STUDENT,
    CASE 
        WHEN (DATE < (SELECT MIN(DATE) 
                      FROM TABLE1 
                      WHERE SUBJECT = 'HISTORY' 
                      GROUP BY STUDENT) 
            AND SUBJECT != HISTORY PARITION OVER (STUDENT) 
            AND DATE >= DATEADD(MONTH, -6, SELECT MIN(DATE) FROM TABLE1 WHERE SUBJECT = 'HISTORY' GROUP BY STUDENT) FROM TABLE1 WHERE SUBJECT != HISTORY 
            THEN 1 
            ELSE 0 
    END AS WANTCOL
FROM 
    TABLE1

CodePudding user response：

Based on your partial query and description, does the following work for you?

Pre-aggregate the data and then use a case expression to determine final value:

with d as (
  select student, 
  Min(case when subject = 'HISTORY' then date end) Hist, 
  Min(case when subject != 'HISTORY' then date end) NotHist
  from t
  group by student
)
select Student,
  case when NotHist < Hist and NotHist >= DateAdd(month, -6, Hist) then 1 else 0 end  Wantcol
from d
where Hist is not null;

Demo fiddle