mercredi 6 mai 2015

how to perfom an update with group by?

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