I got query from dbforums but in weekly data am getting open value and close value are null sometimes. if monday has no trading I need next day OpenPRice is open value. If friday has no trading I need before date closeprice is close value and close date.

I had the query like this,



SELECT tabHL.startofweek,tabHL.endofweek, tabOPEN.OpenPrice, tabHL.weekhigh, tabHL.weeklow, tabCLOSE.ClosePrice FROM ((
SELECT MIN(Date1) startofweek, MAX(Date1) endofweek, max(HighPrice) AS weekhigh, min(LowPrice) AS weeklow FROM HistoricalData c where Symbol='ABAN'
GROUP BY DateAdd(day, -1 * datepart(dw, Date1), Date1 )
) AS tabHL

LEFT JOIN (
SELECT Date1, OpenPrice FROM HistoricalData c
WHERE Date1 = DateAdd(day, -1 * datepart(dw, Date1)+2, Date1) and Symbol='aban'
) AS tabOPEN ON tabHL.startofweek = tabOPEN.Date1)

LEFT JOIN (
SELECT Date1, ClosePrice FROM HistoricalData c
WHERE Date1 = DateAdd(day, -1 * datepart(dw, Date1)+6, Date1) and Symbol='aban'
) AS tabCLOSE ON tabHL.endofweek = tabCLOSE.Date1
ORDER BY convert(datetime,tabHL.startofweek,101) asc