Código SQL:
Ver originalDROP TABLE #temp
DROP TABLE #temp2
CREATE TABLE #temp
(
consecutivo VARCHAR(20),
trabajador VARCHAR(20),
labor1 INT,
labor2 INT,
labor3 INT
)
INSERT INTO #temp VALUES ('01','001' ,1,0,0)
INSERT INTO #temp VALUES ('01','002' ,0,1,0)
INSERT INTO #temp VALUES ('01','003' ,0,0,1)
INSERT INTO #temp VALUES ('01','004' ,0,1,0)
INSERT INTO #temp VALUES ('02','001',0,0,1)
INSERT INTO #temp VALUES ('02','002',0,0,0)
INSERT INTO #temp VALUES ('02','003',0,1,0)
INSERT INTO #temp VALUES ('02','004',1,0,0)
INSERT INTO #temp VALUES ('03','001',1,0,0)
INSERT INTO #temp VALUES ('03','002',1,0,0)
INSERT INTO #temp VALUES ('03','003',0,0,0)
INSERT INTO #temp VALUES ('03','004',1,0,0)
INSERT INTO #temp VALUES ('04','001',0,1,0)
INSERT INTO #temp VALUES ('04','002',0,1,0)
INSERT INTO #temp VALUES ('04','003',1,0,0)
INSERT INTO #temp VALUES ('04','004',0,0,1)
INSERT INTO #temp VALUES ('05','001',1,0,0)
INSERT INTO #temp VALUES ('05','002',0,0,1)
INSERT INTO #temp VALUES ('05','003',0,0,0)
INSERT INTO #temp VALUES ('05','004',0,0,1)
INSERT INTO #temp VALUES ('06','001',0,0,0)
INSERT INTO #temp VALUES ('06','002',0,1,0)
INSERT INTO #temp VALUES ('06','003',0,1,0)
INSERT INTO #temp VALUES ('06','004',0,0,0)
INSERT INTO #temp VALUES ('07','001',0,1,0)
INSERT INTO #temp VALUES ('07','002',0,1,0)
INSERT INTO #temp VALUES ('07','003',1,0,0)
INSERT INTO #temp VALUES ('07','004',0,0,1)
INSERT INTO #temp VALUES ('08','001',0,0,1)
INSERT INTO #temp VALUES ('08','002',0,0,1)
INSERT INTO #temp VALUES ('08','003',1,0,0)
INSERT INTO #temp VALUES ('08','004',1,0,0)
INSERT INTO #temp VALUES ('08','005',0,0,1)
CREATE TABLE #temp2
(
consecutivo VARCHAR(20),
fecha datetime
)
INSERT INTO #temp2 VALUES ('01','2013-11-01')
INSERT INTO #temp2 VALUES ('02','2013-11-02')
INSERT INTO #temp2 VALUES ('03','2013-11-03')
INSERT INTO #temp2 VALUES ('04','2013-11-04')
INSERT INTO #temp2 VALUES ('05','2013-11-05')
INSERT INTO #temp2 VALUES ('06','2013-11-06')
INSERT INTO #temp2 VALUES ('07','2013-11-07')
INSERT INTO #temp2 VALUES ('08','2013-11-08')
DECLARE @inicial DATE
DECLARE @inicial2 DATE
DECLARE @final DATE
DECLARE @x INT
DECLARE @dias INT
DECLARE @pivot VARCHAR(MAX)
DECLARE @header VARCHAR(MAX)
SET @inicial='2013-11-01'
SET @inicial2=@inicial
SET @final='2013-11-08'
SET @dias=datediff(dd,@inicial,@final)
SET @pivot=''
SET @header=''
SET @x=1
WHILE @x<=@dias
BEGIN
SET @pivot=@pivot + '[' + REPLACE(CONVERT(VARCHAR(20),@inicial,111),'/','-') + '],'
SET @header=@header + 'sum(' + '[' + REPLACE(CONVERT(VARCHAR(20),@inicial,111),'/','-') + ']) as ' + '[' + REPLACE(CONVERT(VARCHAR(20),@inicial,111),'/','-') + '],'
SET @inicial=dateadd(dd,1,@inicial)
IF @dias=@x
BEGIN
SET @pivot=@pivot + '[' + REPLACE(CONVERT(VARCHAR(20),@inicial,111),'/','-') + ']'
SET @header=@header + 'sum(' + '[' + REPLACE(CONVERT(VARCHAR(20),@inicial,111),'/','-') + ']) as ' + '[' + REPLACE(CONVERT(VARCHAR(20),@inicial,111),'/','-') + ']'
END
SET @x=@x+1
END
DROP TABLE ##tabla1
DROP TABLE ##tabla2
DECLARE @query Nvarchar(MAX)
SET @query=
'SELECT trabajador,' + @header + '
into ##tabla1
FROM
(
SELECT trabajador,' + @pivot +' FROM
(
SELECT t1.consecutivo,t1.trabajador,
case
when labor1=1 then 1
when labor2=1 then 2
when labor3=1 then 3
else 4
end as labor, fecha
FROM #temp as t1
LEFT JOIN #temp2 AS t2 ON (t1.consecutivo=t2.consecutivo)
WHERE fecha BETWEEN ' + CHAR(39) + REPLACE(CONVERT(VARCHAR(20),@inicial2,111),'/','-') + CHAR(39) + ' and ' + CHAR(39) + REPLACE(CONVERT(VARCHAR(20),@final,111),'/','-') + CHAR(39) + '
) AS source
pivot
(
max(labor) FOR fecha IN (' + @pivot +')
) AS pvt
) AS t1 group by trabajador'
EXEC sp_executesql @query
SET @query=''
SET @query='
select sum(labor1) as labor1, sum(labor2) as labor2, sum(labor3) as labor3,sum(sin_labor) sin_labor, trabajador into ##tabla2 from(
select case when labor=1 then count(1) end as labor1,
case when labor=2 then count(1) end as labor2,
case when labor=3 then count(1) end as labor3,
case when labor=4 or isnull(labor,1)=1 then count(1) end as sin_labor,
consecutivo,trabajador,labor,fecha from(
SELECT t1.consecutivo,t1.trabajador,
case
when labor1=1 then 1
when labor2=1 then 2
when labor3=1 then 3
else 4
end as labor, fecha
FROM #temp as t1
LEFT JOIN #temp2 AS t2 ON (t1.consecutivo=t2.consecutivo)
WHERE fecha BETWEEN ' + CHAR(39) + REPLACE(CONVERT(VARCHAR(20),@inicial2,111),'/','-') + CHAR(39) + ' and ' + CHAR(39) + REPLACE(CONVERT(VARCHAR(20),@final,111),'/','-') + CHAR(39) + '
) as completa group by consecutivo,trabajador,labor,fecha
) as completa group by trabajador'
EXEC sp_executesql @query
SELECT * FROM ##tabla1 AS t1
LEFT JOIN ##tabla2 AS t2 ON (t1.trabajador=t2.trabajador)