我正在处理的查询有两个日期列,一个是“结算日期”,另一个是“报价日期”。您可以根据需要(在特定时间内)提供尽可能多的要约,但是在这种情况下,我只希望看到最新的要约,而不是例如一周前提出的要约。
我认为我可以通过使用MAX(offerdate)来实现这一目标,但是显然不能。
例如,当我运行以下查询时,对于结算日期01/01/2016,我得到7行-报价日期为2015年12月23日至2015年12月31日。就像我上面说的,我只想看看最新的一本。有什么想法吗?
SELECT settlementdate,duid,MAX(offerdate),ROUND(AVG(priceband1),2) AS PRICE_BAND1,ROUND(AVG(priceband2),2) AS PRICE_BAND2,ROUND(AVG(priceband3),2) AS PRICE_BAND3,ROUND(AVG(priceband4),2) AS PRICE_BAND4,ROUND(AVG(priceband5),2) AS PRICE_BAND5,ROUND(AVG(priceband6),2) AS PRICE_BAND6,ROUND(AVG(priceband7),2) AS PRICE_BAND7,ROUND(AVG(priceband8),2) AS PRICE_BAND8,ROUND(AVG(priceband9),2) AS PRICE_BAND9,ROUND(AVG(priceband10),2) AS PRICE_BAND10
FROM fullauth.biddayoffer
WHERE settlementdate >= to_date('01/01/2016','dd/mm/yyyy')
AND settlementdate <= to_date ('31/01/2016','dd/mm/yyyy')
AND duid IN (SELECT duid FROM dudetailsummary
WHERE end_date > to_date('01/01/2016','dd/mm/yyyy')
AND duid = 'LD01'
AND dispatchtype = 'GENERATOR')
AND offerdate < settlementdate - 11.5/24
GROUP BY settlementdate,bidtype
ORDER BY settlementdate ASC;