Attaching an Excel sheet is not called providing readily consumable sample data. Please check the link in my signature and post sample data as shown in the link. This time i'll do it for you so that other Forum members can use it and provide you with a solution:
--Creating Table
Create table Ex
(Product NVarchar(10),
InsertDateTime DateTime,
EffectiveDateTime DateTime,
Value Float )
--Inserting Sample Data
Insert into Ex
Select 'Apples','1/9/2012 5:03','1/10/2012 5:03',1.00
Union ALL
Select 'Apples','1/9/2012 5:03','1/10/2012 5:03',1.00
Union ALL
Select 'Apples','1/9/2012 5:03','1/10/2012 5:03',1.00
Union ALL
Select 'Apples','1/10/2012 5:03','1/10/2012 5:03',1.00
Union ALL
Select 'Apples','1/10/2012 5:03','1/10/2012 5:03',1.00
Union ALL
Select 'Apples','1/10/2012 5:03','1/10/2012 5:03',1.00
Union ALL
Select 'Apples','1/10/2012 5:03','1/10/2012 5:03',1.00
Union ALL
Select 'Apples','1/10/2012 5:03','1/10/2012 5:03',1.00
Union ALL
Select 'Apples','1/10/2012 5:03','1/10/2012 5:03',1.00
Union ALL
Select 'Apples','1/10/2012 5:03','1/10/2012 5:03',1.00
--Query for your Requirement
Select a.Product, a.[SumEffectivedate(currentday)] ,b.[SumEffectivedate(currentmonth)], c.[Sum Effectivedate(currentmonth)where_Insertdatetime_less_than_current_month]
From
(Select Product, SUM(Value) As [SumEffectivedate(currentday)]
From Ex
Group By Product, DATEPART(DD, EffectiveDateTime) ) As a
JOIN
(Select Product, SUM(Value) As [SumEffectivedate(currentmonth)]
From Ex
Group By Product, DATEPART(MM, EffectiveDateTime) ) As b ON a.Product = b.Product
JOIN
(Select Product, SUM(Value) As [Sum Effectivedate(currentmonth)where_Insertdatetime_less_than_current_month]
From Ex
Where DATEDIFF(DD, EffectiveDateTime, InsertDateTime) < 0
Group By Product, DATEPART(MM, EffectiveDateTime) ) As c ON a.Product = c.Product
From what I understood from the Excel sheet you provided....I came up with the above query. If this does not help then please provide the complete query that you are using and then may be someone might come up with a better solution.