Samuele E. Locatelli
94 lines
3.7 KiB
Transact-SQL
94 lines
3.7 KiB
Transact-SQL
|
|
|
|
|
|
-- =============================================
|
|
-- Author: S.E. Locatelli
|
|
-- Create date: 2013.11.27
|
|
-- Description: Procedura per import dati in Package2ItemClient da dati ProductionLogRaw
|
|
-- =============================================
|
|
CREATE PROCEDURE [ext].[stp_mergePackage2ItemClient]
|
|
(
|
|
@source NVARCHAR(20) = 'ProductionLog', -- nome della tabella sorgente tra ProductionLogRaw e ItemsRaw
|
|
@dateFrom DATETIME, -- data da cui iniziare a caricare
|
|
@dateTo DATETIME -- data fino a cui caricare
|
|
)
|
|
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 = 'ProductionLog' -- carico da tracciato produzione
|
|
BEGIN
|
|
MERGE INTO Package2ItemClient AS Target
|
|
USING (
|
|
--SELECT dd.CodItem, plr.CodClient, 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 dd.CodItem, plr.CodClient, plr.CodPackag
|
|
SELECT
|
|
dd.CodItem
|
|
, pl.CodClient
|
|
, pl.CodPackag
|
|
, SUM(QtyOUT) AS TotQta
|
|
-- , COUNT(*) AS NumEv
|
|
, SUM(QtyOUT) / COUNT(*) AS PesoMedio
|
|
, SUM(QtyEXT) / COUNT(*) AS TaraMedia
|
|
-- dati produttività imballo
|
|
, ROUND(SUM(QtyOUT* QuotaMan) / CASE WHEN SUM(QtyOUT) = 0 THEN 1 ELSE SUM(QtyOUT) END,0) as NumImb
|
|
, SUM(Duration) AS Durata
|
|
FROM dbo.ProductionLog pl
|
|
INNER JOIN dbo.DiesDet dd ON pl.CodDies=dd.CodDies
|
|
|
|
WHERE pl.ProcessNum = '04'
|
|
AND pl.okPr = 1 AND pl.okQM = 1
|
|
AND ISNULL(pl.CodPackag, '') <> ''
|
|
AND pl.EventStart BETWEEN @dateFrom AND @dateTo
|
|
GROUP BY dd.CodItem, pl.CodClient, pl.CodPackag
|
|
|
|
)
|
|
AS Source
|
|
ON Target.CodItem = Source.CodItem AND Target.CodClient = Source.CodClient AND Target.CodPackag = Source.CodPackag
|
|
WHEN MATCHED THEN
|
|
UPDATE SET TotQta = Source.TotQta
|
|
, [Weight] = Source.PesoMedio
|
|
, Tare = Source.TaraMedia
|
|
, NumImb = Source.NumImb
|
|
, Durata = Source.Durata
|
|
WHEN NOT MATCHED BY TARGET THEN
|
|
INSERT (CodItem, CodClient, CodPackag, TotQta, [Weight], Tare, NumImb, Durata)
|
|
VALUES (CodItem, CodClient, CodPackag, TotQta, PesoMedio, TaraMedia, NumImb, Durata)
|
|
WHEN NOT MATCHED BY SOURCE THEN
|
|
DELETE
|
|
OUTPUT $action INTO @SummaryOfChanges;
|
|
|
|
END
|
|
--ELSE IF @source = 'ItemsRaw' -- carico da anagrafica
|
|
-- BEGIN
|
|
-- MERGE INTO Item2PackageClient 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
|
|
|
|
|
|
|