SET DATEFORMAT mdy

DECLARE @test-2 TABLE (

Site_id int,

product_id int,

mode varchar(10),

quantity decimal(9,3),

date_of_issue_return date

)

INSERT INTO @test-2 VALUES

(3, 14 ,'Issue' ,75 ,'02/10/2014')

,(6, 14 ,'Issue' ,100.5 ,'02/15/2014')

,(7, 14 ,'Return' ,120 ,'01/14/2014')

,(1, 12 ,'Issue' ,400 ,'01/04/2014')

,(1, 12 ,'Issue' ,27.5 ,'03/05/2014')

,(4, 9 ,'Return' ,159 ,'03/28/2014')

,(4, 9 ,'Return' ,20 ,'03/19/2014')

,(3, 14 ,'Return' ,30 ,'01/25/2014')

,(4, 9 ,'Issue' ,101 ,'01/01/2014')

SELECT site_id,

product_id,

SUM(CASE mode WHEN 'Issue' THEN quantity ELSE 0 END) AS total_issue_qty,

SUM(CASE mode WHEN 'Return' THEN quantity ELSE 0 END) AS total_return_qty,

SUM(CASE mode WHEN 'Issue' THEN quantity ELSE 0 END)

- SUM(CASE mode WHEN 'Return' THEN quantity ELSE 0 END) AS total_consumption

FROM @test-2

WHERE date_of_issue_return >= '01/01/2014'

AND date_of_issue_return < '02/01/2014'

GROUP BY site_id,

product_id

Hope this helps