none
De Excel a SQL [AUXILIO] RRS feed

  • Pregunta

  • Buenas, pueden decirme kj para que sea sencillo.
    o nombre de usuario. 

    Trabajo para una empresa de venta al mayoreo y menudeo de refacciones automotrices. 
    Ejerzo el puesto de inventarista. Sin embargo me encuentro a menudo haciendo tablas de excel para mis superiores ya que tengo experiencia en estas y nuestro sistema de ventas carece de ciertas opciones que los superiores desean. 

    Recientemente me pidieron una tabla donde pudiese separarles ciertas ventas dependiendo de las unidades de negocio tomando en cuenta el costo de cada parte. Las partes estan categorisadas con "lineas" y las facturas no distinguen entre lineas o partes, ya que unas lineas se toman en cuenta como mano de obra.

    Toda esta informacion esta en una base de datos la cual yo consulto con este SQL:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT 	 	titm_0.sid, tkt_0.inum, tkt_0.type, ct_0.logname, titm_0.line, tkt_0.idate, tkt_0.anum,
     	 	 	titm_0.sell, titm_0.qtysold, titm_0.pcore, tkt_0.subnt, tkt_0.subt, tkt_0.misc,
     	 	 	tkt_0.cornt, tkt_0.cort, tkt_0.tfrght, tkt_0.ttax, titm_0.id, tkt_0.workorder
     
    FROM 	 	PACE.PUB.ct ct_0, PACE.PUB.titm titm_0, PACE.PUB.tkt tkt_0
     
    	WHERE 		ct_0.sid = titm_0.sid AND ct_0.sid = tkt_0.sid AND ct_0.id = tkt_0.id AND
     	 	 	 	titm_0.sid = tkt_0.sid AND titm_0.tnum = tkt_0.tnum AND
     	 	 	 	((tkt_0.idate>{d '2018-12-31'}))
     
    ORDER BY tkt_0.idate DESC

    Esa consulta la importo a Excel y sobre esta yo trabajo para generar una tabla dinámica que mis jefes pueden usar para tomar sus decisiones.

    Todo perfecto... Pero el problema es que tengo una i7 de 8tava generación y aun así le toma a la pobre computadora actualizar la tabla al rededor de 2 horas. Y aveces el reporte se corrompe y debo volver a hacer todo. 

    Yo se que se debe a que tengo muchos datos... Pero el verdadero problema esta en que en mi Excel tengo estas formulas:

    (No me permite poner imagenes ni vinculos por que se buggeo la verificacion de cuenta) i.imgur(punto)com/nANB9qM.png 

    Como pueden ver, realmente tengo UN COCHINERO de formulas en diversos campos. Quiero intentar hacer algunas de esas columnas en SQL... Siendo honesto eh estado echándole coco desde hace dos días, y nada viene a mi cabeza. Realmente ya perdí la esperanza de hacer la ultima columna calculada "Tkt" yo le llamo Ticket, pero si pudiese calcular las otras, como la "concatacion de #factura con linea + contar.si.conjunto", el "importe total sin repetir" o la "Unidad de negocio"

    En la columna TKT, no solo me sirve para contar cada factura diferente, eso es sencillo, esa columna la necesito por que entre las lineas TDS es prioritario luego FYE, asi que ocupo que el "1" este SOLO en la fila que lleva TDS o FYE si es que en esa factura se vendio un producto cuya linea es TDS o FYE. Es esa la razon del por que TKT esta tan complejo. Siendo honesto no espero que esto se pueda lograr en SQL sin embargo, cualquier otra columna que se pueda hacer en SQL que estoy seguro si se puede, solo no se como, seria de gran ayuda.

    Debo declarar que NO PUEDO MODIFICAR la base de datos, esto se debe a que esta es administrada por la compañia que nos provee el punto de venta, y solo podemos consultarla. 

    Estoy algo desesperado, cualquiera que quisiera auxiliar es bienvenido, acepto concejos de como deberia aproximar a estas columnas personalisadas, eh intentado "CASE ()" tras "CASE()" y realmente nada me ah funcionado. 

    En la imagen esta la formula que uso en excel, y un escrito en español de lo que hace el proceso.

    Gracias.

    jueves, 7 de marzo de 2019 1:19

Respuestas

  • Hola kjzsbtby:

    De tu pregunta se entiende poco, porque no creo que nadie este en tú negocio.

    Pero un par de matices. Si en excel se puede, dudo muchísimo que SQL Server, para consultar datos, no sea mejor.

    Yo se que se debe a que tengo muchos datos

    Para que te tome 2 horas, hacer una select, me parece que los datos no tienen que ser muchos sino más bien bestiales. No es normal 2 horas en una select en virtud de la que has pegado en esta pregunta.

    Ahí tienes algún problema de índices o ausencia de ellos.

    Por otro lado, de la consulta, si como veo, luego filtras y ordenas con excel, quita el order by y te mejorará muchísimo, ya que order by tiene mucho coste en procesamiento y tiempo.

    Dado que tu imagen tiene muchas causísticas, te voy a preparar la primera de la izquierda

    select
        INUM +LINE + cast(ROW_NUMBER() OVER(PARTITION BY INUM, LINE ORDER BY INUM, LINE, (SELECT NULL)) as varchar(10)) AS FACTU,

    Que básicamente quiere decir, concatena inum + line, y luego numerar las filas empezando en 1 cambiando la numeración por inum y line (o mejor dicho cuando estos cambien), ordenado por inum y line, y ....como no veo más pues por lo que el motor diga.

    Para leer y establecer siguiente y anterior, puedes utilizar las sentencias LAG y LEAD si tu Sql Server es 2012

    https://javifer2.wordpress.com/2018/09/11/lag-y-lead-siguiente-y-anterior/

    Otra cosa, no se realmente como lo hará sql server con tu where, pero ese formato de conversión de fechas tipo ODBC o similar, es posible, que dado que te esta suponiendo una penalización la ejecución de la consulta, y no pierdes nada por cambiar, por si acaso esta haciendo una conversión implicita linea por linea, puedes cambiar tanto el where como el on...algo de este estilo.

    SELECT 	 	titm_0.sid, tkt_0.inum, tkt_0.type, ct_0.logname, titm_0.line, tkt_0.idate, tkt_0.anum,
     	 	 	titm_0.sell, titm_0.qtysold, titm_0.pcore, tkt_0.subnt, tkt_0.subt, tkt_0.misc,
     	 	 	tkt_0.cornt, tkt_0.cort, tkt_0.tfrght, tkt_0.ttax, titm_0.id, tkt_0.workorder
    FROM 	 	PACE.PUB.ct ct_0 
    			inner join	PACE.PUB.titm titm_0  
    				on ct_0.sid = titm_0.sid AND ct_0.sid = tkt_0.sid AND ct_0.id = tkt_0.id
    			 inner join PACE.PUB.tkt tkt_0
    				on titm_0.sid = tkt_0.sid AND titm_0.tnum = tkt_0.tnum
    
    	WHERE 		 
     	 	 	 	tkt_0.idate>'20181231'

    La fecha en formato yyyymmdd

    Espero haberte ayudado en algo

    • Propuesto como respuesta Pedro Alfaro jueves, 7 de marzo de 2019 17:43
    • Marcado como respuesta kjzsbtby jueves, 7 de marzo de 2019 18:42
    jueves, 7 de marzo de 2019 5:41
  • Otra cosa que se me olvidaba, si tus tablas, tienen un campo id y es único en la relación entre ellas, solo hará falta este id.

    PACE.PUB.ct ct_0 
    	 inner join	PACE.PUB.titm titm_0  
    				on ct_0.sid = titm_0.sid   
    			 inner join PACE.PUB.tkt tkt_0
    				on titm_0.sid = tkt_0.sid AND titm_0.tnum = tkt_0.tnum and ct_0.sid = tkt_0.sid and ct_0.id = tkt_0.id

    Esto es como debiera de estar en la entrada anterior, pero desconociendo la información, a priori por los nombres de los campos. ¿No debiera de ser esto?

    Select * from 
    PACE.PUB.ct ct_0 
    	 inner join PACE.PUB.titm titm_0  
    		on ct_0.sid = titm_0.sid   
    	 inner join PACE.PUB.tkt tkt_0
    		on titm_0.sid = tkt_0.sid 

    • Propuesto como respuesta Pedro Alfaro jueves, 7 de marzo de 2019 17:43
    • Marcado como respuesta kjzsbtby jueves, 7 de marzo de 2019 18:41
    jueves, 7 de marzo de 2019 5:52

Todas las respuestas

  • Hola kjzsbtby:

    De tu pregunta se entiende poco, porque no creo que nadie este en tú negocio.

    Pero un par de matices. Si en excel se puede, dudo muchísimo que SQL Server, para consultar datos, no sea mejor.

    Yo se que se debe a que tengo muchos datos

    Para que te tome 2 horas, hacer una select, me parece que los datos no tienen que ser muchos sino más bien bestiales. No es normal 2 horas en una select en virtud de la que has pegado en esta pregunta.

    Ahí tienes algún problema de índices o ausencia de ellos.

    Por otro lado, de la consulta, si como veo, luego filtras y ordenas con excel, quita el order by y te mejorará muchísimo, ya que order by tiene mucho coste en procesamiento y tiempo.

    Dado que tu imagen tiene muchas causísticas, te voy a preparar la primera de la izquierda

    select
        INUM +LINE + cast(ROW_NUMBER() OVER(PARTITION BY INUM, LINE ORDER BY INUM, LINE, (SELECT NULL)) as varchar(10)) AS FACTU,

    Que básicamente quiere decir, concatena inum + line, y luego numerar las filas empezando en 1 cambiando la numeración por inum y line (o mejor dicho cuando estos cambien), ordenado por inum y line, y ....como no veo más pues por lo que el motor diga.

    Para leer y establecer siguiente y anterior, puedes utilizar las sentencias LAG y LEAD si tu Sql Server es 2012

    https://javifer2.wordpress.com/2018/09/11/lag-y-lead-siguiente-y-anterior/

    Otra cosa, no se realmente como lo hará sql server con tu where, pero ese formato de conversión de fechas tipo ODBC o similar, es posible, que dado que te esta suponiendo una penalización la ejecución de la consulta, y no pierdes nada por cambiar, por si acaso esta haciendo una conversión implicita linea por linea, puedes cambiar tanto el where como el on...algo de este estilo.

    SELECT 	 	titm_0.sid, tkt_0.inum, tkt_0.type, ct_0.logname, titm_0.line, tkt_0.idate, tkt_0.anum,
     	 	 	titm_0.sell, titm_0.qtysold, titm_0.pcore, tkt_0.subnt, tkt_0.subt, tkt_0.misc,
     	 	 	tkt_0.cornt, tkt_0.cort, tkt_0.tfrght, tkt_0.ttax, titm_0.id, tkt_0.workorder
    FROM 	 	PACE.PUB.ct ct_0 
    			inner join	PACE.PUB.titm titm_0  
    				on ct_0.sid = titm_0.sid AND ct_0.sid = tkt_0.sid AND ct_0.id = tkt_0.id
    			 inner join PACE.PUB.tkt tkt_0
    				on titm_0.sid = tkt_0.sid AND titm_0.tnum = tkt_0.tnum
    
    	WHERE 		 
     	 	 	 	tkt_0.idate>'20181231'

    La fecha en formato yyyymmdd

    Espero haberte ayudado en algo

    • Propuesto como respuesta Pedro Alfaro jueves, 7 de marzo de 2019 17:43
    • Marcado como respuesta kjzsbtby jueves, 7 de marzo de 2019 18:42
    jueves, 7 de marzo de 2019 5:41
  • Otra cosa que se me olvidaba, si tus tablas, tienen un campo id y es único en la relación entre ellas, solo hará falta este id.

    PACE.PUB.ct ct_0 
    	 inner join	PACE.PUB.titm titm_0  
    				on ct_0.sid = titm_0.sid   
    			 inner join PACE.PUB.tkt tkt_0
    				on titm_0.sid = tkt_0.sid AND titm_0.tnum = tkt_0.tnum and ct_0.sid = tkt_0.sid and ct_0.id = tkt_0.id

    Esto es como debiera de estar en la entrada anterior, pero desconociendo la información, a priori por los nombres de los campos. ¿No debiera de ser esto?

    Select * from 
    PACE.PUB.ct ct_0 
    	 inner join PACE.PUB.titm titm_0  
    		on ct_0.sid = titm_0.sid   
    	 inner join PACE.PUB.tkt tkt_0
    		on titm_0.sid = tkt_0.sid 

    • Propuesto como respuesta Pedro Alfaro jueves, 7 de marzo de 2019 17:43
    • Marcado como respuesta kjzsbtby jueves, 7 de marzo de 2019 18:41
    jueves, 7 de marzo de 2019 5:52
  •                Primero y antes que nada, muchisimas gracias!

    Bien~

    Segundo: Debo aclarar, por que no lo declare, hacer la consulta del SELECT toma como 5 a 20 minutos. (Ya que son las ventas de 9 tiendas, todas enganchadas a un solo servidor, alcanzando a las 974'000 filas en este select solamente)  

    Lo que toma 2 horas en realidad creo que es el calculo que hago en excel, por que hace todo lo que le pido en... Bueno TODAS las 974k+ Filas. Es por eso que ocupo optimizar mi consulta :c

    El campo id no es unico, ese "id" es el numero del vendedor, la tabla "ct" es de Account (cuentas internas del staff) la uso para que en la tabla dinamica mis superiores puedan ver que vendedor hizo que en vez de buscarlo por su nombre si asi lo desean.

    El campo "sid" es el numero de la tienda en que se hizo. La tabla "titm" es donde vienen los items vendidos, sus precios individuales y sus descripciones, mientras que en la tabla "tkt" viene lo que seria el desplegado completo de una venta como un todo. El importe total y el "inum" que seria el numero de factura. Aqui lo que hago es juntar ambos, por su "tnum" campo que ambos comparten es el numero de ticket de la orden.

    Por lo general si lleva una "i" antes del nombre del campo, se refiere al "invoice" que es la factura, y si lleva "t" es de ticket.

    En excel no estoy ordenando, de hecho le pedi el "order.by" a la consulta por SQL para no hacerlo en excel, eso bajo de 2:30hrs a 2hrs...

    La idea aqui es hacer lo menos posible en Excel por que le toma un añal al excel hacer el calculo. 

    La verdad le agradesco mucho por responder, intentare implementar su respuesta y comentare como me fue. Ya leei el [Lead] y [Lag], son precisamente lo que se ocupa para hacer el conteo de tkt! Intentare usarlos igual. 

    Regresare hoy al final de mi jornada (5hrs despues de esta respuesta) o mañana al inicio de mi jornada (11am -7UTC) para comentar como me fue, o plantear mis dudas~

    Muchisimas Gracias!! 

    jueves, 7 de marzo de 2019 19:03