Select Lkp.DateValue, Lkp.YearNum, Lkp.MonthNum, Lkp.DayOfWeekName, DA..ZoneCode, DA.PeakOrOffPeak, DA.Price, RT.RTBID,RT.RTOffer,ISO.Inc, ISO.Dec
from(select cadatetime,peakoroffpeak,zonecode,price
from west_spotindex
where cadatetime<'8/31/2001'
group by cadatetime,peakoroffpeak,zonecode,price)da,
(Select Lkp.DateValue, Lkp.PeakOrOffPeak, RT.Entity, Avg(RT.Bid) RTBid, Avg(RT.Offer) RTOffer
From CA_HourlyBidOffer RT, LkpCalendar_Definitions Lkp
Where Lkp.DateTimeValue = RT.CADateTime
and lkp.datevalue<'8/31/2001'
Group By Lkp.DateValue, Lkp.PeakOrOffPeak, RT.Entity)RT,
(Select Lkp.DateValue, Lkp.PeakOrOffPeak, lkpzone.deliverypoint, avg(ISO.PriceInc) Inc, Avg(ISO.PriceDec) Dec
From LkpCalendar_Definitions Lkp, CA_ISOExPost10Price ISO, lkpCA_Zonecodes lkpzone
Where Lkp.DateTimeValue = ISO.CADateTime
and iso.zonecode = lkpzone.zonecode
and lkp.datevalue='9/1/2001'
Group By Lkp.DateValue, Lkp.PeakOrOffPeak, lkpzone.deliverypoint)ISO, LkpCalendar_Definitions lkp
Where DA.zonecode=RT.entity
And DA.zonecode=ISO.deliverypoint
and DA.cadatetime=RT.datevalue
and DA.cadatetime=ISO.datevalue
and DA.PeakorOffPeak=RT.PeakorOffPeak
and DA.PeakorOffPeak=ISO.PeakorOffPeak
and lkp.datevalue=da.cadatetime
and lkp.datevalue=rt.datevalue
and lkp.datevalue=iso.datevalue
ORDER BY LKP.DATEVALUE