none
Consulta de datos con re-facturación RRS feed

  • Pregunta

  • Hola compañeros me encuentro con un pequeño dilema

    Tengo una bd en la cual tengo 2 tablas una de Facturación y otra de Cancelación, y quiero sacar el reporte de ventas del mes considerando lo siguientes puntos:

    - ventas únicamente del mes sin contar las cancelaciones.

    - si hay una venta, una cancelación y una venta de la misma unidad dentro del mismo mes, cuenta como venta.

    - si la unidad se vendió en febrero, se cancelo en marzo y se volvió a vender en marzo, ya no cuenta como venta. //a esto le llamo re facturación

    Las tablas cuentan con muchos campos pero los que se relacionan son:

    pero los que considero principales son:

    Tabla Facturación tiene:

    fac_fechope - Fecha documento 

    fac_docto - Documento

    fac_serie - Serie del auto

    fac_status - Estatus facturado o cancelado

    Tabla Cancelación tiene:

    vec_fechdocto -  Fecha_documento

    vec_docto - Documento

    vec_serie - Serie del auto

    vec_status - Estatus facturado o cancelado


    Lo que llevo ahorita es lo siguiente

    select * from facturacion as a where left(fac_docto,1) = 'V' and not exists 
    (Select b.VEC_DOCTO,* from cancelacion as b where a.fac_DOCTO = b.VEC_DOCTO and convert(datetime, vec_fechdocto,103) between convert(datetime,'01/03/2019',103) and convert(datetime,'31/03/2019',103)) 
    and convert(datetime, fac_FECHOPE,103) 
    between convert(datetime,'01/03/2019',103) and convert(datetime,'31/03/2019',103) 
    and not exists (select a.fac_serie* from facturacion as c where a.fac_serie = c.vec_serie and convert(datetime, vec_fechdocto,103) between convert(datetime,'01/01/2007',103) and convert(datetime,'28/02/2019',103)) 
    and fac_status = 'facturado'

    Si pudieran ayudarme a solucionar esto porque ya llevo un rato tratando de resolverlo y creo que ya me bloque.

    Gracias de antemano 



    Chinoafro

    jueves, 11 de abril de 2019 19:04

Respuestas

  • Hola chinoafro:

    DECLARE @fecha DATETIME;
    
    SET @fecha = '20181201';
    
    DECLARE @fechafin DATETIME;
    
    SET @fechafin = DATEADD(month, 1, @fecha);
    
    SELECT    facturacion.fac_fechope
    	   , facturacion.fac_docto
    	   , facturacion.fac_serie
    	   , facturacion.fac_status
    	   , validas.diferencia
    FROM      
    	facturacion
    		LEFT JOIN
    (
        SELECT f.fac_fechope
    	    , f.fac_docto
    	    , f.fac_serie
    	    , f.fac_status
    	    , c.vec_fechDocto
    	    , c.vec_docto
    	    , c.vec_serie
    	    , c.vec_Status
    	    , DATEDIFF(month, f.fac_fechope, c.vec_fechDocto) AS diferencia
        FROM   
    	    facturacion f
    		    INNER JOIN cancelacion c ON f.fac_docto = c.vec_docto
    								  AND f.fac_serie = c.vec_serie
        WHERE  c.vec_Status = 'cancelada'
    		 AND DATEDIFF(month, f.fac_fechope, c.vec_fechDocto) > 1
    		 AND f.fac_fechope > @fecha
    ) AS validas ON facturacion.fac_docto = validas.fac_docto
    			 AND facturacion.fac_serie = validas.fac_serie 
    WHERE facturacion.fac_fechope >= @fecha
    	 AND facturacion.fac_fechope < @fechafin
    	 AND ((facturacion.fac_status = 'cancelada'
    		  AND validas.diferencia > 1)
    		 OR facturacion.fac_status <> 'cancelada');

    Tal cual te he entendido relaciono facturacion con un conjunto interior, que relaciona facturacion y cancelacion, pero que solo devuelve como validas, las cancelaciones, que tienen una diferencia mayor de un mes con su factura.

    En la query resultante si las facturas son en estado <> cancelado, van todas, pero si una esta en estado cancelado entonces miro el conjunto interior, llamado validas y si su diferencia es mayor que 1 también vale.

    Espero te ayude

    viernes, 12 de abril de 2019 4:38
  • Hola chinoafro:

    Tú escenario es demasiado "vago", perdón mejor impreciso, como para poder darte una ayuda muy explícita, pero vamos a intentar hacer algo, que al menos te encamine un poco mejor. Lo primero es que no has puesto campos interesantes, ya que seguro que tendrás un id o código de documento, seguro que como es un reporte de ventas, tendrás por la misma venta varias lineas de items/productos/servicios, y estos son los interesantes al reporte.

    Aún así, creo que a lo mejor te puede ayudar esto.

    create table Facturacion (fac_fechope datetime, fac_docto datetime, fac_serie varchar(10), fac_status varchar(20))
    create table cancelacion (vec_Docto datetime, fechdocto datetime, vec_serie varchar(20), vec_Status varchar(20))
    go
    insert into Facturacion (fac_fechope, fac_docto, fac_serie, fac_status)
    values
    ('20190410','20190411','a','pdte'),
    ('20190412','20190418','a','pdte'),
    ('20190415','20190501','a','pdte'),
    ('20190420','20190425','a','pdte'),
    ('20190430','20190430','a','pdte'),
    ('20190501','20190515','a','pdte'),
    ('20190331','20190601','a','pdte');
    go
    insert into cancelacion (vec_Docto, fechdocto, vec_serie, vec_Status)
    values
    ('20190415','20190501','a','pdte'),
    ('20190420','20190425','a','pdte'),
    ('20190331','20190601','a','pdte');
    GO

    Este es el escenario que yo he entendido de tu base de datos. 

    Cuidado porque en la relación que te voy a proponer, es posible, que las columnas de fecha que igualan campos, no sean el motivo de la relación entre las tablas, pero si coges la idea, es posible, que te sirva.

    DECLARE @fecha DATETIME;
    
    SET @fecha = '20190401';
    
    DECLARE @fechafin DATETIME;
    
    SET @fechafin = DATEADD(month, 1, @fecha);
    
    SELECT f.*
    	, v.*
    FROM   
    	Facturacion f
    		LEFT JOIN cancelacion v ON f.fac_fechope = v.vec_Docto
    							--  AND f.fac_fechope = v.fechdocto
    							--  AND f.fac_serie = v.vec_serie
    WHERE  fac_fechope >= @fecha
    	  AND fac_fechope < @fechafin;
    

    En este escenario esta consulta, que lo único que hace es filtrar para el mes solicitado (intervalo de fechas de las variables), y relacionarla con el conjunto de cancelación mediante un left join, realmente me devuelve todas las facturas del rango, y en el conjunto de la derecha solo observo las dos cancelaciones, que tengo.

    Bien pues, si yo excluyo las cancelaciones, o lo que es lo mismo, where v.columna is null ya tengo solo las ventas sin las cancelaciones.

     si hay una venta, una cancelación y una venta de la misma unidad dentro del mismo mes, cuenta como venta.

    Esta parte dado que 1 sumo y 1 resto, si hay otro 1 genial.

    DECLARE @fecha DATETIME;
    
    SET @fecha = '20190401';
    
    DECLARE @fechafin DATETIME;
    
    SET @fechafin = DATEADD(month, 1, @fecha);
    
    WITH CTE
    	AS (SELECT f.fac_fechope
    		    , f.fac_docto
    		    , f.fac_serie
    		    , f.fac_status
    	    FROM   
    		    Facturacion f
    			    LEFT JOIN cancelacion v ON f.fac_fechope = v.vec_Docto
    									 AND f.fac_docto = v.fechdocto
    									 AND f.fac_serie = v.vec_serie
    	    WHERE  fac_fechope >= @fecha
    			 AND fac_fechope < @fechafin
    			 AND V.vec_Docto IS NULL
    )--cierre de la query interior del cte
    	SELECT C.fac_fechope
    		, C.fac_docto
    		, C.fac_serie
    		, C.fac_status
    	FROM   
    		CTE C LEFT JOIN 
    		( -- inicio de la tabla derivada
    	    SELECT f.fac_fechope as fecOpeSig
    		    , f.fac_docto as fecOpeDoc
    		    , f.fac_serie as fecOpeSer
    		    , f.fac_status as fecOpeSta
    	    FROM   
    		    Facturacion f
    			    INNER JOIN cancelacion v ON f.fac_fechope = v.vec_Docto
    									 AND f.fac_docto = v.fechdocto
    									 AND f.fac_serie = v.vec_serie
    	    WHERE  fac_fechope >= DATEADD(MONTH,1,@fecha)
    			 AND fac_fechope < DATEADD(MONTH,1,@fechafin)
    			 
    		) --cierre de la tabla derivada
    		 AS MesSig on c.fac_docto = MesSig.fecOpeDoc and c.fac_fechope = MesSig.fecOpeDoc and c.fac_serie = MesSig.fecOpeSer 
    
    		
    Salida

    Explicación.

    Como ya teníamos el mes en curso, lo he envuelto en una tabla de expresión común llamándola cte, a su salida la he combinado con un left join sobre una tabla derivada, que es exactamente la misma, rodeándola entre paréntesis que he llamado MesSig. Observarás que tiene dos diferencias, la fecha de corte, a las cuales, les he sumado un mes y la relación entre facturación y cancelación que ahora es una inner join para que si están igualadas llega eso que tu llamas re-facturación. 

    Luego de este conjunto salen las del mes siguiente, y relaciono el cte con las del mes siguiente.

    Ya te digo que esto es solo una idea, porque el detalle, que has ofrecido es muy poco preciso.

    Espero te ayude

    Tablas de expresión común

    https://javifer2.blogspot.com/2019/01/with-cte-tablas-de-expresion-comun-1_1.html

    https://javifer2.blogspot.com/2019/01/with-cte-tablas-de-expresion-comun-2.html

    https://javifer2.blogspot.com/2019/01/with-cte-tablas-de-expresion-comun.html

    https://javifer2.blogspot.com/search/label/conjuntos%20correlativos

    Tablas derivadashttps://www.manualsqlserver.com/?p=664

    jueves, 11 de abril de 2019 20:05

Todas las respuestas

  • Hola chinoafro:

    Tú escenario es demasiado "vago", perdón mejor impreciso, como para poder darte una ayuda muy explícita, pero vamos a intentar hacer algo, que al menos te encamine un poco mejor. Lo primero es que no has puesto campos interesantes, ya que seguro que tendrás un id o código de documento, seguro que como es un reporte de ventas, tendrás por la misma venta varias lineas de items/productos/servicios, y estos son los interesantes al reporte.

    Aún así, creo que a lo mejor te puede ayudar esto.

    create table Facturacion (fac_fechope datetime, fac_docto datetime, fac_serie varchar(10), fac_status varchar(20))
    create table cancelacion (vec_Docto datetime, fechdocto datetime, vec_serie varchar(20), vec_Status varchar(20))
    go
    insert into Facturacion (fac_fechope, fac_docto, fac_serie, fac_status)
    values
    ('20190410','20190411','a','pdte'),
    ('20190412','20190418','a','pdte'),
    ('20190415','20190501','a','pdte'),
    ('20190420','20190425','a','pdte'),
    ('20190430','20190430','a','pdte'),
    ('20190501','20190515','a','pdte'),
    ('20190331','20190601','a','pdte');
    go
    insert into cancelacion (vec_Docto, fechdocto, vec_serie, vec_Status)
    values
    ('20190415','20190501','a','pdte'),
    ('20190420','20190425','a','pdte'),
    ('20190331','20190601','a','pdte');
    GO

    Este es el escenario que yo he entendido de tu base de datos. 

    Cuidado porque en la relación que te voy a proponer, es posible, que las columnas de fecha que igualan campos, no sean el motivo de la relación entre las tablas, pero si coges la idea, es posible, que te sirva.

    DECLARE @fecha DATETIME;
    
    SET @fecha = '20190401';
    
    DECLARE @fechafin DATETIME;
    
    SET @fechafin = DATEADD(month, 1, @fecha);
    
    SELECT f.*
    	, v.*
    FROM   
    	Facturacion f
    		LEFT JOIN cancelacion v ON f.fac_fechope = v.vec_Docto
    							--  AND f.fac_fechope = v.fechdocto
    							--  AND f.fac_serie = v.vec_serie
    WHERE  fac_fechope >= @fecha
    	  AND fac_fechope < @fechafin;
    

    En este escenario esta consulta, que lo único que hace es filtrar para el mes solicitado (intervalo de fechas de las variables), y relacionarla con el conjunto de cancelación mediante un left join, realmente me devuelve todas las facturas del rango, y en el conjunto de la derecha solo observo las dos cancelaciones, que tengo.

    Bien pues, si yo excluyo las cancelaciones, o lo que es lo mismo, where v.columna is null ya tengo solo las ventas sin las cancelaciones.

     si hay una venta, una cancelación y una venta de la misma unidad dentro del mismo mes, cuenta como venta.

    Esta parte dado que 1 sumo y 1 resto, si hay otro 1 genial.

    DECLARE @fecha DATETIME;
    
    SET @fecha = '20190401';
    
    DECLARE @fechafin DATETIME;
    
    SET @fechafin = DATEADD(month, 1, @fecha);
    
    WITH CTE
    	AS (SELECT f.fac_fechope
    		    , f.fac_docto
    		    , f.fac_serie
    		    , f.fac_status
    	    FROM   
    		    Facturacion f
    			    LEFT JOIN cancelacion v ON f.fac_fechope = v.vec_Docto
    									 AND f.fac_docto = v.fechdocto
    									 AND f.fac_serie = v.vec_serie
    	    WHERE  fac_fechope >= @fecha
    			 AND fac_fechope < @fechafin
    			 AND V.vec_Docto IS NULL
    )--cierre de la query interior del cte
    	SELECT C.fac_fechope
    		, C.fac_docto
    		, C.fac_serie
    		, C.fac_status
    	FROM   
    		CTE C LEFT JOIN 
    		( -- inicio de la tabla derivada
    	    SELECT f.fac_fechope as fecOpeSig
    		    , f.fac_docto as fecOpeDoc
    		    , f.fac_serie as fecOpeSer
    		    , f.fac_status as fecOpeSta
    	    FROM   
    		    Facturacion f
    			    INNER JOIN cancelacion v ON f.fac_fechope = v.vec_Docto
    									 AND f.fac_docto = v.fechdocto
    									 AND f.fac_serie = v.vec_serie
    	    WHERE  fac_fechope >= DATEADD(MONTH,1,@fecha)
    			 AND fac_fechope < DATEADD(MONTH,1,@fechafin)
    			 
    		) --cierre de la tabla derivada
    		 AS MesSig on c.fac_docto = MesSig.fecOpeDoc and c.fac_fechope = MesSig.fecOpeDoc and c.fac_serie = MesSig.fecOpeSer 
    
    		
    Salida

    Explicación.

    Como ya teníamos el mes en curso, lo he envuelto en una tabla de expresión común llamándola cte, a su salida la he combinado con un left join sobre una tabla derivada, que es exactamente la misma, rodeándola entre paréntesis que he llamado MesSig. Observarás que tiene dos diferencias, la fecha de corte, a las cuales, les he sumado un mes y la relación entre facturación y cancelación que ahora es una inner join para que si están igualadas llega eso que tu llamas re-facturación. 

    Luego de este conjunto salen las del mes siguiente, y relaciono el cte con las del mes siguiente.

    Ya te digo que esto es solo una idea, porque el detalle, que has ofrecido es muy poco preciso.

    Espero te ayude

    Tablas de expresión común

    https://javifer2.blogspot.com/2019/01/with-cte-tablas-de-expresion-comun-1_1.html

    https://javifer2.blogspot.com/2019/01/with-cte-tablas-de-expresion-comun-2.html

    https://javifer2.blogspot.com/2019/01/with-cte-tablas-de-expresion-comun.html

    https://javifer2.blogspot.com/search/label/conjuntos%20correlativos

    Tablas derivadashttps://www.manualsqlserver.com/?p=664

    jueves, 11 de abril de 2019 20:05
  • Hola gracias por la ayuda con esto.

    Perdón por no explicar mi punto de mejor manera.

    voy de nuevo

    create table facturacion (fac_fechope datetime, fac_docto varchar(10), fac_serie varchar(10), fac_status varchar(20))
    create table cancelacion (vec_fechDocto datetime, vec_docto varchar(10), vec_serie varchar(20), vec_Status varchar(20))
    
    
    insert into facturacion (fac_fechope, fac_docto, fac_serie, fac_status)
    values
    ('20181227','v123','123456','cancelada'), -- primera facturacion de 123456 con factura v123 en diciembre 2018
    ('20190112','v124','123565','cancelada'), -- primera facturacion de 123565 con factura v124 en enero
    ('20190315','v125','152636','facturada'), -- venta normal del mes de marzo
    ('20190310','v126','123456','facturada'), -- segunda facturacion de 123456 con facturacion v126 en marzo
    ('20190310','v127','123565','facturada'), -- segunda facturacion de 123565 con facturacion v127 en marzo
    ('20190301','v128','158631','facturada'), -- venta normal del mes de marzo
    ('20190308','v129','155689','cancelada'); -- venta con estatus de cancelada 
    go
    
    insert into cancelacion (vec_fechDocto,vec_docto , vec_serie, vec_Status)
    values
    ('20190215','v123','123456','cancelada'), --cancelacion de la unidad 123456 con factura v123 en febrero
    ('20190302','v124','123565','cancelada'), --cancelacion de la unidad 123565 con factura v124 en marzo
    ('20190331','v129','155689','cancelada'); --cancelacion de la factura v129
    GO

    Esta es la situación.

    Para sacar el reporte de Marzo, tengo que considerar que:

    1.- la venta se haya hecho en ese mes.

    2.- Que no este cancelada.

    3.- Si esta cancelada y tomarla como venta

                 --- la cancelación debe tener mas de 1 mes de diferencia con la nueva factura.

    Gracias por la ayuda, de todas maneras estoy checando el codigo que me pusiste de ejemplo para ver si lo puedo adaptar para solucionar esto.

    Gracias


    Chinoafro


    jueves, 11 de abril de 2019 22:44
  • Hola chinoafro:

    DECLARE @fecha DATETIME;
    
    SET @fecha = '20181201';
    
    DECLARE @fechafin DATETIME;
    
    SET @fechafin = DATEADD(month, 1, @fecha);
    
    SELECT    facturacion.fac_fechope
    	   , facturacion.fac_docto
    	   , facturacion.fac_serie
    	   , facturacion.fac_status
    	   , validas.diferencia
    FROM      
    	facturacion
    		LEFT JOIN
    (
        SELECT f.fac_fechope
    	    , f.fac_docto
    	    , f.fac_serie
    	    , f.fac_status
    	    , c.vec_fechDocto
    	    , c.vec_docto
    	    , c.vec_serie
    	    , c.vec_Status
    	    , DATEDIFF(month, f.fac_fechope, c.vec_fechDocto) AS diferencia
        FROM   
    	    facturacion f
    		    INNER JOIN cancelacion c ON f.fac_docto = c.vec_docto
    								  AND f.fac_serie = c.vec_serie
        WHERE  c.vec_Status = 'cancelada'
    		 AND DATEDIFF(month, f.fac_fechope, c.vec_fechDocto) > 1
    		 AND f.fac_fechope > @fecha
    ) AS validas ON facturacion.fac_docto = validas.fac_docto
    			 AND facturacion.fac_serie = validas.fac_serie 
    WHERE facturacion.fac_fechope >= @fecha
    	 AND facturacion.fac_fechope < @fechafin
    	 AND ((facturacion.fac_status = 'cancelada'
    		  AND validas.diferencia > 1)
    		 OR facturacion.fac_status <> 'cancelada');

    Tal cual te he entendido relaciono facturacion con un conjunto interior, que relaciona facturacion y cancelacion, pero que solo devuelve como validas, las cancelaciones, que tienen una diferencia mayor de un mes con su factura.

    En la query resultante si las facturas son en estado <> cancelado, van todas, pero si una esta en estado cancelado entonces miro el conjunto interior, llamado validas y si su diferencia es mayor que 1 también vale.

    Espero te ayude

    viernes, 12 de abril de 2019 4:38