Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Help with the qry

Posted on 2012-09-12
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
Question by:gs79
• 5
• 3
8 Comments

LVL 25

Expert Comment

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
``````
0

Author Comment

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

ID: 38393315
If you apply rule 1 before rule 2 (i.e. in order) you never look at 18.
0

Author Comment

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

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

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

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
``````
0

Author Closing Comment

ID: 38401918
Worked like a charm!
0

## Featured Post

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.