none
Ingreso Consumo Saldo Con trigers en SQL SERVER 2014 RRS feed

  • Pregunta

  • Buenos dias de antemano me disculpo por molestar muy seguido pero la verdad tengo mucho interes en aprender y aqui siempre aprendo.

    debo mostrar los movimientos de combustibles tanto gasolina como Diésel tipo un kardex o como en contabilidad.

    como dice en el titulo Ingreso Consumo Saldo

    y lo estoy intentando hacer con Triggers ( no se si es la mejor manero que si no la es y uds. me sugiereon otra manera pues la optare) para ello cree este escenario.

    les paso las tablas completas

    create table IngresoCompraCombustible
    (
    Id_IngresoCombustible int primary key identity(1,1),
    IdProducto int not null,
    FechaCompraFactura date not null,
    Cantidad decimal(10,2) not null,
    FechaIngresoCombustible datetime not null default GETDATE(),
    IdUsuario int not null,
    );
    go
    alter table IngresoCompraCombustible add CONSTRAINT FK_IngresoCompraCombustible_Producto_Material FOREIGN KEY (IdProducto) REFERENCES Producto_Material(IdProducto)
    ALTER TABLE IngresoCompraCombustible add CONSTRAINT FK_IngresoCompraCombustible_LoginAutenticar FOREIGN KEY (IdUsuario) REFERENCES LoginAutenticar(idUsuario)
    insert into IngresoCompraCombustible(IdProducto,FechaCompraFactura,Cantidad,FechaIngresoCombustible,IdUsuario)
    VALUES(8,'2018-01-01',100000,GETDATE(),1)
    SELECT * FROM IngresoCompraCombustible
    delete from IngresoCompraCombustible
    create table MovimientoCombustible
    (
    Id_MovimientoCombustible int primary key identity(1,1),
    IdProducto int not null,
    Fecha_Movimiento date not null,
    IngresoCombustible decimal(10,0) not null,
    EgresoCombustible decimal(10,2) not null,
    SaldoCombustible as CAST((IngresoCombustible - EgresoCombustible) AS DECIMAL(10,2))
    );
    GO
    SELECT * FROM MovimientoCombustible
    GO
    CREATE TRIGGER [dbo].[InsertarCompraCombustible]
    on [dbo].IngresoCompraCombustible
    AFTER INSERT
    AS
    BEGIN
    SET NOCOUNT ON;
    DECLARE
    
    @IdProducto int,
    @Fecha_Movimiento datetime,
    @Cantidad decimal(10,2)
    select 
    @IdProducto = IdProducto,
    @Fecha_Movimiento = FechaIngresoCombustible,
    @Cantidad = Cantidad
    
    FROM INSERTED
    INSERT MovimientoCombustible(IdProducto,Fecha_Movimiento,IngresoCombustible,EgresoCombustible) values(@IdProducto,@Fecha_Movimiento,@Cantidad,0)
    end;
    go
    --select * from MovimientoCombustible
    CREATE TABLE [dbo].[DetalleSalidaCombustible](
    	[IdDetalleSalidaCombustible] [int] IDENTITY(1,1) NOT NULL,
    	[IdMaestroSalidaCombustible] [int] NOT NULL,
    	[NumeroBoleta] [nvarchar](20) NULL,
    	[FechaBoleta] [date] NOT NULL,
    	[IdEquipo] [int] NULL,
    	[IdProducto] [int] NULL,
    	[Cantidad] [decimal](18, 2) NOT NULL,
    	[AutorizadoPor] [nvarchar](30) NULL,
     CONSTRAINT [PK__DetalleS__53B5C0453384C283] PRIMARY KEY CLUSTERED 
    (
    	[IdDetalleSalidaCombustible] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
     CONSTRAINT [Uk_NumeroBoleta] UNIQUE NONCLUSTERED 
    (
    	[NumeroBoleta] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    ALTER TABLE [dbo].[DetalleSalidaCombustible]  WITH CHECK ADD  CONSTRAINT [Fk_Equipo_D_Combustible] FOREIGN KEY([IdEquipo])
    REFERENCES [dbo].[Equipos] ([IdEquipo])
    ON UPDATE CASCADE
    ON DELETE CASCADE
    GO
    
    ALTER TABLE [dbo].[DetalleSalidaCombustible] CHECK CONSTRAINT [Fk_Equipo_D_Combustible]
    GO
    
    ALTER TABLE [dbo].[DetalleSalidaCombustible]  WITH CHECK ADD  CONSTRAINT [fK_MaestroSalidaCombustible_DCombustible] FOREIGN KEY([IdMaestroSalidaCombustible])
    REFERENCES [dbo].[MaestroSalidaCombustible] ([IdMaestroSalidaCombustible])
    GO
    
    ALTER TABLE [dbo].[DetalleSalidaCombustible] CHECK CONSTRAINT [fK_MaestroSalidaCombustible_DCombustible]
    GO
    
    ALTER TABLE [dbo].[DetalleSalidaCombustible]  WITH CHECK ADD  CONSTRAINT [Fk_Producto_D_Combustible] FOREIGN KEY([IdProducto])
    REFERENCES [dbo].[Producto_Material] ([IdProducto])
    ON UPDATE CASCADE
    ON DELETE CASCADE
    GO
    
    ALTER TABLE [dbo].[DetalleSalidaCombustible] CHECK CONSTRAINT [Fk_Producto_D_Combustible]
    GO
    
    
    
    CREATE TRIGGER [dbo].[InsertarSalidaCombustible]
    on [dbo].DetalleSalidaCombustible
    AFTER INSERT
    AS
    BEGIN
    SET NOCOUNT ON;
    DECLARE
    
    @IdProducto int,
    --@Fecha_Movimiento datetime,
    @Cantidad decimal(10,2)
    select 
    @IdProducto = IdProducto,
    --@Fecha_Movimiento = default GETDATE(),
    @Cantidad = Cantidad
    
    FROM INSERTED
    INSERT MovimientoCombustible(IdProducto,Fecha_Movimiento,IngresoCombustible,EgresoCombustible) values(@IdProducto,GETDATE(),0,@Cantidad)
    end

    ya me hace los movimientos como lo muestro en la imagen

     pero está claro que necesito hacer la operación y es ahi donde pido la ayuda de uds. les repito no se si sera la mejor manera de hacerlo pero es mi idea, de haber alguna mejor agradeceré me la hagan saber.

    buen día para todos

    Roberto

    martes, 26 de febrero de 2019 13:03

Respuestas

  • Hola Roberto:

    Es complicado seguirte, porque cambias mucho de idea y de ejemplos.

    Expones datos de 2005, pero tu captura es de 2019....

    WITH cte
         AS (SELECT CAST(a.Fecha_Hora AS DATE) AS fecha, 
                    SUM(CASE
                            WHEN a.Ingreso <> 0
                            THEN a.Ingreso
                        END) AS ingresos, 
                    SUM(CASE
                            WHEN a.Egreso <> 0
                            THEN a.Egreso
                        END) AS egresos
             FROM #Tmp AS a
             GROUP BY CAST(a.fecha_hora AS DATE))
         SELECT c.fecha, 
                ISNULL(c.ingresos, 0) AS Ingresos, 
                ISNULL(c.egresos, 0) AS egresos, 
                (ISNULL(c.ingresos, 0) - ISNULL(c.egresos, 0)) AS saldo
         FROM cte c;

    Si haces un sum y dentro haces un case te suma solo cuando se cumpla el case. Como esto lo puedes hacer tantas veces quieras, como columnas quieras, pues tienes sum para ingresos, sum para egresos. Luego y todo esto es porque supongo que lo quieres por dia, ya que por datetime2, no creo que sea.

    Espero te ayude

    • Propuesto como respuesta Pedro Alfaro martes, 26 de febrero de 2019 22:50
    • Marcado como respuesta Roberto C. Melgar miércoles, 27 de febrero de 2019 22:05
    martes, 26 de febrero de 2019 22:32

Todas las respuestas

  • Hola Roberto:

    Esa es una mala idea. Entre muchos otros inconvenientes, que pasaria si tu trigger fallA a veces, sin motivo aparente y cuando te des cuenta ya pasó por ejemplo una semana.

    Lo mas normal es crear una vista que calcula eso en tiempo de ejecucion.

    La otra opcion es utilizar procedimientos almacenados y envolver las insercciones o actualizaciones en una transaccion, para que la informacion de las diferentes tablas no quede "huerfana"

    • Propuesto como respuesta Pedro Alfaro martes, 26 de febrero de 2019 16:04
    martes, 26 de febrero de 2019 14:02
  • ya me asuste, y yo que pensé que estaba yendo bien, bueno Javi alguna idea de hacerlo con vistas por favor para intentar implementarlo

    Gracias

    Roberto

    • Propuesto como respuesta Pedro Alfaro martes, 26 de febrero de 2019 16:04
    • Votado como útil Pedro Alfaro martes, 26 de febrero de 2019 16:04
    martes, 26 de febrero de 2019 14:22
  • Deleted
    • Propuesto como respuesta Pedro Alfaro martes, 26 de febrero de 2019 16:04
    martes, 26 de febrero de 2019 14:59
  • la verdad me hace lo mismo esto lo que necesito hacer es la función o la operación para mi nuevo saldoCombustible

    pero muchas gracias, ya voy teniendo mas ideas.

    Roberto

    martes, 26 de febrero de 2019 16:12
  • Deleted
    martes, 26 de febrero de 2019 19:12
  • ahora esta mas claro gracias,

    ahora estimados por favor ayuda con este problema basado en este ejemplo 

    drop table #Tmp
    Create Table #Tmp(
    Fecha_Hora smalldatetime,
    No_Comprobante int,
    No_Linea smallint,
    Lote int,
    Pcb smallint,
    Pedido smallint,
    Ingreso smallint,
    Egreso smallint,
    Saldo smallint
    )
    go
     
    Insert into #Tmp values('2005-05-02 01:15:00',4,1,21,6,334,167,0,0)
    Insert into #Tmp values('2005-05-03 15:07:10',5,1,29,6,167,167,0,0)
    Insert into #Tmp values('2005-07-01 12:25:15',6,1,12,6,200,50,0,0)
    Insert into #Tmp values('2005-07-01 21:05:52',25,1,1,6,200,0,140,0)
    Insert into #Tmp values('2005-07-02 21:05:52',25,1,1,6,200,9,0,0)
    Insert into #Tmp values('2005-07-03 21:05:52',25,1,1,6,200,0,140,0)
    Insert into #Tmp values('2005-07-04 21:05:52',25,1,1,6,200,100,0,0)
    Insert into #Tmp values('2005-07-05 21:05:52',25,1,1,6,200,0,150,0)
    Insert into #Tmp values('2005-07-06 21:05:52',25,1,1,6,200,20,0,0)
    Insert into #Tmp values('2005-07-07 21:05:52',25,1,1,6,200,0,20,0)
    Insert into #Tmp values('2005-07-08 21:05:52',25,1,1,6,200,100,0,0)
    Insert into #Tmp values('2005-07-09 21:05:52',25,1,1,6,200,0,20,0)
    go
     
    select
            a.fecha_hora,
            a.ingreso,
            a.egreso,
            sum(b.ingreso - b.egreso) as saldo
    from
            #Tmp as a
            inner join
            #Tmp as b
            on b.Fecha_Hora <= a.Fecha_Hora
    group by
            a.fecha_hora,
            a.ingreso,
            a.egreso
    order by
            1 asc
    go
     
    drop table tmp
    go

    hice este código que es practicamente lo mismo.

    select	a.Fecha_Hora, 
    		a.IngresoCombustible, 
    		a.EgresoCombustible, 
    		SUM(b.IngresoCombustible-b.EgresoCombustible)
    		
    		 AS Saldo 
    from	MovimientoCombustible as a
    		inner join 
    		MovimientoCombustible as b
    		on b.Fecha_Hora <= a.Fecha_Hora
    		
    GROUP BY a.Fecha_Hora, a.IngresoCombustible, a.EgresoCombustible
    order by 1 asc

    funciona si ingreso de uno en uno los registros pero yo quiero ingresar un dagrid con mas de una fila si en hasta la segunda fila hace la operación correcta pero es porque ingrese una fila pero si ingreso varias ya no no hace lo mismo

    en la segunda fila debería ser 910 y en la tercera 830 pero miren como se cambia a 830, que hice mal basado en el ejemplo por favor.

    creo que tiene que ver la fecha con la hora por favor ayudenme necesito resolver esto 

    muchas gracias, muchas gracias

    Roberto

    martes, 26 de febrero de 2019 21:47
  • Hola Roberto:

    Es complicado seguirte, porque cambias mucho de idea y de ejemplos.

    Expones datos de 2005, pero tu captura es de 2019....

    WITH cte
         AS (SELECT CAST(a.Fecha_Hora AS DATE) AS fecha, 
                    SUM(CASE
                            WHEN a.Ingreso <> 0
                            THEN a.Ingreso
                        END) AS ingresos, 
                    SUM(CASE
                            WHEN a.Egreso <> 0
                            THEN a.Egreso
                        END) AS egresos
             FROM #Tmp AS a
             GROUP BY CAST(a.fecha_hora AS DATE))
         SELECT c.fecha, 
                ISNULL(c.ingresos, 0) AS Ingresos, 
                ISNULL(c.egresos, 0) AS egresos, 
                (ISNULL(c.ingresos, 0) - ISNULL(c.egresos, 0)) AS saldo
         FROM cte c;

    Si haces un sum y dentro haces un case te suma solo cuando se cumpla el case. Como esto lo puedes hacer tantas veces quieras, como columnas quieras, pues tienes sum para ingresos, sum para egresos. Luego y todo esto es porque supongo que lo quieres por dia, ya que por datetime2, no creo que sea.

    Espero te ayude

    • Propuesto como respuesta Pedro Alfaro martes, 26 de febrero de 2019 22:50
    • Marcado como respuesta Roberto C. Melgar miércoles, 27 de febrero de 2019 22:05
    martes, 26 de febrero de 2019 22:32
  • Estimado Javi muchas gracias por la colaboración. y respecto al ejemplo fue uno que encontré en Internet lo copie tal cual

    muchas gracias por laa ayuda es el resultado que esperaba.

    Roberto 

    miércoles, 27 de febrero de 2019 22:05