Selecting records for the final date and the date immediately preceding it with a condition

25 pts.
Tags:
Oracle
RapidSQL
SQL
SQL development
Hi, For the following records, there is the tracking for a particular R_ID and F_ID. Col_C = '10' means the final entry in the cycle. ENTRY = '1' means new entry and ENTRY = '2' means modifying existing entry on the same date. ENTRY = '2' means it is the most recent entry
F_ID    R_ID    DATE    ENTRY   Col_A   Col_B   Col_C   Score_1    Score_2  Score_3
85     158   20161005    1       99      99      01        0         0        0
85     158   20161010    1       01      99      99        3         2        2
85     158   20161010    2       01      99      99        3         2        2
85     158   20161012    1       01      01      99        3         2        2
85     158   20161012    2       01      01      99        3         2        2
85     158   20161019    1       99      02      99        3         2        2
85     158   20161022    1       99      99      10        3         2        2
85     158   20161022    2       99      99      10        3         1        2
I need to select such that I get records for:
1. The most recent final entry i.e Col_C = '10' and if Col_C = '10' and ENTRY = '1' or '2' for the same DATE then select the one with ENTRY = '2'
And
2. Records with Col_A or Col_B = '01' and Col_C != '10' where the date just precedes(before) the DATE of final entry. Also if Col_A or Col_B = '01'and if ENTRY = '1' or '2' for the same DATE then select the one with ENTRY = '2'
For results I'm looking for something like:
F_ID   R_ID    DATE    ENTRY   Col_A   Col_B   Col_C   Score_1    Score_2  Score_3
85     158   20161012    2       01      01      99        3         2        2
85     158   20161022    2       99      99      10        3         1        2

Answer Wiki

Thanks. We'll let you know when a new response is added.

Sounds like homework. Post the code you have written and the problem you have and we can help straighten it out. The best way to learn these methods is to try them yourself. 

Use the SQL functions you have learned…
something like this partial piece of code..
/* convert current date for date range extraction */
DECLARE @CurrentDateTime Datetime
set @CurrentDateTime = getdate()
 
DECLARE @CurrYY varchar(6)
set @CurrYY = substring(CONVERT(VARCHAR(6), @CurrentDateTime, 12),1,2)
 
DECLARE @CurrMMDD varchar(6)
set @CurrMMDD = substring(CONVERT(VARCHAR(6), @CurrentDateTime, 12),3,4)
 

/* convert current date to 7 days prior for date range extraction */
DECLARE @StartDateTime Datetime
set @StartDateTime = DATEADD(day,-7, getdate())
 

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Jai427
    I am using the following code but couldnt figure out the preceding date problem

    select * from tbl T where Col_C = '10' and T.date=(select min (T2.date) from tbl T2 where T2.Col_C = '10' and T2.R_ID = T.R_ID
               and T2.F_ID = T.F_ID)
     
    
    union 
    
    
    select * from
    (
    select *
    from tbl k
    where (k.Col_A = '01' or k.Col_B = '01')
      and k.Col_A <> k.Col_B
      and k.Col_C <> '10'
      k.date =
            (select min (k2.date) from tbl k2
               where (k2.Col_A='01' or k2.Col_B='01') and 
    		   k2.Col_A <> k2.Col_B and 
    		   k2.Col_C <> '10' and
               k2.R_ID = k.R_ID
               and k2.F_ID = k.F_ID
            )
    
    
    union 
    
    select *
      from tbl  S
      where S.Col_A = '01' and S.Col_B = '01' and S.Col_C <> '10'
        and S.date =
            (select min (S2.date) from tbl S2
               where S2.Col_A='01' and S2.Col_B='01' and S2.Col_C <> '10' and
               S2.R_ID = S.R_ID
               and S2.F_ID = S.F_ID
            )
    )
    25 pointsBadges:
    report
  • Jai427
    I am unable to pin down the logic for the latest final entry(Col_C = '10' with recent date) and preceding entry(Col_A or Col_B = '01' for a date just preceding final entry)

    I will need 2 records for each F_ID, R_ID. One final entry and one preceding entry

    Thanks.
    25 pointsBadges:
    report
  • carlosdl
    Using analytic functions you can do that without needing to access the table more than once (i.e. without UNIONs and/or subqueries).

    I don't have your tables, so I couldn't test it, but something like this should produce the results you want:

    WITH recent_entries_identified as 
    (
    SELECT y.*,
    ROW_NUMBER() 
      OVER (PARTITION BY f_id,
                          r_id,
                          CASE
                            WHEN col_c='10' THEN 1
                            WHEN col_a = '01' or col_b = '01' THEN 2
                            ELSE 3
                          END
            ORDER BY the_date DESC,entry DESC) as last_entry
    FROM your_table y
    )
    , last_entries_prioritized as 
    (
    select l.*,
    ROW_NUMBER()
      OVER (PARTITION BY f_id,
                          r_id
            ORDER BY
              CASE
                WHEN col_c='10' THEN 'A'
                WHEN col_a = '01' or col_b = '01' THEN 'B'
                ELSE 'C'
              END) as rn
    FROM recent_entries_identified l
      WHERE last_entry = 1
    )
    SELECT f_id,r_id,the_date,entry,col_a,col_b,col_c,score_1,score_2,score_3
      FROM last_entries_prioritized
      WHERE rn <= 2
      ORDER BY the_date;
    84,310 pointsBadges:
    report
  • TheRealRaven
    Is there any chance that the table can be re-designed? The rows that you want to select are so odd that the table should be structured differently. It could be much easier with a design that was more flexible. Other details such as naming a column "DATE" coud be eliminated.

    And can you explain the idea of the result rows? It's not clear what the purpose is. What do the rows represent? What does the date just precedes(before) the DATE of final entry represent? What if there is more than just one for the preceding date?  Can there ever be two rows with ENTRY = '2' on the same DATE? (Why not?)

    What do Col_A, Col_B and Col_C represent?

    Your comment has a WHERE clause that includes and k.Col_A <> k.Col_B but you didn't mention that in the question. Is that important? You also have min (k2.date) but it's not clear why you'd use MIN(). Is that because there can only ever be two dates in a group, or is it because you aren't sure the right way to get the preceding date from a group of dates?

    The question is tagged 'Oracle' and 'RapidSQL'. Can you tell us what versions?
    21,845 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: