[SOLUCIONADO] Tablas Pivoteadas con Multiples valores.

[khryztall]

Buenos días;

Hola amigos de foros del web, una vez mas acudo a uds para consultarles acerca de una duda que me asalta y me mortifica, y es la siguiente:

teniendo una tabla de trabajadores con estos datos:

consecutivo | trabajador | labor1|labor2|labor3|
01 | 001 | 1|0|0
01 | 002 | 0|1|0
01 | 003 | 0|0|1
01 | 004 | 0|1|0

consecutivo | trabajador | labor1|labor2|labor3|
02 | 001 | 0|0|1
02 | 002 | 0|0|1
02 | 003 | 0|1|0
02 | 004 | 1|0|0

consecutivo | trabajador | labor1|labor2|labor3|
03 | 001 | 1|0|0
03 | 002 | 1|0|0
03 | 003 | 1|0|0
03 | 004 | 1|0|0

consecutivo | trabajador | labor1|labor2|labor3|
04 | 001 | 0|1|0
04 | 002 | 0|1|0
04 | 003 | 1|0|0
04 | 004 | 0|0|1

Y una tabla que guarda las fechas

consecutivo | fecha
01 | 2013-11-01
02 | 2013-11-02
03 | 2013-11-03
04 | 2013-11-04

La idea es que al enviar el rango de fechas 2013-11-01 al 2013-11-04

el resultado sea el siguiente, donde el valor en la fecha indica la labor que realizo ese día

trabajador | 2013-11-01 |2013-11-02 |2013-11-03 |2013-11-04
001 | 1 | 3 | 1 | 2
002 | 2 | 3 | 1 | 2
003 | 3 | 2 | 1 | 1
004 | 2 | 1 | 1 | 3

En una anterior consulta me recomendaron usar PIVOT y me funcionó pero en esta ocasión necesito saber que labor se realizo ese día sabiendo que solo se puede realizar una labor por día, osea que en un mismo día no habrán dos labores.

Ademas determinar que el rango de la fecha es variable asi que pueden ser desde 1 hasta 100.

Agradezco cualquier ayuda q me puedan brindar o documentación para instruirme, en este tema.

[Libras]

Código SQL:

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,1)
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',1,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)
 
 
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')
 
 
 
SELECT trabajador,SUM([2013-11-01]) AS [2013-11-01],SUM([2013-11-02]) [2013-11-02],SUM([2013-11-03]) [2013-11-03],SUM([2013-11-04]) [2013-11-04] 
FROM
(
    SELECT trabajador,[2013-11-01],[2013-11-02],[2013-11-03],[2013-11-04] FROM
    (
    SELECT t1.consecutivo,t1.trabajador,
    CASE WHEN labor1=1 THEN 1 
    WHEN labor2=1 THEN 2 
    WHEN labor3=1 THEN 3 END AS labor,
    fecha FROM #temp AS t1
    LEFT JOIN #temp2 AS t2 ON (t1.consecutivo=t2.consecutivo)
    WHERE fecha BETWEEN '2013-11-01' AND '2013-11-04'
    ) AS SOURCE
    pivot
    (
    MAX(labor) FOR fecha IN ([2013-11-01],[2013-11-02],[2013-11-03],[2013-11-04])
    ) AS pvt 
) AS t1 GROUP BY trabajador
 

Para la parte de las fechas, se puede lograr con un query dinamico tomando en cuenta la diferencia de dias entre la fecha inicial y la fecha final y sacando las diferentes fechas con un ciclo para poder armar la cadena como quieres :P pero eso si ya seria por tu cuenta o con costo($$$$) adicional(se aceptan pagos por paypal :P)

[khryztall]

Me mato la parte del paypal, jajajaj bueno la idea del jquery no es aceptable ya que podria tener el mismo efecto con un for y la idea es no saturar la red sino dar toda carga al servidor, aunque tu solucion, me parece muy factible tenia la idea de hacerlo con un case, pero utilizar un max si me cogio de sorpresa, gracias por tu ayuda.

[Libras]

aqui esta la solucion con un query dinamico, aclaro el query dinamico nada mas es para obtener los valores de las columnas a pivotear, no ejecutaras el query 1 vez cada dia....y como es cadena se obtiene rapido y no consume mucho el servidor, lo probe en mi server local y para obtener las fechas hasta el 30 de abril del 2014 tardo 2 segundos :P

Código SQL:

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,1)
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',1,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,1)
INSERT INTO #temp VALUES ('05','002',1,1,1)
INSERT INTO #temp VALUES ('05','003',1,1,0)
INSERT INTO #temp VALUES ('05','004',1,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',1,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,1,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 datetime
DECLARE @inicial2 datetime
DECLARE @final datetime
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
 
 
DECLARE @query Nvarchar(MAX)
SET @query='select trabajador,' + @header + '
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 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
 

saludos!

P.S: Lo del paypal no es broma......