Consulta de datos con re-facturación

Question

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

Answer 1

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

Answer 2

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

Answer 3

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