Samuele E. Locatelli
61 lines
2.2 KiB
Transact-SQL
61 lines
2.2 KiB
Transact-SQL
|
|
-- =============================================
|
|
-- Author: S.E. Locatelli
|
|
-- Create date: 2014.01.15
|
|
-- Description: Procedura per import dati in Package2Item da dati ProductionLogRaw
|
|
-- =============================================
|
|
CREATE PROCEDURE [ext].[stp_mergePackage2_OLD]
|
|
(
|
|
@source NVARCHAR(20) = 'ProductionLogRaw' -- nome della tabella sorgente tra ProductionLogRaw e ItemsRaw
|
|
)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
|
|
---- Create a temporary table variable to hold the output actions.
|
|
--DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));
|
|
|
|
-- -- import con MERGE... verifico tipo di caricamento richiesto!
|
|
--IF @source = 'ProductionLogRaw' -- carico da tracciato produzione
|
|
-- BEGIN
|
|
-- MERGE INTO Package2 AS Target
|
|
-- USING (
|
|
-- SELECT plr.CodPackag, SUM(QtyOUT) AS TotQta, COUNT(*) AS NumEv
|
|
-- , SUM(QtyOUT) / COUNT(*) AS PesoMedio
|
|
-- , SUM(QtyEXT) / COUNT(*) AS TaraMedia
|
|
-- FROM ext.ProductionLogRaw plr
|
|
-- INNER JOIN DiesDet dd ON plr.CodDies = dd.CodDies
|
|
-- WHERE ISNULL(plr.CodPackag, '') <> ''
|
|
-- GROUP BY plr.CodPackag
|
|
-- )
|
|
-- AS Source
|
|
-- ON Target.CodPackag = Source.CodPackag
|
|
-- WHEN MATCHED THEN
|
|
-- UPDATE SET TotQty = Source.TotQta
|
|
-- , [Weight] = Source.PesoMedio
|
|
-- , Tare = Source.TaraMedia
|
|
-- WHEN NOT MATCHED BY TARGET THEN
|
|
-- INSERT (CodPackag, TotQty, [Weight], Tare) VALUES (CodPackag, TotQta, PesoMedio, TaraMedia)
|
|
-- OUTPUT $action INTO @SummaryOfChanges;
|
|
|
|
-- END
|
|
----ELSE IF @source = 'ItemsRaw' -- carico da anagrafica
|
|
---- BEGIN
|
|
---- MERGE INTO Item2Package AS Target
|
|
---- USING (SELECT CodItem, ItemDescr, CodPlant, UnitWeight, CodItemGroup FROM ItemsRaw)
|
|
---- AS Source
|
|
---- ON Target.CodItem = Source.CodItem
|
|
---- WHEN MATCHED THEN
|
|
---- UPDATE SET ItemDescr = Source.ItemDescr, CodPlant = Source.CodPlant, UnitWeight = Source.UnitWeight, CodItemGroup = Source.CodItemGroup
|
|
---- WHEN NOT MATCHED BY TARGET THEN
|
|
---- INSERT (CodItem, ItemDescr, CodPlant, UnitWeight, CodItemGroup) VALUES (CodItem, ItemDescr, CodPlant, UnitWeight, CodItemGroup)
|
|
---- OUTPUT $action INTO @SummaryOfChanges;
|
|
|
|
---- END
|
|
|
|
---- Query the results of the table variable.
|
|
--SELECT Change, COUNT(*) AS CountPerChange
|
|
--FROM @SummaryOfChanges
|
|
--GROUP BY Change;
|
|
END |