Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Help with the qry

Posted on 2012-09-12
8
Medium Priority
?
398 Views
Last Modified: 2012-09-15
I have table as follows

ID      FLG      DT_ADDED                        ACT_TYPE
1      761      9/6/2012 7:17:19 PM      
1      421      9/6/2012 7:19:12 PM      
1      4      9/7/2012 7:19:48 PM      
1      555      9/12/2012 10:38:07 PM      
2      761      9/4/2012 7:23:20 PM      
2      4      9/5/2012 7:23:30 PM         18
2      421      9/5/2012 7:23:37 PM      
3      4      9/12/2012 10:46:13 PM      
3      761      9/12/2012 10:46:33 PM      18      
3      421      9/12/2012 10:46:50 PM         
4      4      9/12/2012 11:04:53 PM      
4      761      9/12/2012 11:05:05 PM      
4      421      9/12/2012 11:05:26 PM      



My selection criteria should be as follows:

1. if the lastest flag among 4,421 and 761( do not consider any other than these), then give me the id marking as 1
2. if act_type is 18, then latest among 4,421 and 761 is NOT 421 then give me the id marking as 1
3. If the above condition fails give me the id with 0

so my out put should be as follows:

id     status
1      1             --this is one because the latest among 4,421,761 is one of 4,761)
2      1             --this is one because it has act_type of 1 and latest among 4,421,761 <> 421)
3      0             --this is 0 because it has bus_act =18 but latest among 4,421,761 is 421
4      0             --this is 0 because the latest among 4,421,761 is NOT one of (4,761)

Please let me know how we can do this..

Thanks
0
Comment
Question by:gs79
  • 5
  • 3
8 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 38393273
You rule 1 and rule 2 contradict each other slightly.  I have amended rule 1 to be only when act_type is not 18.

Try
SELECT id,
       case when is18 = 0 and FLG in (4,421,761) then 1 
            when is18 = 1 and FLG in (4,761)     then 1
            else 0 end as status
  FROM (SELECT ID, FLG, DT_ADDED, ACT_TYPE
             , row_number()over(partition by id order by DT_ADDED desc) as rn
             , max(decode(ACT_TYPE,18,1,0))over(partition by id) as is18
          FROM your_table)v
 WHERE rn = 1

Open in new window

0
 

Author Comment

by:gs79
ID: 38393308
it does nt contradict, if any id as 18 as bus_act then apply rule 2 else apply rule 1

Thanks
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38393315
If you apply rule 1 before rule 2 (i.e. in order) you never look at 18.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 

Author Comment

by:gs79
ID: 38393320
thats what i wanted , apply rule 1 only when bus act is not 18..spot on!

let me verify the query and get back to you
0
 

Author Comment

by:gs79
ID: 38393387
your query is giving me following which is not as expected
ID      STATUS
1      0
2      0
3      0
4      1

thanks
0
 

Author Comment

by:gs79
ID: 38393415
i think i made a mistake in my requirement here is the corrected one

1. if the lastest flag among 4, 421 and 761( do not consider any other than these), is one of (4,421)  then give me the id marking as 1
2. if act_type is 18, then latest among 4,421 and 761 is NOT 421 then give me the id marking as 1
3. If the above condition fails give me the id with 0
0
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 38393448
SELECT id,
       case when id18 = 0 and FLG in (4,421) then 1 
            when id18 = 1 and FLG in (4,761) then 1
            else 0 end as status
  FROM (SELECT ID, FLG, DT_ADDED, ACT_TYPE
             , row_number()over(partition by id 
                                order by CASE WHEN FLG IN (4,421,761) THEN DT_ADDED
                                              ELSE null END desc NULLS LAST) as rn
             , max(decode(ACT_TYPE,18,1,0))over(partition by id) as id18
          FROM your_table)v
 WHERE rn = 1

Open in new window

0
 

Author Closing Comment

by:gs79
ID: 38401918
Worked like a charm!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses
Course of the Month21 days, 1 hour left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question