none
Realizar un Kardez en Sql server 2012. RRS feed

  • Pregunta

  • Buenos días a tod@s.

    Estimad@s, tengo un problemita con el cual agradecería de sus conocimientos, para que me puedan ayudar.

    Tengo las siguientes tablas en SQL Server 2012:  Productos, HistorialSalidas, HistorialEntradas, DetalleVentas.

    Bueno a partir de estas tablas quisiera hacer una consulta para posteriormente mostrarla mediante una aplicación hecha en vb.2012, La consulta se trata de un Kardez, el cual quisiera se muestre de la siguiente manera:

    La columna de “Fecha” deberá de ser la fecha del documento ya sea de entrada o de salida

    DetalleVenta: fecha_contabilizada

    HistorialEntrada: fecha

    Historial Salida: fecha

    Nota: En la tabla de productos hay una columna que se llama cantcaja, esta columna almacena un entero el cual indica cuanto contiene cada caja de cada productos, es decir el código 40001 que es : Chicle De Menta, su presentación es de 1x24 por tal razón su contenido cantcaja=24, a la hora de realizar una venta el usuario solo pone la cantidad de cajas que se venderán(puesto que las ventas pueden ser solo por mayor o por unidades), entonces el sistema multiplica la cantidad * cantcaja= totalsalida

    Ejemplo:

    Usuario dijita : 2cajas

    Su contiene(cantcajas)=24; entonces el sistema hace la conversión a:

    24*2 = 48, este 48 es la cantidad que se esta vendiendo y que se almacena en Detalleventa columna “cantidad”;

    La tabla DetalleVentas tiene una columna “contenia” de tipo entero que almacena el contenido del producto facturado que es tomado de la tabla Productos columna “cantcaja”, esto se hace para que quede un registro de cada venta y cuanto contenía cada producto a la hora que fue facturado, ya que en la tabla Productos columna cantcaja, puede ser actualizado, es decir si el código 40001 contenía 24, por que se vendía por cajas y por unidades, pero ahora se decidió vender solo por cajas completas, entonces se cambia su cantcajas a 1, de esta manera podemos saber cuánto contenía cada producto a la hora que fue facturado sin importar lo que contenga actualmente en cantcajas tabla de Productos

    A la hora de realizar una consulta para ver que salió por cada factura, se hace la siguiente conversión cantidad_salida/cast(contenia as float) as salida

    Tomar en cuenta también la columna mayor_detalle en la tabla Detalleventas esta columna puede almacenar solo 3 tipos de valores que son los siguientes:

    1) VALORADO

    2) Regalía xC

    3) Regalía xU

    Le explico: hay clientes que según su historial crediticio se les hace una bonificación por la compra de ciertos productos, es decir yo puedo decir: mira juan si me compras 10 cajas de Chicle De Menta, te voy a bonificar 2 cajas , suponiendo que el cliente acepto el trato, entonces a la hora de realizar la venta yo facturo las 10 cajas esto sale como “VALORADO “ y agrego otro ítem del mismo producto en cuestión, pero este sale como “Regalía XC”, pero hay casos en lo que la bonificación (Regalía) puede ser por unidades, esto va a depender del producto en cuestión.

    Nota: cuando un producto es bonificado sale con un valor=0.

    Por esta razón a la hora de mostrar cada factura la hago tomando en cuenta 4 parametros

    1)contenia

    2)si es VALORADO

    3) si es Regalía xC

    4) si es Regalía xCU

    Ejemplo de consulta para visualizar una factura:

      select dv.idproducto,
            p.producto,
     (case 
    	when (dv.mayor_detalle) ='VALORADO' then (dv.cantidad / cast(dv.contenia as float)) 
    	when (dv.mayor_detalle) ='Regalía xC' then ((dv.cantidad) / cast(dv.contenia as float)) 
        when (dv.mayor_detalle) ='Regalía xU' then (dv.cantidad)
     End) as salida,
    		dv.precio ,
    		dv.total,
    		convert(varchar(12),dv.fecha_contabilizada,113) as Fecha 
     from DetalleVenta as dv inner join 
          Productos as p on dv.idproducto = p.idproducto 
     where numero_factura =6957
     order by 2 asc

    Resultado de la consulta:

    A hora bien, el Kardez que quiero hacer necesito que tome en cuenta estos 4 parametros, puesto que será útil a la hora de realizar resta o suma al stock del producto, además que a la consulta se le pueda pasar dos parámetros @FechaInicial y @FechaFinal.

    A continuación, les muestro las tablas en cuestión:

    Favor indicarme si esto que deseo hacer es posible y si es posible como hacerlo, o si debo implementar algo en la base de datos para llevar un cardes favor indicarme.

    Cualquier información que deseen saber al respecto favor solicitármelo, estaré al pendiente.

    De ante manos muchas gracias.

    miércoles, 4 de diciembre de 2019 18:15

Respuestas

  • Hola Reynaldo Sanchez:

    Lo primero el escenario.

    -- No ejecutar si tienes datos 
    drop table if exists historialEntradaProductos;
    drop table if exists DetalleVenta;
    drop table if exists historialSalidaProductos;
    drop table if exists productos;
    -- Creates
    create table historialEntradaProductos(
    idhistorial int,
    numero_entrada int,
    idproducto int,
    cantidad int,
    fecha datetime)
    go
    
    create table historialSalidaProductos(
    idhistorial int,
    numero_salida int,
    idproducto int,
    cantidad int,
    fecha datetime)
    go
    
    create table productos (
    idproducto int,
    producto nvarchar(50),
    descripcion nvarchar(200),
    u_m nvarchar(50),
    presentacion nvarchar(200),
    fecha_vencimiento datetime,
    precio_Venta_mayor numeric(18,2),
    precio_Venta_detalla numeric (18,2),
    stock int,
    stock_minimo int,
    cantcaja int)
    go
    
    create table DetalleVenta (
    idVenta int,
    numero_factura int,
    idproducto int,
    cantidad int,
    precio numeric(18,2),
    sub_total numeric(18,2),
    descuento numeric(18,2),
    total numeric(18,2),
    cancelado char (2),
    fecha_solicitada datetime,
    fecha_contabilizada datetime,
    mayor_detalle varchar(50),
    contenia int
    );
    
    go
    Insert into historialEntradaProductos 
    (idhistorial,numero_entrada,idproducto,cantidad,fecha)
    values
    (1,5000,40001,100,'20191204'),
    (1,5001,40001,100,'20191205'),
    (1,5002,40094,100,'20191204'),
    (1,5003,40095,100,'20191204');
    go
    
    insert into productos (idproducto,producto,descripcion ,u_m,presentacion,fecha_vencimiento,precio_Venta_mayor,
    precio_Venta_detalla,stock ,stock_minimo,cantcaja)
    values
    (40001,'Chicle de menta','Chicles',null,'1x24','20210101',100,15,20,10,24),
    (40094,'Frambuesa botella','Framb',null,'2x12','20191231',245,30,40,5,12);
    go
    
    insert into DetalleVenta (idVenta,numero_factura,idproducto,cantidad,precio, sub_total,
    descuento,total,cancelado,fecha_solicitada,fecha_contabilizada,mayor_detalle,contenia)
    values
    (1,100,40001,24,15,100,10,90,null, '20191204','20191204','VALORADO',24),
    (1,100,40001,2 ,15,100,10,90,null, '20191204','20191204','xC'		 ,24),
    (2,104,40094,24,15,100,10,90,null, '20191204','20191204','VALORADO',24),
    (2,104,40094,2 ,15,100,10,90,null, '20191204','20191204','xC'		 ,24),
    (3,105,40001,24,15,100,10,90,null, '20191205','20191205','VALORADO',24),
    (3,105,40001,2 ,15,100,10,90,null, '20191205','20191205','xC'		 ,24);
    go

    Ahora ya con algunos datos.

    DECLARE @FECHAINICIAL DATETIME = '20191204';
    DECLARE @FECHAFINAL DATETIME = '20200101';
    ;
    WITH ventas
         AS (SELECT p.idproducto
                  , p.descripcion
                  , dv.numero_factura AS docsalida
                  , CAST(DV.fecha_contabilizada AS DATE) AS fechaDoc
                  , SUM(CASE
                            WHEN dv.mayor_detalle = 'VALORADO'
                            THEN dv.cantidad
                        END) AS cantidad -- SOLO IMPORTAN LOS QUE MUEVEN STOCK
                  , 0 AS IdentificaMovimiento
             FROM productos p
                  INNER JOIN DetalleVenta dv ON dv.idproducto = p.idproducto
    		WHERE DV.fecha_contabilizada >= @FECHAINICIAL AND DV.fecha_contabilizada < @FECHAFINAL
                    GROUP BY p.idproducto
                           , p.descripcion
                           , dv.numero_factura
                           , CAST(DV.fecha_contabilizada AS DATE)),
         compras
         AS (SELECT P.idproducto
                  , P.descripcion
                  , HE.numero_entrada
                  , CAST(HE.fecha AS DATE) AS Fechadoc
                  , SUM(he.cantidad) AS cantidad
                  , 1 AS IdentificaMovimiento
             FROM Productos p
                  INNER JOIN historialEntradaProductos he ON p.idproducto = he.idproducto
    			  WHERE HE.fecha >= @FECHAINICIAL AND HE.fecha < @FECHAFINAL
                    GROUP BY P.idproducto
                           , P.descripcion
                           , HE.numero_entrada
                           , CAST(HE.fecha AS DATE)),
    		saldo_anterior AS
    		(
    		Select t.idproducto, sum(case when identificaMovimiento = 0 then cantidad else -1*cantidad end) as cantidad from (
    		SELECT p.idproducto
                  , SUM(CASE WHEN dv.mayor_detalle = 'VALORADO' THEN dv.cantidad END) AS cantidad -- SOLO IMPORTAN LOS QUE MUEVEN STOCK
                  , 0 AS IdentificaMovimiento
             FROM productos p
                  INNER JOIN DetalleVenta dv ON dv.idproducto = p.idproducto
    			  where dv.fecha_contabilizada < @FECHAINICIAL
    			  group by p.idproducto
    		 Union all
    		 SELECT p.idproducto
                  , SUM(cANTIDAD) AS cantidad -- SOLO IMPORTAN LOS QUE MUEVEN STOCK
                  , 1 AS IdentificaMovimiento
             FROM productos p
                  INNER JOIN historialEntradaProductos he ON he.idproducto = p.idproducto
    			  where he.fecha < @FECHAINICIAL
    			  group by p.idproducto
    			  ) as t
    			  group by t.idproducto
    
    		),
    	Agrupa As (
         SELECT idproducto
              , descripcion
              , numero_entrada
              , Fechadoc
              , cantidad
              , IdentificaMovimiento
         FROM Compras
         UNION ALL
         SELECT idproducto
              , descripcion
              , docsalida
              , fechaDoc
              , cantidad
              , IdentificaMovimiento
         FROM ventas
    	 )
    	 select a.Fechadoc
    		, p.idproducto
    		, p.descripcion
    		, isnull(s.cantidad,0) as Stock_inicial
    		, case when a.IdentificaMovimiento = 1 then cast(a.numero_entrada as varchar(30)) else '' end  as docEntrada, 
    			case when a.IdentificaMovimiento = 1 then a.cantidad else 0 end as entrada,
    			case when a.IdentificaMovimiento = 0 then cast(a.numero_entrada as varchar(30)) else '' end docSalida, 
    			case when a.IdentificaMovimiento = 0 then a.cantidad else 0 end as salida,
    			sum(case when a.identificamovimiento = 1 then a.cantidad else -1*a.cantidad end) over(partition by p.idproducto order by a.fechadoc
    	 rows unbounded preceding) as Saldo
    	 
    
    	 
    	  from productos p 
    		LEFT JOIN Agrupa a on p.idproducto = a.idproducto
    		LEFT JOIN saldo_anterior S on p.idproducto = s.idproducto
    		ORDER BY fechadoc
    	 

    Voy a ir destripandonte la query por partes, porque supongo que apriori, puede resultarte algo compleja. Si no conoces las tablas de expresión común y las tablas derivadas, este es tú momento.

    Derivada

    https://javifer2.wordpress.com/2019/11/06/tabla-derivada/

    Expresión común

    https://javifer2.wordpress.com/2018/12/18/with-cte-tablas-de-expresion-comun-1/

    Correlativas

    https://javifer2.wordpress.com/2018/12/18/with-cte-tablas-de-expresion-comun-2-correlativos/

    Ahora la solución

    Primer conjunto

    ITH ventas
         AS (SELECT p.idproducto
                  , p.descripcion
                  , dv.numero_factura AS docsalida
                  , CAST(DV.fecha_contabilizada AS DATE) AS fechaDoc
                  , SUM(CASE
                            WHEN dv.mayor_detalle = 'VALORADO'
                            THEN dv.cantidad
                        END) AS cantidad -- SOLO IMPORTAN LOS QUE MUEVEN STOCK
                  , 0 AS IdentificaMovimiento
             FROM productos p
                  INNER JOIN DetalleVenta dv ON dv.idproducto = p.idproducto
    		WHERE DV.fecha_contabilizada >= @FECHAINICIAL AND DV.fecha_contabilizada < @FECHAFINAL
                    GROUP BY p.idproducto
                           , p.descripcion
                           , dv.numero_factura
                           , CAST(DV.fecha_contabilizada AS DATE)),

    Da igual que le cobres o no a un cliente, lo que importa es el stock para tu informe, y si el tipo es regalado o no es indiferente, por tanto en este conjunto obtenemos la suma de las cantidades de los productos vendidos con su documento cuya fecha este entre lo parametrizado. Le añado una columna que me servirá p ara saber si suman o restan stock.

    Nota: ojo si el tema del stock es diferente, solo tienes que quitar o cambiar el case interior del sum

    Segundo conjunto.

      compras
         AS (SELECT P.idproducto
                  , P.descripcion
                  , HE.numero_entrada
                  , CAST(HE.fecha AS DATE) AS Fechadoc
                  , SUM(he.cantidad) AS cantidad
                  , 1 AS IdentificaMovimiento
             FROM Productos p
                  INNER JOIN historialEntradaProductos he ON p.idproducto = he.idproducto
    			  WHERE HE.fecha >= @FECHAINICIAL AND HE.fecha < @FECHAFINAL
                    GROUP BY P.idproducto
                           , P.descripcion
                           , HE.numero_entrada
                           , CAST(HE.fecha AS DATE)),

    Es igual que el anterior pero aquí contamos con el historico de entradas y estos movimientos se identifican como 1.

    saldo_anterior AS
    		(
    		Select t.idproducto, sum(case when identificaMovimiento = 0 then cantidad else -1*cantidad end) as cantidad from (
    		SELECT p.idproducto
                  , SUM(CASE WHEN dv.mayor_detalle = 'VALORADO' THEN dv.cantidad END) AS cantidad -- SOLO IMPORTAN LOS QUE MUEVEN STOCK
                  , 0 AS IdentificaMovimiento
             FROM productos p
                  INNER JOIN DetalleVenta dv ON dv.idproducto = p.idproducto
    			  where dv.fecha_contabilizada < @FECHAINICIAL
    			  group by p.idproducto
    		 Union all
    		 SELECT p.idproducto
                  , SUM(cANTIDAD) AS cantidad -- SOLO IMPORTAN LOS QUE MUEVEN STOCK
                  , 1 AS IdentificaMovimiento
             FROM productos p
                  INNER JOIN historialEntradaProductos he ON he.idproducto = p.idproducto
    			  where he.fecha < @FECHAINICIAL
    			  group by p.idproducto
    			  ) as t
    			  group by t.idproducto
    
    		),


    Utilizando la misma consulta y técnica, obtenemos el saldo por producto cuya fecha de compra o venta sea anterior a la fecha de corte.

    Agrupa As (
         SELECT idproducto
              , descripcion
              , numero_entrada
              , Fechadoc
              , cantidad
              , IdentificaMovimiento
         FROM Compras
         UNION ALL
         SELECT idproducto
              , descripcion
              , docsalida
              , fechaDoc
              , cantidad
              , IdentificaMovimiento
         FROM ventas
    	 )

    Introduzco en un mismo conjunto los conjuntos anteriores, para simplificarte un poco el código.

    select a.Fechadoc , p.idproducto , p.descripcion , isnull(s.cantidad,0) as Stock_inicial , case when a.IdentificaMovimiento = 1 then cast(a.numero_entrada as varchar(30)) else '' end as docEntrada, case when a.IdentificaMovimiento = 1 then a.cantidad else 0 end as entrada, case when a.IdentificaMovimiento = 0 then cast(a.numero_entrada as varchar(30)) else '' end docSalida, case when a.IdentificaMovimiento = 0 then a.cantidad else 0 end as salida, sum(case when a.identificamovimiento = 1 then a.cantidad else -1*a.cantidad end) over(partition by p.idproducto order by a.fechadoc rows unbounded preceding) as Saldo from productos p LEFT JOIN Agrupa a on p.idproducto = a.idproducto LEFT JOIN saldo_anterior S on p.idproducto = s.idproducto

    order by fechadoc

    La salida no es más que obtener los datos de todos los productos, mezclados con lo anterior, y para el saldo, utilizamos la función de ventana

    Sumas acumuladas

    https://javifer2.wordpress.com/2019/10/26/sumas-acumuladas/

    Espero te ayude

    viernes, 6 de diciembre de 2019 9:47
  • Gracias Javier...ahorita mismo revisare a ver si me funciona....posteriormente te estare comentando,

    Muchas Gracias de antemano.

    Saludos.

    viernes, 6 de diciembre de 2019 18:35
  • Hola Reynaldo Sanchez:

    Usuario dijita : 2cajas Su contiene(cantcajas)=24; entonces el sistema hace la conversión a: 24*2 = 48, este 48 es la cantidad que se esta vendiendo y que se almacena en Detalleventa columna “cantidad”;

    Si el sistema hace la conversión y lo que anota en detalleventa columna cantidad son las unidades, cosa por otra parte que a mi entender, es correcta, el stock se analiza en unidades, se tiene en unidades.

    Entonces la solución, no es hacer multiplicaciones, ni divisiones, en las operaciones, sino en la última salida del query, mostrar la info por cajas.

    Ojo te voy a dar la query y verás que he multiplicado por 1.0 la salida, para hacer un float. Con mis datos, los del ejemplo, salen cantidades con decimales.

    Le puedes hacer un cast ( (....) as decimal(20,2)) por ejemplo para que salga solo con 2 decimales.

    DECLARE @FECHAINICIAL DATETIME = '20191204';
    DECLARE @FECHAFINAL DATETIME = '20200101';
    ;
    WITH ventas
         AS (SELECT p.idproducto
                  , p.descripcion
                  , dv.numero_factura AS docsalida
                  , CAST(DV.fecha_contabilizada AS DATE) AS fechaDoc
                  , SUM(CASE
                            WHEN dv.mayor_detalle = 'VALORADO'
                            THEN dv.cantidad
                        END) AS cantidad -- SOLO IMPORTAN LOS QUE MUEVEN STOCK
                  , 0 AS IdentificaMovimiento
             FROM productos p
                  INNER JOIN DetalleVenta dv ON dv.idproducto = p.idproducto
    		WHERE DV.fecha_contabilizada >= @FECHAINICIAL AND DV.fecha_contabilizada < @FECHAFINAL
                    GROUP BY p.idproducto
                           , p.descripcion
                           , dv.numero_factura
                           , CAST(DV.fecha_contabilizada AS DATE)),
         compras
         AS (SELECT P.idproducto
                  , P.descripcion
                  , HE.numero_entrada
                  , CAST(HE.fecha AS DATE) AS Fechadoc
                  , SUM(he.cantidad) AS cantidad
                  , 1 AS IdentificaMovimiento
             FROM Productos p
                  INNER JOIN historialEntradaProductos he ON p.idproducto = he.idproducto
    			  WHERE HE.fecha >= @FECHAINICIAL AND HE.fecha < @FECHAFINAL
                    GROUP BY P.idproducto
                           , P.descripcion
                           , HE.numero_entrada
                           , CAST(HE.fecha AS DATE)),
    		saldo_anterior AS
    		(
    		Select t.idproducto, sum(case when identificaMovimiento = 0 then cantidad else -1*cantidad end) as cantidad from (
    		SELECT p.idproducto
                  , SUM(CASE WHEN dv.mayor_detalle = 'VALORADO' THEN dv.cantidad END) AS cantidad -- SOLO IMPORTAN LOS QUE MUEVEN STOCK
                  , 0 AS IdentificaMovimiento
             FROM productos p
                  INNER JOIN DetalleVenta dv ON dv.idproducto = p.idproducto
    			  where dv.fecha_contabilizada < @FECHAINICIAL
    			  group by p.idproducto
    		 Union all
    		 SELECT p.idproducto
                  , SUM(cANTIDAD) AS cantidad -- SOLO IMPORTAN LOS QUE MUEVEN STOCK
                  , 1 AS IdentificaMovimiento
             FROM productos p
                  INNER JOIN historialEntradaProductos he ON he.idproducto = p.idproducto
    			  where he.fecha < @FECHAINICIAL
    			  group by p.idproducto
    			  ) as t
    			  group by t.idproducto
    
    		),
    	Agrupa As (
         SELECT idproducto
              , descripcion
              , numero_entrada
              , Fechadoc
              , cantidad
              , IdentificaMovimiento
         FROM Compras
         UNION ALL
         SELECT idproducto
              , descripcion
              , docsalida
              , fechaDoc
              , cantidad
              , IdentificaMovimiento
         FROM ventas
    	 )
    	 select a.Fechadoc
    		, p.idproducto
    		, p.descripcion
    		, isnull(s.cantidad,0) as Stock_inicial
    		, case when a.IdentificaMovimiento = 1 then cast(a.numero_entrada as varchar(30)) else '' end  as docEntrada, 
    			case when a.IdentificaMovimiento = 1 then a.cantidad * (1.0) / p.cantcaja else 0 end as entrada,
    			case when a.IdentificaMovimiento = 0 then cast(a.numero_entrada as varchar(30)) else '' end docSalida, 
    			case when a.IdentificaMovimiento = 0 then a.cantidad * (1.0)/ p.cantcaja else 0 end as salida,
    			sum(case when a.identificamovimiento = 1 then a.cantidad * (1.0)/p.cantcaja else -1*a.cantidad*(1.0)/p.cantcaja end) over(partition by p.idproducto order by a.fechadoc
    	 rows unbounded preceding) as Saldo
    	  from productos p 
    		LEFT JOIN Agrupa a on p.idproducto = a.idproducto
    		LEFT JOIN saldo_anterior S on p.idproducto = s.idproducto
    		ORDER BY fechadoc

    sábado, 7 de diciembre de 2019 5:31

Todas las respuestas

  • Hola Reynaldo Sanchez:

    Favor indicarme si esto que deseo hacer es posible

    Por supuesto que es posible. Ya sabes que es posible, lo cual dice, que la pregunta correcta, es más bien, ¿Cuál es el coste en tiempo/recursos que puede tener hacer esto?

    Hay una cosa que a mi no me encaja del todo. En tú modelo de tablas, (al menos lo que muestras), en principio las tablas de historial, son casi para ignorar, pero detalleVenta, parece contener una referencia a Venta, que supongo será su cabecera, donde anotarás el cliente, y otros muchos datos. ¿O no?

    La segunda pregunta y dado que veo que idVenta es foreign key (supongo que de venta) e idProducto fk de producto. ¿No se pueden repetir detalleVentas para el mismo numero_Factura y mismo idProducto?

    Ya comentas

    miércoles, 4 de diciembre de 2019 18:39
  • Hay una cosa que a mi no me encaja del todo. En tú modelo de tablas, (al menos lo que muestras), en principio las tablas de historial, son casi para ignorar, pero detalleVenta, parece contener una referencia a Venta, que supongo será su cabecera, donde anotarás el cliente, y otros muchos datos. ¿O no?

    Bueno las tablas de HistorialSalida , por supuesto que podemos ignorarlas, por que la tabla de DetalleVenta, prácticamente contiene lo necesario, para sacar parte de los datos, pero las entradas si que las tendríamos que sacar de HistorialEntradas, al menos que ocupemos la tabla de FacturasVirtuales aquí se almacena todas las compras que ingresan mediante una factura que hace entrega el proveedor les muestro la tabla:

    La segunda pregunta y dado que veo que idVenta es foreign key (supongo que de venta) e idProducto fk de producto. ¿No se pueden repetir detalleVentas para el mismo numero_Factura y mismo idProducto?

    Si se puede repetir el mismo idproducto en la misma venta, puesto que hay una diferencia y es que un producto puede salir como valorado o regalía en la misma factura.

    Es decir si del código 40001 yo vendo 10 cajas , pero por la compra de esas 10 cajas , bonifico 2 cajas entonces en esa misma factura yo agregaría dos ítems del mismo código con la salvedad de que uno es valorado y el otro es bonificado(regalía) con valor =0.

    si te fijas en esta factura el codigo 40094 se facturo dos veces  puesto que se bonificacron 12 unidades (Regalia xU)




    miércoles, 4 de diciembre de 2019 18:59
  • Hola...

    He estado trabajando y casi lo consigo, pero no se por que me duplica filas de resultado

    Anexo:

            declare @FechaInicial datetime
            set @FechaInicial='2019-12-01'
    
            declare @FechaFinal datetime
            set @FechaFinal='2019-12-06'
            
    		declare @codigo int
            set @codigo =40001
    
       select convert(varchar(12),fecha_contabilizada,113) as Fecha,
              p.idproducto as Código,
              p.producto as Producto,
             (p.stock/cast(cantcaja as float) +(dv.cantidad/cast(dv.contenia as float))) as StockInicial,
    		 
    		 (select (numero_factura)
    		  from dbo.FacturasVirtuales
    		 where fecha_factura >= @FechaInicial and fecha_factura < @FechaFinal and idproducto=@codigo) as [Doc.Entrada],
    		  (select isnull(cantidad,0)
    		  from dbo.FacturasVirtuales
    		  where fecha_factura >= @FechaInicial and fecha_factura < @FechaFinal and idproducto=@codigo) as Entrada,
    		  
    		  dv.numero_factura as [Doc.Salida],
    		  dv.cantidad/cast(dv.contenia as float) as Salida,
    		  (p.stock/cast(p.cantcaja as float)) as Saldo
                
    			from dbo.Productos as p inner join
    			     dbo.DetalleVenta as dv on p.idproducto=dv.idproducto left join
    				 dbo.HistorialEntradasProductos as he on p.idproducto = he.idproducto
    where dv.fecha_contabilizada >= @FechaInicial and dv.fecha_contabilizada < @FechaFinal  and p.idproducto=@codigo

    Resultado:

    miércoles, 4 de diciembre de 2019 20:52
  • Hola Reynaldo Sanchez:

    No hagas eso.

    Las subconsultas no son buenas, no te van a dar buen rendimiento.

    Si tienes que devolver un stock de 10000 filas, esas dos consultas, que tienes como columnas las va a realizar por cada una de las 10000 filas.

    Estoy preparando un escenario para ti, pero no se si me dará tiempo hoy. No obstante, dale una leida a este artículo, porque esto es lo que puedes usar para tu escenario.

    https://javifer2.wordpress.com/2019/10/26/sumas-acumuladas/

    miércoles, 4 de diciembre de 2019 21:04
  • Muchas gracias Javi, por tu tiempo, leere el articulo y tranquilo si hoy no te da tiempo quizas mañana si....estare pendiente a tu respuesta.

    Saludos.

    miércoles, 4 de diciembre de 2019 22:44
  • Hola Reynaldo Sanchez:

    Lo primero el escenario.

    -- No ejecutar si tienes datos 
    drop table if exists historialEntradaProductos;
    drop table if exists DetalleVenta;
    drop table if exists historialSalidaProductos;
    drop table if exists productos;
    -- Creates
    create table historialEntradaProductos(
    idhistorial int,
    numero_entrada int,
    idproducto int,
    cantidad int,
    fecha datetime)
    go
    
    create table historialSalidaProductos(
    idhistorial int,
    numero_salida int,
    idproducto int,
    cantidad int,
    fecha datetime)
    go
    
    create table productos (
    idproducto int,
    producto nvarchar(50),
    descripcion nvarchar(200),
    u_m nvarchar(50),
    presentacion nvarchar(200),
    fecha_vencimiento datetime,
    precio_Venta_mayor numeric(18,2),
    precio_Venta_detalla numeric (18,2),
    stock int,
    stock_minimo int,
    cantcaja int)
    go
    
    create table DetalleVenta (
    idVenta int,
    numero_factura int,
    idproducto int,
    cantidad int,
    precio numeric(18,2),
    sub_total numeric(18,2),
    descuento numeric(18,2),
    total numeric(18,2),
    cancelado char (2),
    fecha_solicitada datetime,
    fecha_contabilizada datetime,
    mayor_detalle varchar(50),
    contenia int
    );
    
    go
    Insert into historialEntradaProductos 
    (idhistorial,numero_entrada,idproducto,cantidad,fecha)
    values
    (1,5000,40001,100,'20191204'),
    (1,5001,40001,100,'20191205'),
    (1,5002,40094,100,'20191204'),
    (1,5003,40095,100,'20191204');
    go
    
    insert into productos (idproducto,producto,descripcion ,u_m,presentacion,fecha_vencimiento,precio_Venta_mayor,
    precio_Venta_detalla,stock ,stock_minimo,cantcaja)
    values
    (40001,'Chicle de menta','Chicles',null,'1x24','20210101',100,15,20,10,24),
    (40094,'Frambuesa botella','Framb',null,'2x12','20191231',245,30,40,5,12);
    go
    
    insert into DetalleVenta (idVenta,numero_factura,idproducto,cantidad,precio, sub_total,
    descuento,total,cancelado,fecha_solicitada,fecha_contabilizada,mayor_detalle,contenia)
    values
    (1,100,40001,24,15,100,10,90,null, '20191204','20191204','VALORADO',24),
    (1,100,40001,2 ,15,100,10,90,null, '20191204','20191204','xC'		 ,24),
    (2,104,40094,24,15,100,10,90,null, '20191204','20191204','VALORADO',24),
    (2,104,40094,2 ,15,100,10,90,null, '20191204','20191204','xC'		 ,24),
    (3,105,40001,24,15,100,10,90,null, '20191205','20191205','VALORADO',24),
    (3,105,40001,2 ,15,100,10,90,null, '20191205','20191205','xC'		 ,24);
    go

    Ahora ya con algunos datos.

    DECLARE @FECHAINICIAL DATETIME = '20191204';
    DECLARE @FECHAFINAL DATETIME = '20200101';
    ;
    WITH ventas
         AS (SELECT p.idproducto
                  , p.descripcion
                  , dv.numero_factura AS docsalida
                  , CAST(DV.fecha_contabilizada AS DATE) AS fechaDoc
                  , SUM(CASE
                            WHEN dv.mayor_detalle = 'VALORADO'
                            THEN dv.cantidad
                        END) AS cantidad -- SOLO IMPORTAN LOS QUE MUEVEN STOCK
                  , 0 AS IdentificaMovimiento
             FROM productos p
                  INNER JOIN DetalleVenta dv ON dv.idproducto = p.idproducto
    		WHERE DV.fecha_contabilizada >= @FECHAINICIAL AND DV.fecha_contabilizada < @FECHAFINAL
                    GROUP BY p.idproducto
                           , p.descripcion
                           , dv.numero_factura
                           , CAST(DV.fecha_contabilizada AS DATE)),
         compras
         AS (SELECT P.idproducto
                  , P.descripcion
                  , HE.numero_entrada
                  , CAST(HE.fecha AS DATE) AS Fechadoc
                  , SUM(he.cantidad) AS cantidad
                  , 1 AS IdentificaMovimiento
             FROM Productos p
                  INNER JOIN historialEntradaProductos he ON p.idproducto = he.idproducto
    			  WHERE HE.fecha >= @FECHAINICIAL AND HE.fecha < @FECHAFINAL
                    GROUP BY P.idproducto
                           , P.descripcion
                           , HE.numero_entrada
                           , CAST(HE.fecha AS DATE)),
    		saldo_anterior AS
    		(
    		Select t.idproducto, sum(case when identificaMovimiento = 0 then cantidad else -1*cantidad end) as cantidad from (
    		SELECT p.idproducto
                  , SUM(CASE WHEN dv.mayor_detalle = 'VALORADO' THEN dv.cantidad END) AS cantidad -- SOLO IMPORTAN LOS QUE MUEVEN STOCK
                  , 0 AS IdentificaMovimiento
             FROM productos p
                  INNER JOIN DetalleVenta dv ON dv.idproducto = p.idproducto
    			  where dv.fecha_contabilizada < @FECHAINICIAL
    			  group by p.idproducto
    		 Union all
    		 SELECT p.idproducto
                  , SUM(cANTIDAD) AS cantidad -- SOLO IMPORTAN LOS QUE MUEVEN STOCK
                  , 1 AS IdentificaMovimiento
             FROM productos p
                  INNER JOIN historialEntradaProductos he ON he.idproducto = p.idproducto
    			  where he.fecha < @FECHAINICIAL
    			  group by p.idproducto
    			  ) as t
    			  group by t.idproducto
    
    		),
    	Agrupa As (
         SELECT idproducto
              , descripcion
              , numero_entrada
              , Fechadoc
              , cantidad
              , IdentificaMovimiento
         FROM Compras
         UNION ALL
         SELECT idproducto
              , descripcion
              , docsalida
              , fechaDoc
              , cantidad
              , IdentificaMovimiento
         FROM ventas
    	 )
    	 select a.Fechadoc
    		, p.idproducto
    		, p.descripcion
    		, isnull(s.cantidad,0) as Stock_inicial
    		, case when a.IdentificaMovimiento = 1 then cast(a.numero_entrada as varchar(30)) else '' end  as docEntrada, 
    			case when a.IdentificaMovimiento = 1 then a.cantidad else 0 end as entrada,
    			case when a.IdentificaMovimiento = 0 then cast(a.numero_entrada as varchar(30)) else '' end docSalida, 
    			case when a.IdentificaMovimiento = 0 then a.cantidad else 0 end as salida,
    			sum(case when a.identificamovimiento = 1 then a.cantidad else -1*a.cantidad end) over(partition by p.idproducto order by a.fechadoc
    	 rows unbounded preceding) as Saldo
    	 
    
    	 
    	  from productos p 
    		LEFT JOIN Agrupa a on p.idproducto = a.idproducto
    		LEFT JOIN saldo_anterior S on p.idproducto = s.idproducto
    		ORDER BY fechadoc
    	 

    Voy a ir destripandonte la query por partes, porque supongo que apriori, puede resultarte algo compleja. Si no conoces las tablas de expresión común y las tablas derivadas, este es tú momento.

    Derivada

    https://javifer2.wordpress.com/2019/11/06/tabla-derivada/

    Expresión común

    https://javifer2.wordpress.com/2018/12/18/with-cte-tablas-de-expresion-comun-1/

    Correlativas

    https://javifer2.wordpress.com/2018/12/18/with-cte-tablas-de-expresion-comun-2-correlativos/

    Ahora la solución

    Primer conjunto

    ITH ventas
         AS (SELECT p.idproducto
                  , p.descripcion
                  , dv.numero_factura AS docsalida
                  , CAST(DV.fecha_contabilizada AS DATE) AS fechaDoc
                  , SUM(CASE
                            WHEN dv.mayor_detalle = 'VALORADO'
                            THEN dv.cantidad
                        END) AS cantidad -- SOLO IMPORTAN LOS QUE MUEVEN STOCK
                  , 0 AS IdentificaMovimiento
             FROM productos p
                  INNER JOIN DetalleVenta dv ON dv.idproducto = p.idproducto
    		WHERE DV.fecha_contabilizada >= @FECHAINICIAL AND DV.fecha_contabilizada < @FECHAFINAL
                    GROUP BY p.idproducto
                           , p.descripcion
                           , dv.numero_factura
                           , CAST(DV.fecha_contabilizada AS DATE)),

    Da igual que le cobres o no a un cliente, lo que importa es el stock para tu informe, y si el tipo es regalado o no es indiferente, por tanto en este conjunto obtenemos la suma de las cantidades de los productos vendidos con su documento cuya fecha este entre lo parametrizado. Le añado una columna que me servirá p ara saber si suman o restan stock.

    Nota: ojo si el tema del stock es diferente, solo tienes que quitar o cambiar el case interior del sum

    Segundo conjunto.

      compras
         AS (SELECT P.idproducto
                  , P.descripcion
                  , HE.numero_entrada
                  , CAST(HE.fecha AS DATE) AS Fechadoc
                  , SUM(he.cantidad) AS cantidad
                  , 1 AS IdentificaMovimiento
             FROM Productos p
                  INNER JOIN historialEntradaProductos he ON p.idproducto = he.idproducto
    			  WHERE HE.fecha >= @FECHAINICIAL AND HE.fecha < @FECHAFINAL
                    GROUP BY P.idproducto
                           , P.descripcion
                           , HE.numero_entrada
                           , CAST(HE.fecha AS DATE)),

    Es igual que el anterior pero aquí contamos con el historico de entradas y estos movimientos se identifican como 1.

    saldo_anterior AS
    		(
    		Select t.idproducto, sum(case when identificaMovimiento = 0 then cantidad else -1*cantidad end) as cantidad from (
    		SELECT p.idproducto
                  , SUM(CASE WHEN dv.mayor_detalle = 'VALORADO' THEN dv.cantidad END) AS cantidad -- SOLO IMPORTAN LOS QUE MUEVEN STOCK
                  , 0 AS IdentificaMovimiento
             FROM productos p
                  INNER JOIN DetalleVenta dv ON dv.idproducto = p.idproducto
    			  where dv.fecha_contabilizada < @FECHAINICIAL
    			  group by p.idproducto
    		 Union all
    		 SELECT p.idproducto
                  , SUM(cANTIDAD) AS cantidad -- SOLO IMPORTAN LOS QUE MUEVEN STOCK
                  , 1 AS IdentificaMovimiento
             FROM productos p
                  INNER JOIN historialEntradaProductos he ON he.idproducto = p.idproducto
    			  where he.fecha < @FECHAINICIAL
    			  group by p.idproducto
    			  ) as t
    			  group by t.idproducto
    
    		),


    Utilizando la misma consulta y técnica, obtenemos el saldo por producto cuya fecha de compra o venta sea anterior a la fecha de corte.

    Agrupa As (
         SELECT idproducto
              , descripcion
              , numero_entrada
              , Fechadoc
              , cantidad
              , IdentificaMovimiento
         FROM Compras
         UNION ALL
         SELECT idproducto
              , descripcion
              , docsalida
              , fechaDoc
              , cantidad
              , IdentificaMovimiento
         FROM ventas
    	 )

    Introduzco en un mismo conjunto los conjuntos anteriores, para simplificarte un poco el código.

    select a.Fechadoc , p.idproducto , p.descripcion , isnull(s.cantidad,0) as Stock_inicial , case when a.IdentificaMovimiento = 1 then cast(a.numero_entrada as varchar(30)) else '' end as docEntrada, case when a.IdentificaMovimiento = 1 then a.cantidad else 0 end as entrada, case when a.IdentificaMovimiento = 0 then cast(a.numero_entrada as varchar(30)) else '' end docSalida, case when a.IdentificaMovimiento = 0 then a.cantidad else 0 end as salida, sum(case when a.identificamovimiento = 1 then a.cantidad else -1*a.cantidad end) over(partition by p.idproducto order by a.fechadoc rows unbounded preceding) as Saldo from productos p LEFT JOIN Agrupa a on p.idproducto = a.idproducto LEFT JOIN saldo_anterior S on p.idproducto = s.idproducto

    order by fechadoc

    La salida no es más que obtener los datos de todos los productos, mezclados con lo anterior, y para el saldo, utilizamos la función de ventana

    Sumas acumuladas

    https://javifer2.wordpress.com/2019/10/26/sumas-acumuladas/

    Espero te ayude

    viernes, 6 de diciembre de 2019 9:47
  • Gracias Javier...ahorita mismo revisare a ver si me funciona....posteriormente te estare comentando,

    Muchas Gracias de antemano.

    Saludos.

    viernes, 6 de diciembre de 2019 18:35
  • Hola Javier muchas gracias por tu tiempo.

    La consulta es correcta, se logra con el objetivo del tema, pero hay algo que no tomaste en cuenta

    Y es el contiene de cada producto, por tal razón a la hora de hacer la consulta en mi base de datos los datos son irreales yo estoy haciendo la prueba con un producto que contiene 24 por cada caja,

    Te explico.

    En la BD este código 40001 que es Chicle De Menta hay en existencia stock=2400, pero esa tabla contiene una columna que se llama cantcaja y esta columna es de tipo entero y almacena el valor del contenido de cada producto,

    Por ejemplo:

    idproducto

    producto

    stock

    cantcaja

    40001

    Chicle De Menta

    2400

    24

    Si te fijas en stock tenemos 2400, pero este valor es lo que almacenamos en la BD, pero  al usuario no le mostramos ese 2400, porque a la hora de hacer una venta ellos solo ponen la cantidad de cajas que van a venderse, es decir si venden 4 cajas no ponen 96 si no que ellos digitan 4 directamente y el sistema se encarga de hacer la conversión necesaria, que sería el contenido de la caja * la cantidad digitada 24*4=96; de igual forma pasa a la hora de mostrarle el stock de cada producto al usuario el sistema divide el stock / el contenido de cada producto(cantcaja), para este mismo producto sería de la siguiente manera: 2400/24=100; el resultado dice que hay 100 cajas en stock, entonces el sistema le muestra al usuario 100 cajas en ver de 2400 unidades que hay almacenado en la BD.

    Por esta razón en la Query hay que tomar en cuenta esa columna, de modo que para calcular el stock inicial hay que hacerlo tomando en cuenta la columna de cantcaja en la tabla de productos, es decir tomamos de la tabla de Productos el stock lo dividimos entre la columna cantcaja , le restamos todas las ventas de la tabla DetalleVenta y posteriormente le sumamos  todas las compras de la tabla HistorialEntradasProductos  y así conseguimos el stock_inicial:

    Ejemplo:

    Para este ejemplo partiremos de los datos del mismo código de arriba descrito, y dice, que en la BD hay 2,400 entonces seria así:

    2400/24= 100 esto sería nuestro estock_inicial, ahora bien, suponiendo que  en la fecha de entre el 04-12-2019 y el 06-12-2019 se realizaron dos ventas

    Factura: 7329 con una cantidad de 480

    Factura: 7230 con una cantidad de 720, entonces lo primero es tomar encuenta el siguiente parámetro y es que en la tabla DetalleVentas  hay una columna que se llama mayor_detalle esta columna si bien es cierto nos indica que si es valorado o no un producto vendido, pero también nos brinda otro dato y es que esta columna también nos dice si la cantidad vendida es por caja o por unidades, es decir

    Nota: la columna mayor_detalle almacena solo tres posibles cadenas

    1. Valorado
    2. Regalía xC
    3. Regalía xU

    Que si mayor_detalle  = Valorado entonces quiere decir que las cantidades salieron por cajas entonces para conocer la cantidades de cajas hay que dividir la cantidad entre el contenido de cada caja que esta almacenado en la columna de contenia de la tabla de Detallesventas

    Si mayor_detalle  = Regalía xC entonces quiere decir que las cantidades salieron por cajas entonces para conocer la cantidades de cajas hay que dividir la cantidad entre el contenido de cada caja que esta almacenado en la columna de contenia de la tabla de Detallesventas

    Si mayor_detalle  = Regalía xU entonces quiere decir que las cantidades salieron por unidades no hay necesidad de realizar ninguna división

    Entonces a hora para ver que cantidad se vendió de cada producto y así restarlo de forma correcta a nuestro Stock_Inicial ya calculado, tenemos que tomar en cuenta esta columna mayor_detalle y lo hacemos de la siguiente manera:

    (case

           when (dv.mayor_detalle) ='VALORADO' then (dv.cantidad / cast(dv.contenia as float))

           when (dv.mayor_detalle) ='Regalía xC' then ((dv.cantidad) / cast(dv.contenia as float))

        when (dv.mayor_detalle) ='Regalía xU' then (dv.cantidad)

     End) as CANTIDAD

    Factura: 7329 con una cantidad de 480 =480/24= 20cajas

    Factura: 7230 con una cantidad de 720=720/24= 30cajas

    Ahora que ya conocemos las cantidades en cajas que se venideron y el Stock_Inicial, podemos hacer nuestro Kardez para este producto y quedaría así más o menos:

    Fecha

    Código

    Producto

    Stock Inicial

    Doc.Entrada

    Entrada

    Doc.Salida

    Salida

    Saldo

    04-Dic-2019

    40001

    Chicle De Menta

    100

    0

    0

    7329

    20

    80

    04-Dic-2019

    40001

    Chicle De Menta

    80

    0

    0

    7330

    30

    50

    05-Dic-2019

    40001

    Chicle De Menta

    50

    1212

    60

    0

    0

    110

    En el Query que has creado primero muestras las compras, pero eso no importa al final es el mismo resultado, solo que con diferente orden.

    Bueno espero me hayas entendió y me puedas ayudar a adaptar tu Query al requerimiento que necesito, Gracias.

    viernes, 6 de diciembre de 2019 20:50
  • Hola Reynaldo Sanchez:

    Usuario dijita : 2cajas Su contiene(cantcajas)=24; entonces el sistema hace la conversión a: 24*2 = 48, este 48 es la cantidad que se esta vendiendo y que se almacena en Detalleventa columna “cantidad”;

    Si el sistema hace la conversión y lo que anota en detalleventa columna cantidad son las unidades, cosa por otra parte que a mi entender, es correcta, el stock se analiza en unidades, se tiene en unidades.

    Entonces la solución, no es hacer multiplicaciones, ni divisiones, en las operaciones, sino en la última salida del query, mostrar la info por cajas.

    Ojo te voy a dar la query y verás que he multiplicado por 1.0 la salida, para hacer un float. Con mis datos, los del ejemplo, salen cantidades con decimales.

    Le puedes hacer un cast ( (....) as decimal(20,2)) por ejemplo para que salga solo con 2 decimales.

    DECLARE @FECHAINICIAL DATETIME = '20191204';
    DECLARE @FECHAFINAL DATETIME = '20200101';
    ;
    WITH ventas
         AS (SELECT p.idproducto
                  , p.descripcion
                  , dv.numero_factura AS docsalida
                  , CAST(DV.fecha_contabilizada AS DATE) AS fechaDoc
                  , SUM(CASE
                            WHEN dv.mayor_detalle = 'VALORADO'
                            THEN dv.cantidad
                        END) AS cantidad -- SOLO IMPORTAN LOS QUE MUEVEN STOCK
                  , 0 AS IdentificaMovimiento
             FROM productos p
                  INNER JOIN DetalleVenta dv ON dv.idproducto = p.idproducto
    		WHERE DV.fecha_contabilizada >= @FECHAINICIAL AND DV.fecha_contabilizada < @FECHAFINAL
                    GROUP BY p.idproducto
                           , p.descripcion
                           , dv.numero_factura
                           , CAST(DV.fecha_contabilizada AS DATE)),
         compras
         AS (SELECT P.idproducto
                  , P.descripcion
                  , HE.numero_entrada
                  , CAST(HE.fecha AS DATE) AS Fechadoc
                  , SUM(he.cantidad) AS cantidad
                  , 1 AS IdentificaMovimiento
             FROM Productos p
                  INNER JOIN historialEntradaProductos he ON p.idproducto = he.idproducto
    			  WHERE HE.fecha >= @FECHAINICIAL AND HE.fecha < @FECHAFINAL
                    GROUP BY P.idproducto
                           , P.descripcion
                           , HE.numero_entrada
                           , CAST(HE.fecha AS DATE)),
    		saldo_anterior AS
    		(
    		Select t.idproducto, sum(case when identificaMovimiento = 0 then cantidad else -1*cantidad end) as cantidad from (
    		SELECT p.idproducto
                  , SUM(CASE WHEN dv.mayor_detalle = 'VALORADO' THEN dv.cantidad END) AS cantidad -- SOLO IMPORTAN LOS QUE MUEVEN STOCK
                  , 0 AS IdentificaMovimiento
             FROM productos p
                  INNER JOIN DetalleVenta dv ON dv.idproducto = p.idproducto
    			  where dv.fecha_contabilizada < @FECHAINICIAL
    			  group by p.idproducto
    		 Union all
    		 SELECT p.idproducto
                  , SUM(cANTIDAD) AS cantidad -- SOLO IMPORTAN LOS QUE MUEVEN STOCK
                  , 1 AS IdentificaMovimiento
             FROM productos p
                  INNER JOIN historialEntradaProductos he ON he.idproducto = p.idproducto
    			  where he.fecha < @FECHAINICIAL
    			  group by p.idproducto
    			  ) as t
    			  group by t.idproducto
    
    		),
    	Agrupa As (
         SELECT idproducto
              , descripcion
              , numero_entrada
              , Fechadoc
              , cantidad
              , IdentificaMovimiento
         FROM Compras
         UNION ALL
         SELECT idproducto
              , descripcion
              , docsalida
              , fechaDoc
              , cantidad
              , IdentificaMovimiento
         FROM ventas
    	 )
    	 select a.Fechadoc
    		, p.idproducto
    		, p.descripcion
    		, isnull(s.cantidad,0) as Stock_inicial
    		, case when a.IdentificaMovimiento = 1 then cast(a.numero_entrada as varchar(30)) else '' end  as docEntrada, 
    			case when a.IdentificaMovimiento = 1 then a.cantidad * (1.0) / p.cantcaja else 0 end as entrada,
    			case when a.IdentificaMovimiento = 0 then cast(a.numero_entrada as varchar(30)) else '' end docSalida, 
    			case when a.IdentificaMovimiento = 0 then a.cantidad * (1.0)/ p.cantcaja else 0 end as salida,
    			sum(case when a.identificamovimiento = 1 then a.cantidad * (1.0)/p.cantcaja else -1*a.cantidad*(1.0)/p.cantcaja end) over(partition by p.idproducto order by a.fechadoc
    	 rows unbounded preceding) as Saldo
    	  from productos p 
    		LEFT JOIN Agrupa a on p.idproducto = a.idproducto
    		LEFT JOIN saldo_anterior S on p.idproducto = s.idproducto
    		ORDER BY fechadoc

    sábado, 7 de diciembre de 2019 5:31
  • Buenos días Javier Fernández, es un gusto saludarte.

    Estimado, he terminado de revisar la Query que me has compartido y la he ejecutado contra la BD que tengo en funcionamiento y los datos que me arroja son correctos y además me los muestra de la forma en la que deseo, a como dijiste en tu ultima observación,

    Entonces la solución, no es hacer multiplicaciones, ni divisiones, en las operaciones, sino en la última salida del Query, mostrar la info por cajas.

    Exacta mente no era necesario de realizar nada de eso, solo que en mi caso como necesitaba el Saldo Inicial en cajas entonces lo que hice fue dividirlo entre el contenido del producto en cuestión y sumarle el stock actual, pero esta división se hace al final de la Query donde sale el resultado.

    select a.Fechadoc

                 , p.idproducto

                 , p.descripcion

                 ,isnull(s.cantidad / cast(p.cantcaja as float),0) + p.stock / cast(p.cantcaja as float) as Stock_inicial ……….

    Ya con esto doy por serrado este tema, si antes agradecerte a ti Javier Fernández , por tu tiempo y dedicación que tuviste para dar la solución precisa, sin duda se que con este tema y la repuesta a la que se ha concluido, sé que servirá de ayuda a muchas personas, ya que he visto que, este tema de Kardez es bastante debatido en este foro y la verdad, que no logre encontrar nada parecido a lo que se logró en esta ocasión, así que quedara como guía y soporte para muchos, Bendiciones.

    Les comparto La Query:

    Nota: Si en tu sistema  las cantidades las muestran como tal "unidades", no es necesario de hacer ninguna division o algun tipo de conversion, entonces estarian ocupando la primera Query que nos compartio Javier.

    DECLARE @FECHAINICIAL DATETIME = '20191204';
    DECLARE @FECHAFINAL DATETIME = '20191206';
    DECLARE @CODIGO DATETIME = 40001;
    ;
    WITH ventas
         AS (SELECT p.idproducto
                  , p.descripcion
                  , dv.numero_factura AS docsalida
                  , CAST(DV.fecha_contabilizada AS DATE) AS fechaDoc
                  , SUM(dv.cantidad) cantidad -- SOLO IMPORTAN LOS QUE MUEVEN STOCK
                  , 0 AS IdentificaMovimiento
             FROM productos p
                  INNER JOIN DetalleVenta dv ON dv.idproducto = p.idproducto
    		WHERE DV.fecha_contabilizada >= @FECHAINICIAL AND DV.fecha_contabilizada < @FECHAFINAL and dv.idproducto = @CODIGO
                    GROUP BY p.idproducto
                           , p.descripcion
                           , dv.numero_factura
                           , CAST(DV.fecha_contabilizada AS DATE)),
         compras
         AS (SELECT P.idproducto
                  , P.descripcion
                  , HE.numero_entrada
                  , CAST(HE.fecha AS DATE) AS Fechadoc
                  , SUM(he.cantidad) AS cantidad
                  , 1 AS IdentificaMovimiento
             FROM Productos p
                  INNER JOIN HistorialEntradasProductos he ON p.idproducto = he.idproducto
    			  WHERE HE.fecha >= @FECHAINICIAL AND HE.fecha < @FECHAFINAL and he.idproducto = @CODIGO
                    GROUP BY P.idproducto
                           , P.descripcion
                           , HE.numero_entrada
                           , CAST(HE.fecha AS DATE)),
    		saldo_anterior AS
    		(
    		Select t.idproducto ,sum(case when identificaMovimiento = 0 then cantidad else -1*cantidad end) as cantidad from (
    		SELECT p.idproducto
                  , SUM(dv.cantidad) AS cantidad -- SOLO IMPORTAN LOS QUE MUEVEN STOCK
                  , 0 AS IdentificaMovimiento
             FROM productos p
                  INNER JOIN DetalleVenta dv ON dv.idproducto = p.idproducto
    			  where dv.fecha_contabilizada >= @FECHAINICIAL and dv.fecha_contabilizada < @FECHAFINAL and dv.idproducto = @CODIGO
    			  group by p.idproducto
    		 Union all
    		  select he.idproducto
             ,sum(he.cantidad) AS cantidad 
    		 ,1 AS IdentificaMovimiento 
    		 from HistorialEntradasProductos as he inner join
    		      Productos as p on p.idproducto = he.idproducto 
    		 where he.fecha >= @FECHAINICIAL and he.fecha < @FECHAFINAL and he.idproducto =@CODIGO 
    		 group by he.idproducto
    			  ) as t
    			  group by t.idproducto
    
    		),
    	Agrupa As (
         SELECT idproducto
              , descripcion
              , numero_entrada
              , Fechadoc
              , cantidad
              , IdentificaMovimiento
         FROM Compras
         UNION ALL
         SELECT idproducto
              , descripcion
              , docsalida
              , fechaDoc
              , cantidad
              , IdentificaMovimiento
         FROM ventas
    	 )
    	 select a.Fechadoc
    		, p.idproducto
    		, p.descripcion
    		, isnull(s.cantidad / cast(p.cantcaja as float),0) + p.stock / cast(p.cantcaja as float) as Stock_inicial
    		, case when a.IdentificaMovimiento = 1 then cast(a.numero_entrada as varchar(30)) else '' end  as docEntrada, 
    			case when a.IdentificaMovimiento = 1 then a.cantidad * (1.0) / cast( p.cantcaja as float) else 0 end as entrada,
    			case when a.IdentificaMovimiento = 0 then cast(a.numero_entrada as varchar(30)) else '' end docSalida, 
    			case when a.IdentificaMovimiento = 0 then a.cantidad * (1.0)/ cast(p.cantcaja as float) else 0 end as salida,
    			sum(case 
    			when a.identificamovimiento = 1 then 
    			a.cantidad * (1.0)/cast(p.cantcaja as float) + isnull(s.cantidad / cast(p.cantcaja as float),0) + p.stock / cast(p.cantcaja as float)
    			else -1*a.cantidad*(1.0)/p.cantcaja end) over(partition by p.idproducto order by a.fechadoc
    	 rows unbounded preceding) as Saldo
    	  from productos p 
    		inner JOIN Agrupa a on p.idproducto = a.idproducto
    		inner JOIN saldo_anterior S on p.idproducto = s.idproducto
    		ORDER BY fechadoc


    Resultado:

    Hasta pronto.



    sábado, 7 de diciembre de 2019 17:43

  • De nada. Hasta la proxima

    sábado, 7 de diciembre de 2019 17:51