November 26, 2007 at 4:22 pm
SELECT KRR.KRR_COM_id AS CompanyId, CUR_code AS CurrencyCode,
AKA.AKA_MED_id AS MetadataId, AKA.AKA_TIM_id AS TimeId, AKA.AKA_keyValueY AS TimeYear
,KRR.KRR_id as ReportId
FROM AKA_ArchivedKeyRatio AKA JOIN
KRR_KeyRatioReport KRR ON
AKA.AKA_KRR_id = KRR.KRR_id
JOIN CUR_Currency CUR on KRR.KRR_currencyId=CUR.CUR_id
where KRR.KRR_id IN
(SELECT (KRR1.KRR_id) from KRR_KeyRatioReport KRR1 where
KRR1.KRR_COM_id=KRR.KRR_COM_id AND KRR1.KRR_COD_id=118
AND KRR1.KRR_publishedDate<='2006-11-26'
AND KRR1.KRR_publishedDate>='2006-05-26')
AND
KRR.KRR_COD_id = 118 AND AKA.AKA_MED_id = 195
AND ((KRR.KRR_COM_id IN (59)AND
AKA.AKA_TIM_ID = 525) OR
(KRR.KRR_COM_id IN (962,944,911,345)AND AKA.AKA_TIM_ID = 530))
order by KRR_COM_id, reportID desc
returns
345 NOK 195 530 12.5150735942 30577
345 NOK 195 530 12.5150735942 30526
345 NOK 195 530 12.5150738809 30452
345 NOK 195 530 13.6679479495 29666
345 NOK 195 530 13.5697898828 27732
345 NOK 195 530 11.9047718556 27380
345 NOK 195 530 11.5035318873 26649
345 NOK 195 530 11.5035318874 26382
911 DKK 195 530 32.1182352867 26781
911 DKK 195 530 32.1182352919 26492
962 EUR 195 530 1.5316402000 30655
962 EUR 195 530 1.5285702000 30607
962 EUR 195 530 1.5285702000 30543
I would like to ONLY return ONE value for each CompanyId, hence one reportId per CompanyId, but with the criteria that TimeYear value differs from the previous one when searching from the highest ReportId for example. So for each CompanyId I search for each ReportId checking it's TimeYear value and "break" the search when the criteria is fulfilled
These are the rows I would like to be returned, when starting with the highest reportid
345 NOK 195 530 12.5150738809 30452
911 DKK 195 530 32.1182352919 26492
962 EUR 195 530 1.5285702000 30607
It's not the maximum value, but it's the first value, that has changed based on searching from the highest reportid (order by reportid desc)
That may not be able to query so easy I guess
Is that possible?
November 26, 2007 at 8:34 pm
I am not sure this will get what you want since I don't have the data and I don't understand your system, but you can try.
SELECT K.KRR_COM_id AS CompanyId, C.CUR_code AS CurrencyCode, A.AKA_MED_id AS MetadataId, A.AKA_TIM_id AS TimeId, A.AKA_keyValueY AS TimeYear
,K.KRR_id as ReportId
FROM AKA_ArchivedKeyRatio A
INNER JOIN KRR_KeyRatioReport K ON A.AKA_KRR_id = K.KRR_id
INNER JOIN CUR_Currency C on K .KRR_currencyId=C.CUR_id
INNER JOIN (SELECT R.KRR_id Companyid, R.KRR_COD_id CodeID, MAX(K.KRR_id) ReportID
FROM KRR_KeyRatioReport R
WHERE R.KRR_COD_id = 118
AND R.KRR_PublishedDate <='2006-11-26'
AND R.KRR_PublishedDate >='2006-05-26'
GROUP BY R.KRR_id, R.KRR_COD_id ) R ON R.Companyid = K.KRR_id AND R.CodeID = K.KRR_COD_id AND R.ReportID = K.KRR_id
WHERE A.AKA_MED_id = 195 AND
((K.KRR_COM_id = 59 AND A.AKA_TIM_ID = 525) OR (K.KRR_COM_id IN (962,944,911,345) AND A.AKA_TIM_ID = 530))
ORDER BY K.KRR_COM_id, K.KRR_id desc
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply