Código SQL:
Ver originalCREATE TABLE #temp
(
id_mov INT,
fecha datetime,
depo_mov INT,
arti_mov INT,
cant INT,
tipomov INT
)
INSERT INTO #temp VALUES (1,'2013-23-06 00:00:00', 1, 1, 5, 1)
INSERT INTO #temp VALUES (2,'2013-23-06 00:00:00',1, 1, 1, 2)
INSERT INTO #temp VALUES (3,'2013-23-06 00:00:00',1, 1, 3, 3)
INSERT INTO #temp VALUES (4,'2013-23-06 00:00:00',1, 1, 10, 4)
INSERT INTO #temp VALUES (5,'2013-21-07 00:00:00',2, 1, 3, 5)
INSERT INTO #temp VALUES (6,'2013-22-06 00:00:00',3, 1, 5, 4)
INSERT INTO #temp VALUES (7,'2013-30-07 00:00:00',1, 2, 4, 3)
INSERT INTO #temp VALUES (8,'2013-23-06 00:00:00',2, 2, 1, 4)
INSERT INTO #temp VALUES (9,'2013-23-07 00:00:00',3, 2, 1, 4)
INSERT INTO #temp VALUES (10,'2013-25-06 00:00:00',1, 3, 2, 3)
INSERT INTO #temp VALUES (11,'2012-24-06 00:00:00',1, 1, 3, 2)
INSERT INTO #temp VALUES (12,'2012-24-06 00:00:00',1, 1, 5, 4)
INSERT INTO #temp VALUES (13,'2012-24-06 00:00:00', 1, 1, 1, 2)
--drop table #temp2
CREATE TABLE #temp2
(
id_tipo INT,
factor INT
)
INSERT INTO #temp2 VALUES (1, 1)
INSERT INTO #temp2 VALUES (2, -1)
INSERT INTO #temp2 VALUES (3, -1)
INSERT INTO #temp2 VALUES (4, 1 )
INSERT INTO #temp2 VALUES (5, -1)
SELECT mes,[año],depo_mov,arti_mov,SUM(factor) total FROM(
SELECT datepart(mm,fecha) AS mes, datepart(yyyy,fecha) AS [año],depo_mov,arti_mov,(cant*factor) AS factor FROM #temp AS t1
LEFT JOIN #temp2 AS t2 ON (t1.tipomov=t2.id_tipo)) t1 GROUP BY mes,[año],depo_mov,arti_mov