Foros del Web » Programación para mayores de 30 ;) » Bases de Datos General » SQL Server »

[SOLUCIONADO] Sumatoria de valores en tabla con PIVOT

 Estas en el tema de Sumatoria de valores en tabla con PIVOT en el foro de SQL Server en Foros del Web. Buenas tardes; Amigos del Foros del Web, nuevamente vengo con inquietudes hace algun tiempo he venido aprendiendo bases de datos en SERVER SQL, y he ...
  #1 (permalink)  
Antiguo 11/12/2013, 13:43
 
Fecha de Ingreso: mayo-2011
Mensajes: 47
Antigüedad: 13 años, 5 meses
Puntos: 1
Pregunta Sumatoria de valores en tabla con PIVOT
Buenas tardes;

Amigos del Foros del Web, nuevamente vengo con inquietudes hace algun tiempo he venido aprendiendo bases de datos en SERVER SQL, y he publicado algunas preguntas que muy amablemente uds me han respondido especialmente el usuario libras, (muchas gracias por su ayuda).

Como dije he venido aprendiendo de tablas pivote en SERVER SQL pero hoy cuando intente sumar los valores resultados de una tabla , descubri que no puedo sumar estos resultados ya que me arroja 1 lo cual no es el resultado de la operacion. me explico tengo las siguientes tablas:

Una tabla que guarda un consecutivo un codigo de trabajador y unas novedades que pueden ser del tipo labor1, labor2 o labor3

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|0
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 | 0|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

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

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

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

consecutivo | trabajador |labor1|labor2|labor3
08 | 001 | 0|0|1
08 | 002 | 0|0|1
08 | 003 | 1|0|0
08 | 004 | 1|0|0
08 | 005 | 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
05 | 2013-11-05
06 | 2013-11-06
07 | 2013-11-07
08 | 2013-11-08

Lo que hace en el momento es que al enviar el rango de fechas 2013-11-01 al 2013-11-08

el resultado sea el siguiente

trabajador |2013-11-01 |2013-11-02 |2013-11-03 |2013-11-04 |2013-11-05 |2013-11-06 |2013-11-07 |2013-11-08
001|1| 3| 1| 2| 1| 4| 2| 3|
002|2| 4| 1| 2| 3| 2| 2| 3|
003|3| 2| 4| 1| 4| 2| 1| 1|
004|2| 1| 1| 3| 3| 4| 3| 1|
005|NULL| NULL| NULL| NULL| NULL| NULL| NULL| 3

La manera en la que lo hace es la siguiente:
Código SQL: 
Ver original
  1. DROP TABLE #temp
  2. DROP TABLE #temp2
  3.  
  4. CREATE TABLE #temp
  5.     (
  6.         consecutivo VARCHAR(20),
  7.         trabajador VARCHAR(20),
  8.         labor1 INT,
  9.         labor2 INT,
  10.         labor3 INT
  11.     )
  12.  
  13. INSERT INTO #temp VALUES ('01','001' ,1,0,0)
  14. INSERT INTO #temp VALUES ('01','002' ,0,1,0)
  15. INSERT INTO #temp VALUES ('01','003' ,0,0,1)
  16. INSERT INTO #temp VALUES ('01','004' ,0,1,0)
  17.   
  18. INSERT INTO #temp VALUES ('02','001',0,0,1)
  19. INSERT INTO #temp VALUES ('02','002',0,0,0)
  20. INSERT INTO #temp VALUES ('02','003',0,1,0)
  21. INSERT INTO #temp VALUES ('02','004',1,0,0)
  22.   
  23. INSERT INTO #temp VALUES ('03','001',1,0,0)
  24. INSERT INTO #temp VALUES ('03','002',1,0,0)
  25. INSERT INTO #temp VALUES ('03','003',0,0,0)
  26. INSERT INTO #temp VALUES ('03','004',1,0,0)
  27.   
  28. INSERT INTO #temp VALUES ('04','001',0,1,0)
  29. INSERT INTO #temp VALUES ('04','002',0,1,0)
  30. INSERT INTO #temp VALUES ('04','003',1,0,0)
  31. INSERT INTO #temp VALUES ('04','004',0,0,1)
  32.  
  33. INSERT INTO #temp VALUES ('05','001',1,0,0)
  34. INSERT INTO #temp VALUES ('05','002',0,0,1)
  35. INSERT INTO #temp VALUES ('05','003',0,0,0)
  36. INSERT INTO #temp VALUES ('05','004',0,0,1)
  37.   
  38. INSERT INTO #temp VALUES ('06','001',0,0,0)
  39. INSERT INTO #temp VALUES ('06','002',0,1,0)
  40. INSERT INTO #temp VALUES ('06','003',0,1,0)
  41. INSERT INTO #temp VALUES ('06','004',0,0,0)
  42.   
  43. INSERT INTO #temp VALUES ('07','001',0,1,0)
  44. INSERT INTO #temp VALUES ('07','002',0,1,0)
  45. INSERT INTO #temp VALUES ('07','003',1,0,0)
  46. INSERT INTO #temp VALUES ('07','004',0,0,1)
  47.  
  48. INSERT INTO #temp VALUES ('08','001',0,0,1)
  49. INSERT INTO #temp VALUES ('08','002',0,0,1)
  50. INSERT INTO #temp VALUES ('08','003',1,0,0)
  51. INSERT INTO #temp VALUES ('08','004',1,0,0)
  52. INSERT INTO #temp VALUES ('08','005',0,0,1)
  53.   
  54. CREATE TABLE #temp2
  55.     (
  56.         consecutivo VARCHAR(20),
  57.         fecha datetime
  58.     )
  59.  
  60. INSERT INTO #temp2 VALUES ('01','2013-11-01')
  61. INSERT INTO #temp2 VALUES ('02','2013-11-02')
  62. INSERT INTO #temp2 VALUES ('03','2013-11-03')
  63. INSERT INTO #temp2 VALUES ('04','2013-11-04')
  64. INSERT INTO #temp2 VALUES ('05','2013-11-05')
  65. INSERT INTO #temp2 VALUES ('06','2013-11-06')
  66. INSERT INTO #temp2 VALUES ('07','2013-11-07')
  67. INSERT INTO #temp2 VALUES ('08','2013-11-08')
  68.  
  69. DECLARE @inicial DATE
  70. DECLARE @inicial2 DATE
  71. DECLARE @final DATE
  72. DECLARE @x INT
  73. DECLARE @dias INT
  74. DECLARE @pivot VARCHAR(MAX)
  75. DECLARE @header VARCHAR(MAX)
  76.  
  77. SET @inicial='2013-11-01'
  78. SET @inicial2=@inicial
  79. SET @final='2013-11-08'
  80. SET @dias=datediff(dd,@inicial,@final)
  81.  
  82. SET @pivot=''
  83. SET @header=''
  84. SET @x=1
  85. WHILE @x<=@dias
  86.     BEGIN
  87.     SET @pivot=@pivot + '[' + REPLACE(CONVERT(VARCHAR(20),@inicial,111),'/','-') + '],'
  88.     SET @header=@header + 'sum(' + '[' + REPLACE(CONVERT(VARCHAR(20),@inicial,111),'/','-') + ']) as ' + '[' + REPLACE(CONVERT(VARCHAR(20),@inicial,111),'/','-') + '],'
  89.     SET @inicial=dateadd(dd,1,@inicial)
  90.         IF @dias=@x
  91.      BEGIN
  92.          SET @pivot=@pivot + '[' + REPLACE(CONVERT(VARCHAR(20),@inicial,111),'/','-') + ']'
  93.          SET @header=@header + 'sum(' + '[' + REPLACE(CONVERT(VARCHAR(20),@inicial,111),'/','-') + ']) as ' + '[' + REPLACE(CONVERT(VARCHAR(20),@inicial,111),'/','-') + ']'
  94.      END
  95.     SET @x=@x+1
  96.     END
  97.  
  98. DECLARE @query Nvarchar(MAX)
  99. SET @query=
  100. 'SELECT trabajador,' + @header + '
  101. FROM
  102.     (
  103.         SELECT trabajador,' + @pivot +' FROM
  104.             (
  105.                 SELECT t1.consecutivo,t1.trabajador,
  106.                     case 
  107.                  when labor1=1 then 1 
  108.                         when labor2=1 then 2 
  109.                         when labor3=1 then 3 
  110.                         else 4
  111.                     end as labor, fecha 
  112.                 FROM #temp as t1
  113.                 LEFT JOIN #temp2 AS t2 ON (t1.consecutivo=t2.consecutivo)
  114.                 WHERE fecha BETWEEN ' + CHAR(39) + REPLACE(CONVERT(VARCHAR(20),@inicial2,111),'/','-') + CHAR(39) + ' and ' + CHAR(39) +  REPLACE(CONVERT(VARCHAR(20),@final,111),'/','-') + CHAR(39) + '
  115.             ) AS source
  116.         pivot
  117.         (
  118.         max(labor) FOR fecha IN (' + @pivot +')
  119.         ) AS pvt 
  120.     ) AS t1 group by trabajador'
  121.   
  122. EXEC sp_executesql @query

El problema radica en querer saber cuantas veces el trabajador realiza las diferentes labores, en resumen lo que busco es que el resultado sea el siguiente:

trabajador |2013-11-01 |2013-11-02 |2013-11-03 |2013-11-04 |2013-11-05 |2013-11-06 |2013-11-07 |2013-11-08|labor1|labor2|labor3|sinlabor|
001 |1|3|1|2|1|4|2|3|3|2|2|1
002 |2|4|1|2|3|2|2|1|2|3|1|1
003 |3|2|4|1|4|2|1|1|3|2|0|2
004 |2|1|1|3|3|4|3|1|3|0|3|1
005 |N|N|N|N|N|N|N|3|0|0|1|4

Al ingresar bien sea la funcion count o la funcion sum en alguna de las labores me arroja como resultado 1, lo cual es es un fallo.

Agradezco la ayuda que me puedan brindar con respecto a este tema.
  #2 (permalink)  
Antiguo 11/12/2013, 14:57
Avatar de Libras
Colaborador
  
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.412
Antigüedad: 18 años, 3 meses
Puntos: 774
Respuesta: Sumatoria de valores en tabla con PIVOT
Código SQL: 
Ver original
  1. DROP TABLE #temp
  2. DROP TABLE #temp2
  3.  
  4. CREATE TABLE #temp
  5.     (
  6.         consecutivo VARCHAR(20),
  7.         trabajador VARCHAR(20),
  8.         labor1 INT,
  9.         labor2 INT,
  10.         labor3 INT
  11.     )
  12.  
  13. INSERT INTO #temp VALUES ('01','001' ,1,0,0)
  14. INSERT INTO #temp VALUES ('01','002' ,0,1,0)
  15. INSERT INTO #temp VALUES ('01','003' ,0,0,1)
  16. INSERT INTO #temp VALUES ('01','004' ,0,1,0)
  17.   
  18. INSERT INTO #temp VALUES ('02','001',0,0,1)
  19. INSERT INTO #temp VALUES ('02','002',0,0,0)
  20. INSERT INTO #temp VALUES ('02','003',0,1,0)
  21. INSERT INTO #temp VALUES ('02','004',1,0,0)
  22.   
  23. INSERT INTO #temp VALUES ('03','001',1,0,0)
  24. INSERT INTO #temp VALUES ('03','002',1,0,0)
  25. INSERT INTO #temp VALUES ('03','003',0,0,0)
  26. INSERT INTO #temp VALUES ('03','004',1,0,0)
  27.   
  28. INSERT INTO #temp VALUES ('04','001',0,1,0)
  29. INSERT INTO #temp VALUES ('04','002',0,1,0)
  30. INSERT INTO #temp VALUES ('04','003',1,0,0)
  31. INSERT INTO #temp VALUES ('04','004',0,0,1)
  32.  
  33. INSERT INTO #temp VALUES ('05','001',1,0,0)
  34. INSERT INTO #temp VALUES ('05','002',0,0,1)
  35. INSERT INTO #temp VALUES ('05','003',0,0,0)
  36. INSERT INTO #temp VALUES ('05','004',0,0,1)
  37.   
  38. INSERT INTO #temp VALUES ('06','001',0,0,0)
  39. INSERT INTO #temp VALUES ('06','002',0,1,0)
  40. INSERT INTO #temp VALUES ('06','003',0,1,0)
  41. INSERT INTO #temp VALUES ('06','004',0,0,0)
  42.   
  43. INSERT INTO #temp VALUES ('07','001',0,1,0)
  44. INSERT INTO #temp VALUES ('07','002',0,1,0)
  45. INSERT INTO #temp VALUES ('07','003',1,0,0)
  46. INSERT INTO #temp VALUES ('07','004',0,0,1)
  47.  
  48. INSERT INTO #temp VALUES ('08','001',0,0,1)
  49. INSERT INTO #temp VALUES ('08','002',0,0,1)
  50. INSERT INTO #temp VALUES ('08','003',1,0,0)
  51. INSERT INTO #temp VALUES ('08','004',1,0,0)
  52. INSERT INTO #temp VALUES ('08','005',0,0,1)
  53.   
  54. CREATE TABLE #temp2
  55.     (
  56.         consecutivo VARCHAR(20),
  57.         fecha datetime
  58.     )
  59.  
  60. INSERT INTO #temp2 VALUES ('01','2013-11-01')
  61. INSERT INTO #temp2 VALUES ('02','2013-11-02')
  62. INSERT INTO #temp2 VALUES ('03','2013-11-03')
  63. INSERT INTO #temp2 VALUES ('04','2013-11-04')
  64. INSERT INTO #temp2 VALUES ('05','2013-11-05')
  65. INSERT INTO #temp2 VALUES ('06','2013-11-06')
  66. INSERT INTO #temp2 VALUES ('07','2013-11-07')
  67. INSERT INTO #temp2 VALUES ('08','2013-11-08')
  68.  
  69. DECLARE @inicial DATE
  70. DECLARE @inicial2 DATE
  71. DECLARE @final DATE
  72. DECLARE @x INT
  73. DECLARE @dias INT
  74. DECLARE @pivot VARCHAR(MAX)
  75. DECLARE @header VARCHAR(MAX)
  76.  
  77. SET @inicial='2013-11-01'
  78. SET @inicial2=@inicial
  79. SET @final='2013-11-08'
  80. SET @dias=datediff(dd,@inicial,@final)
  81.  
  82. SET @pivot=''
  83. SET @header=''
  84. SET @x=1
  85. WHILE @x<=@dias
  86.     BEGIN
  87.     SET @pivot=@pivot + '[' + REPLACE(CONVERT(VARCHAR(20),@inicial,111),'/','-') + '],'
  88.     SET @header=@header + 'sum(' + '[' + REPLACE(CONVERT(VARCHAR(20),@inicial,111),'/','-') + ']) as ' + '[' + REPLACE(CONVERT(VARCHAR(20),@inicial,111),'/','-') + '],'
  89.     SET @inicial=dateadd(dd,1,@inicial)
  90.         IF @dias=@x
  91.      BEGIN
  92.          SET @pivot=@pivot + '[' + REPLACE(CONVERT(VARCHAR(20),@inicial,111),'/','-') + ']'
  93.          SET @header=@header + 'sum(' + '[' + REPLACE(CONVERT(VARCHAR(20),@inicial,111),'/','-') + ']) as ' + '[' + REPLACE(CONVERT(VARCHAR(20),@inicial,111),'/','-') + ']'
  94.      END
  95.     SET @x=@x+1
  96.     END
  97.  
  98. DROP TABLE ##tabla1
  99. DROP TABLE ##tabla2
  100.  
  101.  
  102. DECLARE @query Nvarchar(MAX)
  103. SET @query=
  104. 'SELECT trabajador,' + @header + '
  105. into ##tabla1
  106. FROM
  107.     (
  108.         SELECT trabajador,' + @pivot +' FROM
  109.             (
  110.                 SELECT t1.consecutivo,t1.trabajador,
  111.                     case 
  112.                  when labor1=1 then 1 
  113.                         when labor2=1 then 2 
  114.                         when labor3=1 then 3 
  115.                         else 4
  116.                     end as labor, fecha 
  117.                 FROM #temp as t1
  118.                 LEFT JOIN #temp2 AS t2 ON (t1.consecutivo=t2.consecutivo)
  119.                 WHERE fecha BETWEEN ' + CHAR(39) + REPLACE(CONVERT(VARCHAR(20),@inicial2,111),'/','-') + CHAR(39) + ' and ' + CHAR(39) +  REPLACE(CONVERT(VARCHAR(20),@final,111),'/','-') + CHAR(39) + '
  120.             ) AS source
  121.         pivot
  122.         (
  123.         max(labor) FOR fecha IN (' + @pivot +')
  124.         ) AS pvt 
  125.     ) AS t1 group by trabajador'
  126.  
  127. EXEC sp_executesql @query
  128.  
  129. SET @query=''
  130. SET @query='
  131.     select sum(labor1) as labor1, sum(labor2) as labor2, sum(labor3) as labor3,sum(sin_labor) sin_labor, trabajador into ##tabla2 from(
  132.             select case when labor=1 then count(1) end as labor1,   
  133.             case when labor=2 then count(1) end as labor2,
  134.             case when labor=3 then count(1) end as labor3,
  135.             case when labor=4 or isnull(labor,1)=1 then count(1) end as sin_labor,
  136.             consecutivo,trabajador,labor,fecha from(
  137.                 SELECT t1.consecutivo,t1.trabajador,
  138.                     case 
  139.                  when labor1=1 then 1 
  140.                         when labor2=1 then 2 
  141.                         when labor3=1 then 3 
  142.                         else 4
  143.                     end as labor, fecha 
  144.                 FROM #temp as t1
  145.                 LEFT JOIN #temp2 AS t2 ON (t1.consecutivo=t2.consecutivo)
  146.             WHERE fecha BETWEEN ' + CHAR(39) + REPLACE(CONVERT(VARCHAR(20),@inicial2,111),'/','-') + CHAR(39) + ' and ' + CHAR(39) +  REPLACE(CONVERT(VARCHAR(20),@final,111),'/','-') + CHAR(39) + '
  147.     ) as completa group by consecutivo,trabajador,labor,fecha
  148. ) as completa group by trabajador'
  149.  
  150. EXEC sp_executesql @query
  151.  
  152. SELECT * FROM ##tabla1 AS t1
  153. LEFT JOIN  ##tabla2 AS t2 ON (t1.trabajador=t2.trabajador)

saludos!
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me
  #3 (permalink)  
Antiguo 11/12/2013, 16:30
Avatar de Libras
Colaborador
  
Fecha de Ingreso: agosto-2006
Ubicación: En la hermosa perla de occidente
Mensajes: 7.412
Antigüedad: 18 años, 3 meses
Puntos: 774
Respuesta: Sumatoria de valores en tabla con PIVOT
sirvio no sirvio...........
__________________
What does an execution plan say to t-sql query? Go f**k yourself, if you are not happy with me
  #4 (permalink)  
Antiguo 12/12/2013, 07:21
 
Fecha de Ingreso: mayo-2011
Mensajes: 47
Antigüedad: 13 años, 5 meses
Puntos: 1
Respuesta: Sumatoria de valores en tabla con PIVOT
Era exactamente lo que buscaba muchas, gracias, cada vez me doy cuante de lo poco que se y de lo mucho que me falta por aprender.

Etiquetas: fecha, join, pivot, select, server, sql, sumatoria, tabla
Atención: Estás leyendo un tema que no tiene actividad desde hace más de 6 MESES, te recomendamos abrir un Nuevo tema en lugar de responder al actual.
Respuesta


« Tema Anterior | Próximo Tema »


La zona horaria es GMT -6. Ahora son las 11:05.