UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Oracle Discoverer Plus    
 
   
jimhaddon
post Mar 7 2008, 06:03 PM
Post #1

UtterAccess Enthusiast
Posts: 59
Hi,

I have a report I'm trying to create in Discoverer, but I don't know if it's possible.

I have a list of data as below

|Employee Num|Person Name|Appraisal Date|
|129484387 |Mark Smith |01-JAN-2006 |
|129484387 |Mark Smith |23-FEB-2007 |
|195839493 |Sarah Bloggs|01-FEB-2007 |
|342356436 |Daniel Craig |17-MAR-2008 |


I need to use CASE WHEN on the MOST RECENT 'Appraisal Date', to show 1 if it's between two certain dates, otherwise 0.

Then, I need to sum the outcome.

This is what i've tried so far...

CODE
SUM(CASE WHEN MAX(Appraisal Date) BETWEEN to_date('01-JAN-2007') and to_date('01-FEB-2008') then 1 else 0 end)


But this just does not work, as analytic functions are not allowed on aggregate functions.

So, then I tried the same but with a partition in:

CODE
SUM(CASE WHEN MAX(Appraisal Date) BETWEEN to_date('01-JAN-2007') and to_date('01-FEB-2008') then 1 else 0 end) OVER (PARTITION BY Employee Num)


This just counts all of the correct dates, but gives the total in every cell.

I've also tried using a condition like so:

CODE
Appraisal date = Max(Appraisal Date)


This just gives 'unimplemented feature'.

Any ideas anyone?

Cheers

James
Go to the top of the page
 
+
jsitraining
post Mar 8 2008, 10:21 AM
Post #2

UtterAccess VIP
Posts: 5,230
From: Scotland (Sunny Glasgow)
Hi James. It is not entirely clear what exact resultset you are looking for. Are you looking to supply a count of how may appraisal dates lie within the specified date range, for each employee? Could you supply a slightly bigger resultset and also supply the expected results to help make things clearer.
Cheers
Jim
PS
TO_DATE('01-Jan-2007') is a really bad idea as it relies on on your date format settings for the current session being correct. always supply the full and appropriate format mask (you may even want to make it language independent by not using a 3 char month, but rather use a number for the month).
Go to the top of the page
 
+
jimhaddon
post Mar 8 2008, 10:58 AM
Post #3

UtterAccess Enthusiast
Posts: 59
Hi Jim,

Please find attached example...

Thanks for looking

James
Attached File(s)
Attached File  Oracle Example.zip ( 2.52K ) Number of downloads: 7
 
Go to the top of the page
 
+
jsitraining
post Mar 9 2008, 03:59 AM
Post #4

UtterAccess VIP
Posts: 5,230
From: Scotland (Sunny Glasgow)
Hi,
here is a quick demo that works in SQL based on tha data that you supplied (i added an extra row for testing). Unfortunately I don't have Discoverer installed here at home (nor at work for that matter) so I can't actually test that the syntax works corrrectly. I don't think there should be any issues but never say never.
CODE
WITH t AS ( SELECT 'Ward 4' department, 123456 employee_num, 'James Haddon' person_name,  TO_DATE('01/01/2007','DD/MM/YYYY') appraisal_date FROM DUAL UNION ALL
            SELECT 'Ward 4', 123456, 'James Haddon',  TO_DATE('02/02/2008','DD/MM/YYYY') FROM DUAL UNION ALL
            SELECT 'Ward 2', 654321, 'Sarah Smith',  TO_DATE('01/02/2008','DD/MM/YYYY') FROM DUAL UNION ALL
            SELECT 'Ward 7', 456154, 'John Smith',  TO_DATE('23/02/2006','DD/MM/YYYY') FROM DUAL UNION ALL
            SELECT 'Ward 7', 456154, 'John Smith',  TO_DATE('27/02/2007','DD/MM/YYYY') FROM DUAL UNION ALL
            SELECT 'Ward 7', 456154, 'John Smith',  TO_DATE('28/02/2008','DD/MM/YYYY') FROM DUAL UNION ALL
            SELECT 'System Support', 165829, 'Kelvin Ho',  TO_DATE('02/07/2002','DD/MM/YYYY') FROM DUAL UNION ALL
            SELECT 'System Support', 165829, 'Kelvin Ho',  TO_DATE('02/07/2003','DD/MM/YYYY') FROM DUAL UNION ALL
            SELECT 'System Support', 165829, 'Kelvin Ho',  TO_DATE('02/07/2007','DD/MM/YYYY') FROM DUAL UNION ALL
            SELECT 'System Support', 165829, 'Kelvin Ho',  TO_DATE('02/02/2008','DD/MM/YYYY') FROM DUAL UNION ALL
            SELECT 'Ward 1', 385764, 'Gretta Clayton',  TO_DATE('14/02/2006','DD/MM/YYYY') FROM DUAL UNION ALL
            SELECT 'Ward 1', 385764, 'Gretta Clayton',  TO_DATE('16/02/2008','DD/MM/YYYY') FROM DUAL UNION ALL
            SELECT 'Ward 2', 185264, 'John Raycliff',  TO_DATE('01/02/2007','DD/MM/YYYY') FROM DUAL UNION ALL
            SELECT 'Ward 2', 185265, 'Bernie Cliffton',  TO_DATE('01/02/2008','DD/MM/YYYY') FROM DUAL UNION ALL
            SELECT 'Ward 7', 158685, 'Marge Simpson',  TO_DATE('23/08/2007','DD/MM/YYYY') FROM DUAL)
SELECT department
           , COUNT(DISTINCT employee_num)
           , SUM(CASE WHEN appraisal_date > TO_DATE('01/01/2008','DD/MM/YYYY') THEN 1 ELSE 0 END) calc
FROM t
GROUP BY department;

Obviously you don't need to worry about the subquery factoring clause, what you are cocerned with is simply the expressiion that I aliased as calc
Go to the top of the page
 
+
jimhaddon
post Mar 9 2008, 05:51 AM
Post #5

UtterAccess Enthusiast
Posts: 59
Thanks Jim, but I have no idea what you did there...

In Discoverer at work, I cannot use any SELECT, or change any queries. I can only add fields from the EUL.

Therefore I can only make calculations on data, not change the data source or sub queries or anything like that... this is why I don't think this is possible.

Also, i only supplied you with the data for a few staff, whereas at work, there are ~ 3000 employees I need to run this data for.

Thanks for your time anyway Jim, it is much appreciated.
Go to the top of the page
 
+
jsitraining
post Mar 9 2008, 07:53 AM
Post #6

UtterAccess VIP
Posts: 5,230
From: Scotland (Sunny Glasgow)
Hi Jim,
I think that you misunderstand. The only bit that you need to focus on is the expression i.e.
CODE
SUM(CASE WHEN appraisal_date > TO_DATE('01/01/2008','DD/MM/YYYY') THEN 1 ELSE 0 END)
The code above was simply used as a test case (I didn't think for a second that you would be querying only a few rows, I'd have told you to use Excel in that case (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif) ) You will need to tweak the expression to suit your needs (putting in the appropriate ranges for the date(s), putting in the correct item names etc) but the structure should be correct.
Go to the top of the page
 
+
jimhaddon
post Mar 9 2008, 08:32 AM
Post #7

UtterAccess Enthusiast
Posts: 59
Yes Jim, that's the calculation I specified in my original post. The only problem with that, is it's not counting just the LATEST appraisal date, but all of them.
Go to the top of the page
 
+
jsitraining
post Mar 9 2008, 05:04 PM
Post #8

UtterAccess VIP
Posts: 5,230
From: Scotland (Sunny Glasgow)
1. At no point in your original post, did you use that expression
2. My expression supplies the correct result as per your supplied data.
So hows about you supply a test case that will accurately describe your situation and where my expression does not meet the criteria.
Go to the top of the page
 
+
jimhaddon
post Mar 11 2008, 11:25 AM
Post #9

UtterAccess Enthusiast
Posts: 59
1) My Post:

SUM(CASE WHEN MAX(Appraisal Date) BETWEEN to_date('01-JAN-2007') and to_date('01-FEB-2008') then 1 else 0 end)

Your Post: SUM(CASE WHEN appraisal_date > TO_DATE('01/01/2008','DD/MM/YYYY') THEN 1 ELSE 0 END)

The only difference there is the Max() functions and using between instead of >, and the Date Formating.

The reason I used the Max() function is to get the LATEST appraisal date for each employee, not just any date.

2) Yes, that is correct but your formula would not work if one person had two appraisal dates within the selected date. (i.e. if formula was SUM(CASE WHEN appraisal_date > TO_DATE('01/01/2008','DD/MM/YYYY') THEN 1 ELSE 0 END) and the employee had two appraisal dates of: 03/02/2008 and 03/05/2008, this would give the employee TWO appraisals, whereas I only want to count the LATEST date.

Does this help?
Go to the top of the page
 
+
jsitraining
post Mar 15 2008, 03:16 AM
Post #10

UtterAccess VIP
Posts: 5,230
From: Scotland (Sunny Glasgow)
Apologies for not getting back to you. I got called out to a client site and was unable to look further into your issue. I don't want to get into any kind of argument, but let's face it:
SUM(CASE WHEN MAX(Appraisal Date) BETWEEN to ...
is different to:
SUM(CASE WHEN appraisal_date > TO...
and the data that you supplied, in your zip file, gave the wrong impression of your situation. Now, as far as the solution goes, I can't see a way without the use of subqueries (which are unavailable in Discoverer Plus). However, I am no Discoverer wizard, so you may find a better answer from the Reports and Discoverer forum here (I would suggest however, that when you supply a test case to the forum, that you make the data as representative as you can, i.e. supply data that will need the function to return more than just the value '1'.
Go to the top of the page
 
+
jimhaddon
post May 4 2008, 09:54 AM
Post #11

UtterAccess Enthusiast
Posts: 59
For anyone who might encounter this in the future, I used the RANK() function to overcome this..
Go to the top of the page
 
+
lnsalazar
post Aug 2 2011, 09:58 AM
Post #12

New Member
Posts: 1
Hi Jim, I have the same problem, I try to use this: sum(CASE WHEN Bi tarifa.Nromesvig = 0 THEN COUNT(Bi tarifa.Idepol||Bi tarifa.Numcert) ELSE 0 END), but discoverer show me this: nested aggregate functions are not allowed....

you can solved this problem ???
Go to the top of the page
 
+
« Next Oldest · Other Database Engines · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 18th May 2013 - 04:48 AM