Insert into table leaving null the old record

Asked

Viewed 34 times

-1

Good morning, you guys! I have the following code below, where I am trying to save a value of kg per day, I am using as the basis the day without time, however when setting the value for any day, when changing the day and rotating again, it arrow the value on the new day, but returns to NULL the day before. If you can help me, it’s for a job assignment.

Insert into CompraDiariaBI (DiaCompra)
select top 1  convert (date,DtInc)DiaCompra from FFardosCompra where convert (date, DtInc)>='2020-04-22' and convert (date, DtInc)<='2020-04-22'  


update  CompraDiariaBI  set Matriz_CSul_KG= (select sum(coalesce(PesoFardo,0))
from FFardosCompra fc
inner join FClasseCompra ci on (ci.CodClasseCompra=fc.CodClasseCompra)
inner join FProdutor p  on (p.CodProd=fc.CodProd and p.CodFilial=1)
inner join FProdInstr pi on pi.CodProd=fc.CodProd and pi.CodSafra=18
inner join FSupInstrSafra sis with(nolock) on (sis.CodInstr = pi.CodInstr and p.CodProd = pi.CodProd and sis.CodSafra=18)
left join FCargaProd cp  on cp.CodCargaProd = fc.CodCargaProd 
where fc.CodSafra = 18 and ci.CodVar=10 and fc.Status='A' and fc.PesoTransf is null and sis.CodSupervisor=16 
and convert (date,fc.DtInc)>='2020-04-22' and convert (date, fc.DtInc)<='2020-04-22' and tmp.DiaCompra=convert (date,fc.DtInc))  from CompraDiariaBI tmp

In the image below, I circled scprit again changing the day, it set the value for day 22 but left NULL the value of day 21.

valor

How can I solve this, since every day a new value has to be inserted, and the old ones must be maintained?

sql

  • Without the structure, it’s hard to identify. But you can try to isolate the problems by first making the initial select and checking if it does not return the two days. You should also use Where in the date parameter with the equals sign, as you are comparing whether the start date is greater than or equal to 2020-04-22 AND ALSO less than or equal to 2020-04-22. Example: Convert (date, Fc.Dtinc) == '2020-04-22' only.

  • @flvrm92 what structure do you say? I used only the same, however the problem remains, I am using Where, there is somewhere that it is missing?

1 answer

0

Try using the following query

DECLARE
@pesoFardo DECIMAL

Insert into CompraDiariaBI
    (DiaCompra)
select top 1
    fc.DtInc
from FFardosCompra as fc
where fc.DtInc = '2020-04-22'

select @pesoFardo = sum(coalesce(PesoFardo,0)
from FFardosCompra fc
    inner join FClasseCompra ci on ci.CodClasseCompra=fc.CodClasseCompra
    inner join FProdutor p on p.CodProd=fc.CodProd and p.CodFilial=1
    inner join FProdInstr pi on pi.CodProd=fc.CodProd and pi.CodSafra=18
    inner join FSupInstrSafra sis with(nolock) on (sis.CodInstr = pi.CodInstr and p.CodProd = pi.CodProd and sis.CodSafra=18)
    left join FCargaProd cp on cp.CodCargaProd = fc.CodCargaProd
where fc.CodSafra = 18 and ci.CodVar= 10 and fc.Status='A' and fc.PesoTransf is null and sis.CodSupervisor=16
    and fc.DtInc = '2020-04-22'


-- select @pesoFardo

update  CompraDiariaBI  set Matriz_CSul_KG = @pesoFardo where DiaCompra = '2020-04-22'

Uncheck the penultimate line (-- select @pesoFardo) to check that the result is not null and makes sense with your Where. Comment the last line (-- ) not to make the change before making sure it only updates one record.

I recommend always using a key field (PK) to update the values in the tables, thus decreasing the risk of updating records that should not be updated.

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.