Oracle Help with Case Statement

[LadyDi02]

Wow now I am stumped with the requirments.

I have a subset of data using oracle 9.2

Date name SRT Status ValueINT
03-Jun-05 Jim C 123 WIP 0
03-Jun-05 Dino B 123 WIP 0
03-Jun-05 Jim C 123 Closed 1
03-Jun-05 Sal M 456 WIP 0
03-Jun-05 Dave G 456 WIP 0
03-Jun-05 Sal M 456 Closed 1

What I need to do, hopefully in a case statement or even a function(as my query is a bunch of selects and case statements), is look for all records where the field heading status is 'closed'. When you find the 'closed' status if name heading in that record is the same as the name in the first record of that SRT(look closely how I ordered the SRT numbers) then give the value of 1 in another column(ValueInt). Hope that makes sense. I am trying to compare values from the last record in an ordered by SRT, date to the first record in the order. The order by cannot change in this query.

Therefore looking for the first closed in the recordset I can see that it occurs in the following record.

03-Jun-05 Jim C 123 Closed 1

I will then compare this record to the first record of that SRT group(123) and I will find

03-Jun-05 Jim C 123 WIP 0

Well the name in this record is the same as the name in the closed record so therefore I will increment the field ValueInt for the closed record to 1. I will then proceed to look at SRT 456 and look for the closed status and compare it to the first record of SRT 456...etc etc
If someone can figure this out your a genious. Thanks again.

 

[Welshbird]

Let me make sure I understand this before I have a go...

for a given SRT number with a closed record, if the FIRST (how is the decided as being the first by the way?) record ofthat same SRT number has the same name, then the record with closed in the status field should have its ValueINT incremented by 1.

Does this mean the ValueINT gets bigger? Or is it always 1 or 0?

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[LadyDi02]

Willif thanks so much for helping out. Really appreciate this.

Ok to answer your question what I mean by first.... is if you look at the sample data I gave you for SRT 123 Jim C would be considered first and for 456 Sal M would be considered first. So I guess to try and describe this to you in oracle terms if I use the rank function and rank my data as follows and 1 sor each new SRT would be considered the first.

rank() over (partition by srt order by date) as rnk

You will see the ranking as follows

Date name SRT Status ValueINT RNK
03-Jun-05 Jim C 123 WIP 0 1
03-Jun-05 Dino B 123 WIP 0 2
03-Jun-05 Jim C 123 Closed 1 3
03-Jun-05 Sal M 456 WIP 0 1
03-Jun-05 Dave G 456 WIP 0 2
03-Jun-05 Sal M 456 Closed 1 3

I was trying to incorporate the rank function in case statement some how but haven't had the success I wanted.

As for your second question no the valueINT does not get bigger it is always 1 or 0 so that I may sum up all the 1's(which signify that there is a match) later.

 

[Welshbird]

Ok - That makes sense then... I have to attend some training today, but will try and get back to you as soon as I can with a response. (I don't think this is too difficult - but I'll need to have a play later before I make you any promises!)

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[cmmrfrds]

One way, is to use the analytical function first_value instead of rank. Do it in 2 steps. Establish the 1st and last in one step and pass it back to an outer step.


Select
'make your comparison here'
etc.....
From
(
Select
srt,
first_value(name) over (partition by srt order by date desc) as alast,
first_value(name) over (partition by srt order by date asc) as afirst
) aview

 

[Welshbird]

LadyDi02 => Did you get nearer to solving this? Sorry , work got a bit hectic again, so I haven't been able to look at it. I can do later if you still need me to though.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[LadyDi02]

Willif,

yes I did based on the other posters. Thanks for your help and follow up. Now I need some help with a post I submitted yesterday Thanks again.