I have concerns with an update statement. The primary key of my table is composed of the following columns:
ReferencePiece, NumeroPhase, AncienPoste,DateStrategie, DateCadence, NomProgramme, VersionProgramme.
Here is my procedure:
update S
set S.stock=T.Stock-T.NombrePiecesParAvion*T.CadenceProgramme
from SITUATIONS_Final_1 S inner join SITUATIONS_Final_1 T
on S.ReferencePiece=T.ReferencePiece and S.NumeroPhase=T.NumeroPhase and S.AncienPoste=T.AncienPoste
and T.DateDebProduction<S.DateDebProduction and T.stock = (select min(T.stock) from SITUATIONS_Final_1 T)
where S.DateDebProduction=(select min(S.datedebproduction) as 'date1'
from SITUATIONS_Final_1 S inner join SITUATIONS_Final_1 T
on S.ReferencePiece=T.ReferencePiece and S.NumeroPhase=T.NumeroPhase and S.AncienPoste=T.AncienPoste
where S.datedebproduction>T.DateDebProduction and T.stock = (select min(T.stock) from SITUATIONS_Final_1 T))
But actually, as there is no 'group by' on my update statement, It only takes into account the Min(stock) of all my references and do the update for the future dates.
I tried to do it as following but I'm sure it's much easier than that :
update S
set S.stock=T.Stock-T.NombrePiecesParAvion*T.CadenceProgramme
from SITUATIONS_Final_1 S inner join SITUATIONS_Final_1 T
on S.ReferencePiece=T.ReferencePiece and S.NumeroPhase=T.NumeroPhase and S.AncienPoste=T.AncienPoste
and T.DateDebProduction<S.DateDebProduction inner join (select referencepiece,NumeroPhase,AncienPoste,DateStrategie,
nomprogramme,versionprogramme, datedebproduction, min(stock) StockMini from SITUATIONS_Final_1 group by ReferencePiece,NumeroPhase,AncienPoste,DateStrategie,
nomprogramme,versionprogramme,DateDebProduction)
F on F.ReferencePiece=T.ReferencePiece and T.Stock=F.StockMini
inner join (select referencepiece,NumeroPhase,AncienPoste,DateStrategie,
nomprogramme,versionprogramme, min(datedebproduction) datemini,stock
from SITUATIONS_Final_1 group by ReferencePiece,NumeroPhase,AncienPoste,DateStrategie,
nomprogramme,versionprogramme,DateDebProduction,stock) Z on F.ReferencePiece=Z.ReferencePiece
where T.DateDebProduction=Z.datemini and Z.datemini=F.DateDebProduction and Z.Stock=F.StockMini and T.ReferencePiece=Z.ReferencePiece and S.datedebproduction>T.DateDebProduction
and T.stock=F.StockMini and S.ReferencePiece=T.ReferencePiece and T.ReferencePiece=F.ReferencePiece and F.numerophase=Z.numerophase
and T.numerophase=Z.numerophase and F.AncienPoste=Z.AncienPoste and T.AncienPoste=Z.AncienPoste and F.DateStrategie=Z.DateStrategie and T.DateStrategie =Z.DateStrategie
and F.nomprogramme=Z.nomprogramme and T.nomprogramme=Z.nomprogramme and F.versionprogramme=Z.versionprogramme and T.versionprogramme=Z.versionprogramme
and S.ReferencePiece=T.ReferencePiece and S.NumeroPhase=T.NumeroPhase and S.AncienPoste=T.AncienPoste and S.nomprogramme=T.nomprogramme
and S.versionprogramme=T.versionprogramme and S.DateStrategie=T.datestrategie
But it doesn't perform well: It updates the same rows (the sames dates). I need this procedure to do something like that:
Referencepiece=RefA DateDebProduction='02/05/2015' Stock=4 NbrePieces=2 CadenceProgramme=1
For the same reference and the next date The stock should be= 4-2*1=2... Actually, this procedure is contained on another one. It launches it 5 times so that I can be sure that It will update all my rows. If you could help me finding a better way to update all rows without launching my procedure many times it would be great.
Here is a sample of data:
ReferencePiece NumeroPhase AncienPoste DateStrategie Charge NomProgramme VersionProgramme CadenceProgramme DateCadence DateDebProduction NombrePiecesParAvion Stock
RefA001 10 114179A 02/05/2015 0.000 Z540 A BGR 29.000 01/03/2015 01/03/2015 1.000 23.000
RefA001 10 114179A 02/05/2015 0.000 Z540 A BGR 25.000 01/04/2015 01/04/2015 1.000 NULL
RefA001 10 114179A 02/05/2015 0.000 Z540 A BGR 19.000 01/05/2015 01/05/2015 1.000 NULL
RefA001 10 114179A 02/05/2015 0.000 Z540 A BGR 25.000 01/06/2015 01/06/2015 1.000 NULL
RefXY001 10 1MR704 02/05/2015 0.220 X220 RM 2.000 01/05/2015 01/05/2015 2.000 12.000
RefXY001 10 1MR704 02/05/2015 0.220 X220 RM 2.000 01/06/2015 01/06/2015 2.000 8.000
RefXY001 10 1MR704 02/05/2015 0.220 X220 RM 2.000 01/07/2015 01/07/2015 2.000 NULL
RefXY001 10 1MR704 02/05/2015 0.220 X220 RM 3.000 01/08/2015 01/08/2015 2.000 NULL
As you can notice in this sample (where I've launched my procedure once), It updated the second row of the reference 'RefXY001' where stock=12-2*2=8, because 12<23. As I run this procedure, It will update only future dates of reference 'RefXY001' because the smallest stock is its.
Aucun commentaire:
Enregistrer un commentaire