wielgeraats
Starting Member
3 Posts |
Posted - 2013-03-28 : 11:00:21
|
The stored procedure:create procedure sp_bywerken_voorrcumdag @mutdatum datetimeasset nocount on -- Uitzetten berichteninsert into VOORRCUMDAG(product,datum,vvp,voorraad_ve,voorraad_kg,voorraad_wrd,gereserveerd_ve,gereserveerd_kg,gereserveerd_wrd,levering_klant_ve,levering_klant_kg,levering_klant_wrd,productie_verbruikt_ve,productie_verbruikt_kg,productie_verbruikt_wrd,factregels,omzet,winst)selectp.id,@mutdatum,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0from product as p WHERE (P.Affiliate = '1fffdda0-a5dc-44f1-bdec-971c46a5c8d0') AND (P.IsGroup = 0) and(SELECT SUM(TS.Weight) FROM Stockmutation TS WHERE TS.Product = P.Id AND TS.Affiliate = '1fffdda0-a5dc-44f1-bdec-971c46a5c8d0' and TS.modified < @mutdatum ) <> 0 group by p.idupdate VOORRCUMDAGset voorraad_ve = isnull(( select convert(integer, sum(st.quantity)) from stockmutation as st where st.product = VOORRCUMDAG.product and VOORRCUMDAG.datum = @mutdatum and st.modified < @mutdatum ),0)where diverse_tabellen_views.dbo.VOORRCUMDAG.datum = @mutdatumupdate diverse_tabellen_views.dbo.VOORRCUMDAGset voorraad_kg = isnull(( select convert(integer, sum(st.weight)) from stockmutation as st where st.product = VOORRCUMDAG.product and VOORRCUMDAG.datum = @mutdatum and st.modified < @mutdatum ),0)where VOORRCUMDAG.datum = @mutdatumThe stored procedure has more update statements in it but they have no additional value for the question.In another table dates are defined. The storage procedure has to be called for every date. |
|
|