Sean

I realised this morning that the data I provided wasn't much use on account of it not containing the the actual problem so I've added a few more lines.

Ok, the data now contains dates for 3 weeks: weeke ending 5th Aug 12, 12th Aug 12 and 19th Aug 12. In the SSRS report this data would be summarised as follows:

week ending 5th August = 62 PlanItemsSold & 67 ActualItemsSold; week ending 12th August = 61 PlanItemsSold & 27 ActualItemsSold; week ending 19th August = 51 PlanItemsSold & 66 ActualItemsSold.

I'm happy with 12th and 19th totals but weeke dning 5th August is not very accurate since only 5 out of its 7 days fall within August (the other 2 falling within July). So what I'd like is to be able to somehow recalculate these figures so that only 5/7 of the total are calculated ie week ending 5th August should now = 44 PlanItemsSold & 48 ActualItemsSold.

I wrongly said yesterday that I needed the monthly to change but of course if the week ending totals change then the monthly total will correspondingly change anyway.

Hope this all makes sense.

Thanks again.

BO

IF OBJECT_ID('TempDB..#MyTable') IS NOT NULL

BEGIN

DROP TABLE #MyTable

END

CREATE TABLE [dbo].[#MyTable]

(

[WeekEndDate] [date] NULL,

[site] [varchar](10) NULL,

[ItemDescription] [varchar](75) NULL,

[PlanItemsSold] [int] NULL,

[ActualItemsSold] [int] NULL

)

SET IDENTITY_INSERT #mytable ON

--===== Insert the test data into the test table

INSERT INTO #MyTable (WeekEndDate, [site], ItemDescription, PlanItemsSold, ActualItemsSold)

SELECT 'Aug 12 2012 12:00AM', 'Central', 'Item1', '10', '7'

UNION ALL SELECT 'Aug 12 2012 12:00AM', 'Central', 'Item2', '5', '3'

UNION ALL SELECT 'Aug 12 2012 12:00AM', 'Central', 'Item3', '21', '7'

UNION ALL SELECT 'Aug 12 2012 12:00AM', 'South', 'Item1', '14', '3'

UNION ALL SELECT 'Aug 12 2012 12:00AM', 'West', 'Item2', '11', '7'

UNION ALL SELECT 'Aug 19 2012 12:00AM', 'West', 'Item1', '17', '9'

UNION ALL SELECT 'Aug 19 2012 12:00AM', 'North', 'Item1', '21', '32'

UNION ALL SELECT 'Aug 19 2012 12:00AM', 'East', 'Item1', '13', '19'

UNION ALL SELECT 'Aug 5 2012 12:00AM', 'West', 'Item2', '11', '7'

UNION ALL SELECT 'Aug 5 2012 12:00AM', 'West', 'Item1', '17', '9'

UNION ALL SELECT 'Aug 5 2012 12:00AM', 'North', 'Item1', '21', '32'

UNION ALL SELECT 'Aug 5 2012 12:00AM', 'East', 'Item1', '13', '19'

--===== Set the identity insert back to normal

SET IDENTITY_INSERT #MyTable OFF

select * from #MyTable

drop table #MyTable