Filtering Specific Code's Date

[yokboylebirsey]

Hi gurus,

I need to make a report which shows some codes dates. My report members are;

• employee
• work code
• work code's date

What I want to do is filter and order work codes. For example, if "A" code does exist for a employee take "A" code and ignore others, if "A" code does not exist then take "B" code else take "C" code, if none of them exist show "null". The order is important, every employee should got 1 date and they shouldn't duplicate because of diffrent codes, if a employee has "a" and "c" code, the filter has to ignore the "c" code and take "a".

The list should be like this:

EMPLOYEE     CODES     DATES AAA BBBBBB        A         01.04.2013 SSSS DDDDD       A         02.04.2013 FFFF GGGGGG      C         01.04.2013 EEEE RRRRRR       B         05.04.2013 CCCC BBBBBB      C         07.04.2013 OOOO BYYYYB      null      --.--.----

There is a lot of codes but I interest 3 of them and their ordes. I tried case and if statement but i couldn't achieve it.

Thanks,
yokboylebirsey

[RKMI]

What was your case statement and what was the returned output?

Thanks,
RK

[yokboylebirsey]

My case statement was

Code Select Expand

case([WORK_CODE])
WHEN 'A' THEN 'A'
WHEN 'B' THEN 'B'
WHEN 'C' THEN 'C'
ELSE
'NONE'
END
I know, it's missing something this statement.
My output was

EMPLOYEE     CODES     DATES AAA BBBBBB        A         01.04.2013 AAA BBBBBB       B         01.04.2013 AAA BBBBBB      C         01.04.2013 EEEE RRRRRR       B         05.04.2013 CCCC BBBBBB      C         07.04.2013 OOOO BYYYYB      null      --.--.----

As you can see, there is 3 rows for "AAA BBBBBB", I need to ignore 2 of them(B&C), cause the order is A-->B-->C.

[BigChris]

I think your CASE statement is working correctly - what you perhaps need to do is group on the Employee Code and find the minimum code in one query. THen link that query back to your code to get the date for that code.

[yokboylebirsey]

Thanks BigChris, but i guess it's getting complicated, is there any way to solve this problem?

[BigChris]

It's hard to tell from the data that you've posted, but is there a different way of looking at the problem? Your data suggests that A happens before B and that B hapens before C. Could your query be constructed to be a bit more like:

[Employee], [Date], minimum([Codes] for [Employee], [Date]). You'd need to group the data on [Employee] and [Date], which in turn should bring back something that looks like:


EMPLOYEE       CODES        DATE
AAA BBBBBB       A         01.04.2013
EEEE RRRRRR      B         05.04.2013
CCCC BBBBBB      C         07.04.2013

Obviously I might have that round my neck, but it might be worth investigating...

[yokboylebirsey]

Sorry for misunderstanding but minimum([codes]) is not what I want. On some cases "A" could be after "B" or "C" however I need "A" code on those circumstances. For this reason, I should order them while filtering, but how?