I'm making a report and I need to filter out the last 3 vendor evaluations in case this vendor has been downgraded by the date requested in the report.
Each evaluation has several questions and I need to bring all these questions to each of the last 3 evaluations of each supplier.
Here's what I've got ready:
SELECT forn.cd_fornecedor,
forn.nm_fornecedor,
isoa.cd_avaliacao,
isoa.cd_ranking,
isoa.vl_nota,
isoa.vl_media,
isoa.dt_avaliacao,
isor.cd_resposta,
isor.cd_itpergunta,
isoi.cd_pergunta,
isoi.ds_itpergunta,
isop.ds_pergunta
FROM fornecedor forn,
iso_avaliacao isoa,
iso_resposta isor,
iso_itpergunta isoi,
iso_pergunta isop
WHERE forn.cd_fornecedor = isoa.cd_fornecedor
AND isoa.cd_avaliacao = isor.cd_avaliacao
AND isor.cd_itpergunta = isoi.cd_itpergunta
AND isoi.cd_pergunta = isop.cd_pergunta
--AND isoa.cd_ranking IN (1, 7)
AND forn.cd_fornecedor in (select isoa2.cd_fornecedor
from iso_avaliacao isoa2
where isoa2.dt_avaliacao between to_date('01/01/2016') and to_date('20/01/2016')
and isoa2.cd_ranking in (1,7)
/*and isoa2.cd_avaliacao in (select isoa3.cd_avaliacao
from iso_avaliacao isoa3
where )*/
)
--AND FORN.CD_FORNECEDOR = 4272
--AND ISOP.CD_PERGUNTA > 6
--AND ISOI.DS_ITPERGUNTA = 'NAO'
ORDER BY forn.nm_fornecedor, isoa.cd_avaliacao desc, isoa.dt_avaliacao;