Select Lkp.DateValue, Lkp.YearNum, Lkp.MonthNum, Lkp.DayOfWeekName, Beauty.ZoneCode, Beauty.PeakOrOffPeak, Beauty.Price, beauty.RTbid,Beauty.RTOffer, Beauty.Long, Beauty.Short, ISO.Inc, ISO.Dec, (Beauty.Price-ISO.Inc) ISOShort, (ISO.Dec-Beauty.Price) ISOLong
From (Select Lkp.DateValue, Lkp.PeakOrOffPeak, ISO.ZoneCode, avg(ISO.PriceInc) Inc, Avg(ISO.PriceDec) Dec
From LkpCalendar_Definitions Lkp, CA_ISOExPost10Price ISO
Where Lkp.DateTimeValue = ISO.CADateTime
Group By Lkp.DateValue, Lkp.PeakOrOffPeak, ISO.ZoneCode)ISO, (Select DA.CADateTime, DA.ZoneCode, DA.PeakOrOffPeak, DA.Price, RT.RTBid, RT.RTOffer, ((RT.RTBid)-DA.Price) Long, ((DA.Price)-RT.RTOffer) Short
From (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
Group By Lkp.DateValue, Lkp.PeakOrOffPeak, RT.Entity) RT, West_Spotindex DA

Where RT.Entity = DA.ZoneCode
AND RT.DateValue = DA.CADateTime
AND RT.PeakOrOffPeak = DA.PeakOrOffPeak) Beauty, LkpCalendar_Definitions Lkp

Where Lkp.DateTimeValue = Beauty.CADateTime
AND Beauty.PeakOrOffPeak = ISO.PeakOrOffPeak
AND Lkp.DateValue = ISO.DateValue
and lkp.datevalue>'7/1/2001'
Order By Lkp.DateValue