Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a log table with these following fields:
DOMANDA_ID | LOG_DATE | LOG_STATO |
1 | 20/01/2013 | SOSPESA |
1 | 22/01/2013 | APPROVATA SENZA COPERTURA |
1 | 02/03/2013 | APPROVATA |
2 | 06/02/2013 | SOSPESA |
2 | 21/02/2013 | RESPINTA |
3 | 23/05/2013 | APPROVATA |
4 | 28/08/2013 | SOSPESA |
4 | 05/12/2013 | APPROVATA SENZA COPERTURA |
5 | 15/07/2013 | SOSPESA |
6 | 21/09/2013 | RESPINTA |
DOMANDA_ID is the key and LOG_STATO is a description.
Selecting the year from YEAR(LOG_DATE) field, I need to show the count of DOMANDA_ID based on max LOG_DATE and group by every LOG_STATO. For example, for 2013 there are the following records:
DOMANDA_ID | LOG_DATE | LOG_STATO |
1 | 02/03/2013 | APPROVATA |
2 | 21/02/2013 | RESPINTA |
3 | 23/05/2013 | APPROVATA |
4 | 05/12/2013 | APPROVATA SENZA COPERTURA |
5 | 15/07/2013 | SOSPESA |
6 | 21/09/2013 | RESPINTA |
.. and then I need to show this pivot :
LOG_STATO | COUNT(DOMANDA_ID) |
APPROVATA | 2 |
APPROVATA SENZA COPERTURA | 1 |
RESPINTA | 2 |
SOSPESA | 1 |
I tried using Max, Aggr, FirstSortedValue and set analysis without find a solution. Have you got a similar case?
Thank you
Giovanni
Hello,
I had two problems:
So I find the solution using ROWNO() AS LOG_ID, WHERE LOG_STATO<>'' and ORDER BY DOMANDA_ID, LOG_DATE in script. Then in pivot i used =aggr(FirstSortedValue(LOG_STATO,-LOG_ID),DOMANDA_ID) as dimension and COUNT(DISTINCT DOMANDA_ID) as expression.
Thank you to all for your response.