Ok, gracias nuevamente, ya lo solucione, dentro del sql el order lo modifique por: order by 2,1,3'
Slds On Fri, May 21, 2010 at 12:53 PM, Dan <[email protected]> wrote: > Buenas Tardes, lo del crosstab ya 'funciona' pero tengo un inconveniente: > 1- cuando consulto por un solo idchofer sale correcto, idchofer, hora de > ingreso sale la minima, hora de salida sale la maxima > 2.- cuando consulto todos los choferes, como esta en el sql adjunto, > algunos valores salen invertidos la hora minima osea de ingreso sale > despues, > y la hora maxima de salida sale antes. > > como les comente en la tabla control un chofer puede registrar varios > ingresos y salidas > y si notan en la consulta que realizo primero busco la menor hora de > ingreso > y en el 2do query despues de la union busco la hora maxima > > SELECT * > FROM crosstab(' > select cast(c.idchofer, c.fecha, min(c.hora1) as hora > from control c > where c.fecha = ''20/05/2010'' and c.tipo=''INGRESO'' > group by tio.pers_id, tio.fecha > union all > select cast(c.idchofer, c.fecha, max(c.hora1) as hora > from control > where c.fecha = ''20/05/2010'' and c.tipo=''SALIDA'' > group by c.idchofer, c.fecha > order by 1,2' > ) > AS control(idchofer integer, ingreso time, salida time); > > > > On Fri, May 21, 2010 at 10:54 AM, Dan <[email protected]> wrote: > >> Upsss... Gracias a todos, no me habia percatado de ejecutar make y make >> install en el directorio */usr/src/postgresql-8.2.4/contrib/tablefunc/ >> y luego >> >> * >> path\to\postgresql\bin\psql -h localhost -U someuser -d somedb -f >> "path\to\postgresql\share\contrib\tablefunc.sql" >> >> Slds. >> >> On Fri, May 21, 2010 at 12:27 AM, Dan <[email protected]> wrote: >> >>> Hola Jaime, busque acerca de crosstab y encontre la sgt pagina: >>> http://www.postgresonline.com/journal/index.php?/archives/14-guid.html >>> >>> <http://www.postgresonline.com/journal/index.php?/archives/14-guid.html>el >>> paso que indica: path\to\postgresql\bin\psql -h localhost -U someuser -d >>> somedb -f "path\to\postgresql\share\contrib\tablefunc.sql" >>> en el postgresql 8.3 funciono perfecto, pero en el 8.2.4 me sale el sgt >>> mensaje: >>> >>> *HINT: No function matches the given name and argument types. You may >>> need to add explicit type casts.* >>> *-- recursion detection* >>> *INSERT INTO connectby_int VALUES(10,9);* >>> *INSERT 0 1* >>> *INSERT INTO connectby_int VALUES(11,10);* >>> *INSERT 0 1* >>> *INSERT INTO connectby_int VALUES(9,11);* >>> *INSERT 0 1* >>> *-- should fail due to infinite recursion* >>> *SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', >>> 0, '~') AS t(keyid int, parent_keyid int, level int, branch text);* >>> *psql:/usr/src/postgresql-8.2.4/contrib/tablefunc/sql/tablefunc.sql:178: >>> ERROR: function connectby("unknown", "unknown", "unknown", "unknown", >>> integer, "unknown") does not exist* >>> *LINE 1: SELECT * FROM connectby('connectby_int', 'keyid', 'parent_ke... >>> * >>> * ^* >>> *HINT: No function matches the given name and argument types. You may >>> need to add explicit type casts.* >>> *-- infinite recursion failure avoided by depth limit* >>> *SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', >>> 4, '~') AS t(keyid int, parent_keyid int, level int, branch text);* >>> *psql:/usr/src/postgresql-8.2.4/contrib/tablefunc/sql/tablefunc.sql:181: >>> ERROR: function connectby("unknown", "unknown", "unknown", "unknown", >>> integer, "unknown") does not exist* >>> *LINE 1: SELECT * FROM connectby('connectby_int', 'keyid', 'parent_ke... >>> * >>> * ^* >>> *HINT: No function matches the given name and argument types. You may >>> need to add explicit type casts.* >>> *-- test for falsely detected recursion* >>> *DROP TABLE connectby_int;* >>> *DROP TABLE* >>> *CREATE TABLE connectby_int(keyid int, parent_keyid int);* >>> *CREATE TABLE* >>> *INSERT INTO connectby_int VALUES(11,NULL);* >>> *INSERT 0 1* >>> *INSERT INTO connectby_int VALUES(10,11);* >>> *INSERT 0 1* >>> *INSERT INTO connectby_int VALUES(111,11);* >>> *INSERT 0 1* >>> *INSERT INTO connectby_int VALUES(1,111);* >>> *INSERT 0 1* >>> *-- this should not fail due to recursion detection* >>> *SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '11', >>> 0, '-') AS t(keyid int, parent_keyid int, level int, branch text);* >>> *psql:/usr/src/postgresql-8.2.4/contrib/tablefunc/sql/tablefunc.sql:191: >>> ERROR: function connectby("unknown", "unknown", "unknown", "unknown", >>> integer, "unknown") does not exist* >>> *LINE 1: SELECT * FROM connectby('connectby_int', 'keyid', 'parent_ke... >>> * >>> * ^* >>> *HINT: No function matches the given name and argument types. You may >>> need to add explicit type casts.* >>> >>> 2010/5/20 Jaime Casanova <[email protected]> >>> >>> 2010/5/20 Dan <[email protected]>: >>>> > >>>> > dias 19/05/2010 20/05/2010 >>>> > ------------------------------------------------------------------- >>>> > 1 | Juan Perez | 08:00-16:00 | 08:02-16:45 >>>> > 2 | Luis Rodriguez | 08:05-16:30 | 08:05-18:00 >>>> > 3 | Damian Diaz | 08:06-16:00 | 08:00-17:00 >>>> > >>>> > >>>> >>>> para hacerlo tal como esta en tu ejemplo necesitaras usar crosstab >>>> >>>> -- >>>> Jaime Casanova www.2ndQuadrant.com >>>> Soporte y capacitación de PostgreSQL >>>> >>> >>> >>> >>> -- >>> ----------------- >>> [) /-\ |\| >>> >> >> >> >> -- >> ----------------- >> [) /-\ |\| >> > > > > -- > ----------------- > [) /-\ |\| > -- ----------------- [) /-\ |\|
