Trasnformadas, crosstab, pivot, referencias cruzadas ...

[MDante]

Hola fjcg02 soy un novato en programación de bases de datos hace tiempo que busco resolver el problema de presentar información de esta manera

Clavealumno     horaentrada           diames1   diames2   diames3   diames4   hasta el dia 31 del X mes

1                 8:30 am             8:25am    8:20am    8:50 am   9:28 am   ----------------------->

he utilizado el fragmento de código que compartiste sin embargo hasta el momento no logro hacerlo funcionar mi versión de firebird es la 2.1 y la ejecuto tanto en ibexpert como en firebird maestro y el resultado es el mismo ingrese los datos como sugieres el archivo de word en una tabla llamada testing con columnas llamadas NOMBRE, FECHA, TIPO, IMPORTE y los importe a la base de datos y cada vez que ejecuto el procedimiento desde cualquiera de los dos programas mencionados con los parametros

(TABLA = TESTING )( CAMPO_VISUALIZAR = NOMBRE)( CAMPO_PIVOTE = FECHA) (CONDICIONES = VACIO)

y el resultado no importa que combinación de parámetros use el error siempre es:
Engine Error (code = 335544569):
Dynamic SQL Error.
SQL error code = -206.
Column unknown.
TESTING.
At line 2, column 21.

SQL Error (code = -206):
Column does not belong to referenced table

aunque esto solo era para un proyectito para ganar algunos puntos en la escuela los cuales ya no me gane jeje me esta volviendo loco ya que dure varios días intentando resolver este problema, MS SQL ya lo hace pero me siento a un paso de lograrlo en firebird por eso sigo persistiendo.

tambien he utilizado este tipo de query pero solo me devuelve valores 1 y 0 aunque si con una elegante tabla como yo necesito, esto es en la copia de la base de datos que nos da la escuela
la cual tiene como estructura: TABLA ASISTENCIA
COLUMNAS CLAVEALUMNO(INT) ENTRADA(TIMESTAMP) REGISTROENTRADA(TIMESTAMP)

Código SQL [-]
select distinct 
    clavealumno as clave,
    count( registroentrada) as entrada,
    count( case when extract (day from entrada) = 1 then cast(asistencia.REGISTROENTRADA as date) end ) as dia1,
    count( case when extract (day from entrada) = 2 then cast(entrada as date) end ) as dia2,
    count( case when extract (day from entrada) = 3 then cast(entrada as date) end ) dia3,
    count( case when extract (day from entrada) = 4 then cast(entrada as date) end ) dia4,
    count( case when extract (day from entrada) = 5 then cast(entrada as date) end ) dia5,
    count( case when extract (day from entrada) = 6 then cast(entrada as date) end ) dia6,
    count( case when extract (day from entrada) = 7 then cast(entrada as date) end ) dia7,
    count( case when extract (day from entrada) = 8 then cast(entrada as date) end ) dia8,
    count( case when extract (day from entrada) = 9 then cast(entrada as date) end ) dia9,
    count( case when extract (day from entrada) = 10 then cast(entrada as date) end ) dia10,
    count( case when extract (day from entrada) = 11 then cast(entrada as date) end ) dia11,
    count( case when extract (day from entrada) = 12 then cast(entrada as date) end ) dia12,
    count( case when extract (day from entrada) = 13 then cast(entrada as date) end ) dia13,
    count( case when extract (day from entrada) = 14 then cast(entrada as date) end ) dia14,
    count( case when extract (day from entrada) = 15 then cast(entrada as date) end ) dia15
from asistencia
where 
   (
      (clavealumno < 400)
   and 
      (registroentrada between cast('10/1/2010'as date) and cast('10/15/2010' as date))
   )
group by clavealumno
order by 1

ojala alguien mas que vea el tema aporte sus valiosos conocimientos para lograr hacer un query funcional ya que en ningún lado de la red alguien a compartido algo similar

les dejo una copia de la tabla de la base de datos con información real de entre octubre de 2009 hasta octubre 2011 aprox sobre clave, horario de entrada y registroentrada

https://skydrive.live.com/redir?resi...JwNTFW0rK0bA00

saludos

[Casimiro Notevi]

Bienvenido a clubdelphi, ¿ya leiste nuestra guía de estilo?, gracias por tu colaboración

Recuerda poner los tags al código fuente, ejemplo:



Gracias

[[fjcg02]]

Hola MDante,
vamos por partes porque estás confundiendo algunos términos...

Código SQL [-]
...
    count( registroentrada) as entrada,
    count( case when extract (day from entrada) = 1 then cast(asistencia.REGISTROENTRADA as date) end ) as dia1,
    count( case when extract (day from entrada) = 2 then cast(entrada as date) end ) as dia2,
    count( case when extract (day from entrada) = 3 then cast(entrada as date) end ) dia3,
    count( case when extract (day from entrada) = 4 then cast(entrada as date) end ) dia4,
    count( case when extract (day from entrada) = 5 then cast(entrada as date) end ) dia5,
    count( case when extract (day from entrada) = 6 then cast(entrada as date) end ) dia6,
    count( case when extract (day from entrada) = 7 then cast(entrada as date) end ) dia7,
    count( case when extract (day from entrada) = 8 then cast(entrada as date) end ) dia8,
    count( case when extract (day from entrada) = 9 then cast(entrada as date) end ) dia9,
    count( case when extract (day from entrada) = 10 then cast(entrada as date) end ) dia10,
    count( case when extract (day from entrada) = 11 then cast(entrada as date) end ) dia11,
    count( case when extract (day from entrada) = 12 then cast(entrada as date) end ) dia12,
    count( case when extract (day from entrada) = 13 then cast(entrada as date) end ) dia13,
    count( case when extract (day from entrada) = 14 then cast(entrada as date) end ) dia14,
    count( case when extract (day from entrada) = 15 then cast(entrada as date) end ) dia15
...

Piensa lo que estás haciendo... count "cuenta" las veces que aparece, por lo tanto, te saldrán como valores 1 ó cero, ya que son las veces que aparecen registros con esos datos. Entiendo que en tu tabla hay un registro por día.
Si hay un registro por día te valdría sustituir el count por first o last, y supongo que te saldría el resultado que esperas, es decir, la hora de entrada de cada día.

Respecto a la función que publiqué, le falta un parámetro, que es el campo a sumar. Supongo que es lo que está mal.
Si no recuerdo mal, el procedimiento genera una sentencia sql. Haz pruebas y pon los parámetros de entrada y la sentencia que genera en cada caso, para que pueda analizarlas. A ver si puedo ponerte algo de luz en el asunto.

Estamos en contacto.

Saludos

[MDante]

Primero que nada, gracias por tomarte la molestia de responder , en efecto el código que tu nos ofreces funciona perfectamente cuando hay campos a sumar lo único que me faltaba hacer era colocar los campos entre apostrofes , sin embargo yo no tengo ningún campo a sumar, aunque de todas formas me funciona de maravilla para otras cosas, por otra parte por supuesto que se que el valor devuelto de count es un valor nonull y debido a mi inexperiencia esperaba encontrar alguna sentencia en alguna parte de Internet que me devolviera el campo que necesito, que son las fechas en columnas, sin embargo también funciona como lista de asistencia, después de todo ese era el proyecto que tenia por encargo, desplegar en una lista las asistencias de los alumnos por cada día del mes, y ya que devuelve valores nonull, en las fechas donde no hay asistencia devuelve un cero y donde hay asistencia devuelve un hermoso 1 , me habría gustado mas que apareciera la hora por cada día, pero ya que no logro hacer eso ni con transform y pivot en MS SQL llegue a la conclusión de que mejor voy a desplegar la información reacomodandola mediante uso de matrices y después enviarlos a un dataset o un datatable ya que yo uso C# soy nuevo en rad studio y mi versión caducará pronto pero me encanto trabajar en rad studio cuando logre hacerlo prometo publicar un prototipo para quien quiera usarlo tanto en C# como en delphi.


Saludos y hasta pronto

[MDante]

Por cierto olvide poner el query que me devuelve unos y ceros jeje

Código Delphi [-]
select distinct 
    clavealumno as clave,
    count( registroentrada) as entrada,
    count( case when cast (entrada as date) = '10/01/2010' then 0 end ) as dia1,
    count( case when cast (entrada as date) = '10/02/2010' then 0 end ) as dia2,
    count( case when cast (entrada as date) = '10/03/2010' then 0 end ) as dia3,
    count( case when cast (entrada as date) = '10/04/2010' then 0 end ) as dia4,
    count( case when cast (entrada as date) = '10/05/2010' then 0 end ) as dia5,
    count( case when cast (entrada as date) = '10/06/2010' then 0 end ) as dia6,
    count( case when cast (entrada as date) = '10/07/2010' then 0 end ) as dia7,
    count( case when cast (entrada as date) = '10/08/2010' then 0 end ) as dia8,
    count( case when cast (entrada as date) = '10/09/2010' then 0 end ) as dia9,
    count( case when cast (entrada as date) = '10/10/2010' then 0 end ) as dia10,
    count( case when cast (entrada as date) = '10/11/2010' then 0 end ) as dia11,
    count( case when cast (entrada as date) = '10/12/2010' then 0 end ) as dia12,
    count( case when cast (entrada as date) = '10/13/2010' then 0 end ) as dia13,
    count( case when cast (entrada as date) = '10/14/2010' then 0 end ) as dia14,
    count( case when cast (entrada as date) = '10/15/2010' then 0 end ) as dia15
from asistencia
where 
   (
      (clavealumno < 400)
   and 
      (registroentrada between cast('10/1/2010'as date) and cast('10/15/2010' as date))
   )
group by clavealumno
order by 1

[[fjcg02]]

A ver, hombre de poca fe...

Vas a probar con esto, que significa básicamente lo siguiente...

Cogeme las fechas de entrada de los alumnos y haces esto
- si es el día uno, coges la hora y la conviertes a caracteres. Si no es día uno te quedas con el valor '00:00'. Te quedas con el valor más alto ( es decir, el que tenga un valor ).
- Así para cada día.

Además, me lo agrupas por alumno.

Código SQL [-]
select clavealumno as clave,
 max( case when extract(day from entrada) = 1 then cast(cast(entrada as time) as varchar(25)) else '00:00' end) as "DIA 1" ,
 max( case when extract(day from entrada) = 2 then cast(cast(entrada as time) as varchar(25)) else '00:00' end) as "DIA 2" ,
 max( case when extract(day from entrada) = 3 then cast(cast(entrada as time) as varchar(25)) else '00:00' end) as "DIA 3" ,
 max( case when extract(day from entrada) = 4 then cast(cast(entrada as time) as varchar(25)) else '00:00' end) as "DIA 4" ,
 max( case when extract(day from entrada) = 5 then cast(cast(entrada as time) as varchar(25)) else '00:00' end) as "DIA 5" ,
 max( case when extract(day from entrada) = 6 then cast(cast(entrada as time) as varchar(25)) else '00:00' end) as "DIA 6" ,
 max( case when extract(day from entrada) = 7 then cast(cast(entrada as time) as varchar(25)) else '00:00' end) as "DIA 7" ,
 max( case when extract(day from entrada) = 8 then cast(cast(entrada as time) as varchar(25)) else '00:00' end) as "DIA 8" ,
 max( case when extract(day from entrada) = 9 then cast(cast(entrada as time) as varchar(25)) else '00:00' end) as "DIA 9" ,
 max( case when extract(day from entrada) = 10 then cast(cast(entrada as time) as varchar(25)) else '00:00' end) as "DIA 10" ,
 max( case when extract(day from entrada) = 11 then cast(cast(entrada as time) as varchar(25)) else '00:00' end) as "DIA 11" ,
 max( case when extract(day from entrada) = 12 then cast(cast(entrada as time) as varchar(25)) else '00:00' end) as "DIA 12" ,
 max( case when extract(day from entrada) = 13 then cast(cast(entrada as time) as varchar(25)) else '00:00' end) as "DIA 13"

 from asistencia
where 
   (
      (clavealumno < 400)
   and 
      (registroentrada between cast('10/1/2010'as date) and cast('10/15/2010' as date))
   )
group by clavealumno

Lo pruebas, si funciona perfecto, pero te dejo de deberes que lo estudies y lo entiendas.

Nos informas, majete.

Saludos
PD: A ver si te ganas esa buena nota, aunque sea tarde.