count for max date and group by status

[gvasti]

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

[gvasti · Accepted solution]

Hello,

I had two problems:

1. there were some records without value on LOG_STATO, so the aggr(FirstSortedValue(LOG_STATO,-DATA_LOG) did not work properly
2. aggr(FirstSortedValue(LOG_STATO,-DATA_LOG) had problems with some values on DATA_LOG, I do not know why...

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.