CODEWITHSUNDEEP
sqloracle-databaseplsql
Doc Holiday
Doc Holiday

Reputation: 10254

Quering dates back 2 years

I need to add a couple and statements to my WHERE clause.

I need to get data from today back 2 years. I'm not sure how to write that in SQL. I got every thing else though, so it's at the end of the code:

FUNCTION summarize_item_search_data (p_obj_code IN VARCHAR2,  p_val2 IN VARCHAR2,
                                                               p_sac IN NUMBER, p_job_type_id   IN NUMBER,
                                                               p_sup IN NUMBER)
  RETURN sys_refcursor
  IS
  stmt     VARCHAR2(4000);
  result_cur   sys_refcursor;  
 BEGIN

    OPEN result_cur FOR

    SELECT DISTINCT jp.id, jp.row_top.mwslin AS mwslin, jp.obj_code, jp.jobload_year, jp.row_top.fiscal_year AS fiscal_year,
                                      val1s.sac, val2s.val2, val1s.val1,
         DECODE( jp.row_top.val1_id, NULL, jp.row_top.nomenclature ,val1s.nomenclature) AS nomenclature, jp.row_top.sup AS sup
        FROM schedules sch, job_plans JP, master_val1 val1s, master_val2 val2s, TABLE(val2s.group_id) (+) ntab, 
                 groups pgds
  WHERE 
-- stmt := stmt || ' AND ''' || p_year || ''' = ntab.fiscal_year(+)';
    (val1s.sac = p_sac OR p_sac IS NULL) AND

    (UPPER(val2s.val2) LIKE UPPER(p_val2) OR p_val2 IS NULL) AND

    (UPPER(jp.obj_code) LIKE UPPER(p_obj_code) OR p_obj_code IS NULL) AND

    (jp.row_top.sup <= p_sup OR p_sup IS NULL) AND

    (jp.row_top.job_type_id = p_job_type_id OR p_job_type_id IS NULL) 

    AND ntab.group_id = pgds.id(+) 
    AND jp.row_top.val1_id = val1s.id(+) 
    AND val1s.val2_id = val2s.id(+)
    AND  jp.jobload_year > 
    AND  fiscal_year > 


  RETURN result_cur;
  END summarize_item_search_data;

Upvotes: 0

Views: 119

Answers (2)

Nick Rolando
Nick Rolando

Reputation: 26177

AND  jp.jobload_year > ADD_MONTHS(CURRENT_DATE, -24)
AND  fiscal_year > ADD_MONTHS(CURRENT_DATE, -24)

I see those fields aren't datetime, so that won't work. You would need to convert them to datetime, or convert to integer and compare by year alone. Going with the first option, you could try:

AND  TO_DATE('1-1-'||jp.jobload_year) > ADD_MONTHS(CURRENT_DATE, -24)
AND  TO_DATE('1-1-'||fiscal_year) > ADD_MONTHS(CURRENT_DATE, -24)

Although it's not very dynamic. Could wait for a better solution.

Upvotes: 2

Allan
Allan

Reputation: 17429

@Shredder's solution is perfectly functional, but it isn't sargable. Even if there is no applicable index today, designing queries so that they can take advantage of indexes that may be created in the future is a good practice (assuming that it doesn't add significant complexity):

AND jp.jobload_year > TO_CHAR(ADD_MONTHS(CURRENT_DATE, -24),'YYYY')
AND fiscal_year > TO_CHAR(ADD_MONTHS(CURRENT_DATE, -24),'YYYY')

Upvotes: 1

Related Questions