Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: Perform Delete query with Date logic

    Hi;


    Please find the below requirement ...how to modify the below query

    Code:
    delete  from table2
      where CD_PLT=:WS-PLT
     AND  BASE_NAME=:WS-BASE
     AND  PRE_NAME =:WS-PRE
     AND SUF_NAME =:WS-SUF
    AND YEAR(IN_DT)  < YEAR(CURRENT_DATE)
    Need to check any future date (IN_DT) available for the
    same CD_PLT,BASE_NAME,PRE_NAME and SUF_NAME record
    (If the future IN_DT is not available for that row means..need not delete the row)
    If available means need to delete the old IN_DT row from the Table while the Current date reaches the future IN_DT

    Consider the below two examples
    Code:
    Example1  
    
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  WKLY_CA 	PER_YEAR     IN_DT                     
    ----------- ------------ -------------- ---------- -------    ------------   --------- 
    ABACC            6065        REWS           AE     1000          2011         2011-01-13 
    ABACC            6065        REWS           AE     2450          2011         2011-12-11
    Code:
    Example2
    
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  WKLY_CA 	PER_YEAR     IN_DT                     
    ----------- ------------ -------------- ---------- -------    ------------   --------- 
    BBBBB            6063        REWS           BB     9999          2011         2010-11-11 
    BBBBB            6063        REWS           BB     2350          2011         2011-11-22 
    BBBBB            6063        REWS           BB     2350          2012         2012-02-02
    In the Example 1 ;WKLY_CA value is 1000 on the IN_DT '2011-01-13'(means that, able to collect 1000 from 2011-01-13)
    For the same record,WKLY_CA value is 2450 on the IN_DT '2011-12-11'(means that, able to collect 2450 from 2011-12-11)

    So that the first record will be available in the Table up to 2011-12-10..When the user enter the screen on 2011-12-11
    means the first record needs to be deleted from the Table



    Expected output (on 2011-12-11) Example1

    Code:
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  WKLY_CA 	PER_YEAR     IN_DT                     
    ----------- ------------ -------------- ---------- -------    ------------   --------- 
    ABACC            6065        REWS           AE     2450          2011         2011-12-11
    Expected output (on 2011-11-22) Example2
    Code:
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  WKLY_CA 	PER_YEAR     IN_DT                     
    ----------- ------------ -------------- ---------- -------    ------------   --------- 
    BBBBB            6063        REWS           BB     2350          2011         2011-11-22 
    BBBBB            6063        REWS           BB     2350          2012         2012-02-02
    Expected output (on 2012-02-02) Example2
    Code:
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  WKLY_CA 	PER_YEAR     IN_DT                     
    ----------- ------------ -------------- ---------- -------    ------------   --------- 
    BBBBB            6063        REWS           BB     2350          2012         2012-02-02

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (1) Why did you applied YEAR functions in here?
    AND YEAR(IN_DT) < YEAR(CURRENT_DATE)

    (2)
    Need to check any future date (IN_DT) available for the
    same CD_PLT,BASE_NAME,PRE_NAME and SUF_NAME record
    (If the future IN_DT is not available for that row means..need not delete the row)
    If available means need to delete the old IN_DT row from the Table while the Current date reaches the future IN_DT
    One way may be to add an EXISTS predicate.

  3. #3
    Join Date
    Sep 2011
    Posts
    220
    Thanks for the reply;

    I was trying below one..Is it correct one

    Code:
    delete *
      FROM table2 T1
            WHERE EXISTS
         (SELECT *
    FROM table2 t2
    WHERE 
    
       T2.CD_PLT=T1.CD_PLT
    AND T2.PRE_NAME  = T1.PRE_NAME
    AND T2.BASE_NAME = T1.BASE_NAME
    AND T2.SUF_NAME  = T1.SUF_NAME
    
    AND T2.IN_DT > T1.NI_DT)
    Thanks in advance

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can try yourself.

    By the way, I wrote
    One way may be to add an EXISTS predicate.
    You added an EXISTS predicate and removed some predicates.
    Why did you removed the predicates?

  5. #5
    Join Date
    Sep 2011
    Posts
    220
    Tried more ways..but didnt get..
    Code:
    AND T2.IN_DT > T1.NI_DT
    changed to
    Code:
    AND T2.IN_DT > current_date
    Please help..give some pseudo code..

  6. #6
    Join Date
    Sep 2011
    Posts
    220
    Hi;

    Tried below query
    Code:
    delete *
      FROM table2 T1
            WHERE NOT EXISTS
         (SELECT *
    FROM table2 t2
    WHERE 
    
       T2.CD_PLT=T1.CD_PLT
    AND T2.PRE_NAME  = T1.PRE_NAME
    AND T2.BASE_NAME = T1.BASE_NAME
    AND T2.SUF_NAME  = T1.SUF_NAME
    
    AND T1.IN_DT > CURRENT_DATE)
    Have a look on below records
    Code:
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  WKLY_CA 	PER_YEAR     IN_DT                     
    ----------- ------------ -------------- ---------- -------    ------------   --------- 
    ABACC            6065        REWS           AE     1000          2011         2011-01-13 
    ABACC            6065        REWS           AE     2450          2011         2011-12-11
    BBBBB            6063        REWS           BB     9999          2011         2010-11-11 
    BBBBB            6063        REWS           BB     2350          2011         2011-11-22 
    BBBBB            6063        REWS           BB     2350          2012         2012-02-02 
    CCCCC            9999        EEEE           EE     2300          2011         2011-01-31
    > The last row has to be retained in the table,because it does not have future date


    The above query deleted all rows which are all having less than current date

    Below records only has to be deleted in the table
    Code:
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  WKLY_CA 	PER_YEAR     IN_DT                     
    ----------- ------------ -------------- ---------- -------    ------------   --------- 
    ABACC            6065        REWS           AE     1000          2011         2011-01-13 
    BBBBB            6063        REWS           BB     9999          2011         2010-11-11

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Did you read my previous post?
    By the way, I wrote
    One way may be to add an EXISTS predicate.
    You added an EXISTS predicate and removed some predicates.
    Why did you removed the predicates?
    Please read carefully and understand exactly what I wrote.

    I think that I already gave you enough information/suggestion.

  8. #8
    Join Date
    Sep 2011
    Posts
    220
    Thanks for quick reply

    Why did you removed the predicates?
    Where i removed ?...Give some tips where i need to change the code to get desired result.
    Please help

  9. #9
    Join Date
    Sep 2011
    Posts
    220
    Thanks for the help...I have finished...working fine...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •