With 146 points it would seem you've been around long enough to know we need some help from you to help you, namely in the form of DDL and sample data.

CREATE TABLE #tblProducts

(

Product VARCHAR(10)

,[Month] INT

,[Year] INT

,Location VARCHAR(5)

,StartDate DATE

,EndDate DATE

,MaxPrice MONEY

,MaxPriceDate DATE

,MinPrice MONEY

,MinPriceDate DATE

);

INSERT INTO #tblProducts

(Product, [Month], [Year], Location, StartDate, EndDate)

SELECT 'ACBD',8,2013,'Lon','11/01/2012','10/31/2013'

UNION ALL SELECT 'ACBD',9,2013,'Lon','11/01/2012','10/31/2013'

UNION ALL SELECT 'ACBD',10,2013,'Lon','11/01/2012','10/31/2013'

UNION ALL SELECT 'EFGH',8,2013,'Lon','11/01/2012','10/31/2013';

I must be feeling charitable today, so perhaps this will get you close:

WITH tblPrice ([Date],Product,[Month],[Year],Price) AS

(

SELECT CAST('08/01/2013' AS DATE),'ACBD',9,2013,180

UNION ALL SELECT '07/31/2013','ACBD',9,2013,178

UNION ALL SELECT '07/30/2013','ACBD',9,2013,167

UNION ALL SELECT '07/29/2013','ACBD',9,2013,179

UNION ALL SELECT '07/26/2013','ACBD',9,2013,180

UNION ALL SELECT '07/25/2013','ACBD',9,2013,167

UNION ALL SELECT '07/24/2013','ACBD',9,2013,175

UNION ALL SELECT '07/23/2013','ACBD',9,2013,175

)

,tblDiff ([Date],Product,[Month],[Year],Location,Price) AS

(

SELECT CAST('08/01/2013' AS DATE),'ACBD',9,2013,'Lon',1

UNION ALL SELECT '07/29/2013','ACBD',9,2013,'Lon',2

UNION ALL SELECT '07/28/2013','ACBD',9,2013,'Lon',5

UNION ALL SELECT '07/27/2013','ACBD',9,2013,'Lon',5

UNION ALL SELECT '07/24/2013','ACBD',9,2013,'Lon',3

)

,Calendar AS

(

SELECT [Date]=DATEADD(day, n, (SELECT MIN([Date]) FROM tblPrice))

FROM

(

SELECT 0 UNION ALL

SELECT TOP (SELECT DATEDIFF(day, MIN([Date]), MAX([Date])) FROM tblPrice)

ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM sys.all_columns a

) a(n)

),

Prices AS

(

SELECT c.Product,c.[Month],c.[Year],c.Location

,PriceDate=a.[Date]

,Price=b.Price + c.Price

,rn1=ROW_NUMBER() OVER (PARTITION BY c.Product,c.[Month],c.[Year],c.Location ORDER BY b.Price + c.Price)

,rn2=ROW_NUMBER() OVER (PARTITION BY c.Product,c.[Month],c.[Year],c.Location ORDER BY b.Price + c.Price DESC)

FROM Calendar a

OUTER APPLY

(

SELECT [Date],Product,[Month],[Year],Price

FROM

(

SELECT [Date],Product,[Month],[Year],Price

,rn=ROW_NUMBER() OVER (PARTITION BY Product,[Month],[Year] ORDER BY b.[Date] DESC)

FROM tblPrice b

) b

WHERE rn = 1 AND a.[Date] <= b.[Date]

) b

OUTER APPLY

(

SELECT TOP 1 [Date],Product,[Month],[Year],Location,Price

FROM tblDiff c

WHERE c.[Date] <= a.[Date] AND

c.Product = b.Product AND c.[Month] = b.[Month] AND c.[Year] = b.[Year]

ORDER BY c.[Date] DESC

) c

)

UPDATE a

SET MinPrice = b.MinPrice

,MinPriceDate = b.MinPriceDate

,MaxPrice = b.MaxPrice

,MaxPriceDate = b.MaxPriceDate

FROM #tblProducts a

JOIN

(

SELECT Product,[Month],[Year],Location

,MinPrice=MAX(CASE WHEN rn1=1 THEN Price END)

,MinPriceDate=MAX(CASE WHEN rn1=1 THEN PriceDate END)

,MaxPrice=MAX(CASE WHEN rn2=1 THEN Price END)

,MaxPriceDate=MAX(CASE WHEN rn2=1 THEN PriceDate END)

FROM Prices

GROUP BY Product,[Month],[Year],Location

) b

ON a.Product = b.Product AND a.[Month] = b.[Month] AND a.[Year] = b.[Year] AND

a.Location = b.Location;

SELECT *

FROM #tblProducts;

GO

DROP TABLE #tblProducts;

Note that I get something slightly different than your expected results, but that may be due to an inconsistency that seems to be in your expected results. Or possibly a minor error in my suggested query or data setup. Either way, it would be incumbent upon you to analyze my method (I believe it is sound) and see if you can make it work for you.