I'm using SQL Server 2005. I got a lot of help on here a while ago getting this set up and it works perfectly except when someone enters one of the codes after one has already been used. They aren't supposed to, but it happens. I need to prevent this because it is doubling (or tripling) the costs on the job. I started out trying to use a subquery that counted the number of times the codes were used but got hung up. I thought a CTE would work but I'm not sure where to place it in the trigger.
This is the current trigger.
This small CTE test isolates what I need but I don't know if it will work in here:
This is the current trigger.
Code:
ALTER TRIGGER [dbo].[LM_DigiCost]
ON [dbo].[JobStatus1]
AFTER INSERT
AS
BEGIN
INSERT
INTO dbo.JobMaterial(
JobN,
MccN,
MatlQuantity,
Employee,
Shift,
TransDate,
TransTime,
Unit,
AICCost,
UpdateDate,
LastChanged,
ChangedBy)
SELECT Inserted.JobN,
CT_PrintItems.RunMCCN,
(CASE WHEN CT_PrintItems.PgRec > 0 THEN
CASE WHEN (((CASE WHEN CT_PrintItems.Format = 'Signature' THEN
CT_PrintItems.Pages/2
ELSE
CT_PrintItems.Pages
END) + CT_PrintItems.Setup) % CT_PrintItems.Up > 0) THEN
(FLOOR(((CASE WHEN CT_PrintItems.Format = 'Signature' THEN
CT_PrintItems.Pages/2
ELSE
CT_PrintItems.Pages
END) + CT_PrintItems.Setup)/CT_PrintItems.Up) + 1) * (CASE WHEN CT_PrintItems.PgRec > 0 THEN
CT_PrintItems.Qty
WHEN (CT_PrintItems.PgRec = 0 AND CT_PrintItems.Up > 0) THEN
CASE WHEN ((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) % CT_PrintItems.Up) > 0 then
FLOOR((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) / CT_PrintItems.Up) + 1
ELSE
FLOOR((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) / CT_PrintItems.Up)
END
END) * CT_PrintItems.PgRec
ELSE
(((CASE WHEN CT_PrintItems.Format = 'Signature' THEN
CT_PrintItems.Pages/2
ELSE
CT_PrintItems.Pages
END) + CT_PrintItems.Setup)/CT_PrintItems.Up) * (CASE WHEN CT_PrintItems.PgRec > 0 THEN
CT_PrintItems.Qty
WHEN (CT_PrintItems.PgRec = 0 AND CT_PrintItems.Up > 0) THEN
CASE WHEN ((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) % CT_PrintItems.Up) > 0 then
FLOOR((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) / CT_PrintItems.Up) + 1
ELSE
FLOOR((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) / CT_PrintItems.Up)
END
END) * CT_PrintItems.PgRec
END
ELSE
(CASE WHEN CT_PrintItems.PgRec > 0 THEN
CT_PrintItems.Qty
WHEN (CT_PrintItems.PgRec = 0 AND CT_PrintItems.Up > 0) THEN
CASE WHEN ((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) % CT_PrintItems.Up) > 0 then
FLOOR((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) / CT_PrintItems.Up) + 1
ELSE
FLOOR((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) / CT_PrintItems.Up)
END
END) * (CASE WHEN CT_PrintItems.Format = 'Signature' THEN
CT_PrintItems.Pages/2
ELSE
CT_PrintItems.Pages
END)
END),
999,
1,
Inserted.UpdateDate,
Cast(Replace(Convert(varchar(5),GetDate(),108),':','') as int),
MatlCostCntr.AICRate,
MatlCostCntr.AICRate * (CASE WHEN CT_PrintItems.PgRec > 0 THEN
CASE WHEN (((CASE WHEN CT_PrintItems.Format = 'Signature' THEN
CT_PrintItems.Pages/2
ELSE
CT_PrintItems.Pages
END) + CT_PrintItems.Setup) % CT_PrintItems.Up > 0) THEN
(FLOOR(((CASE WHEN CT_PrintItems.Format = 'Signature' THEN
CT_PrintItems.Pages/2
ELSE
CT_PrintItems.Pages
END) + CT_PrintItems.Setup)/CT_PrintItems.Up) + 1) * (CASE WHEN CT_PrintItems.PgRec > 0 THEN
CT_PrintItems.Qty
WHEN (CT_PrintItems.PgRec = 0 AND CT_PrintItems.Up > 0) THEN
CASE WHEN ((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) % CT_PrintItems.Up) > 0 then
FLOOR((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) / CT_PrintItems.Up) + 1
ELSE
FLOOR((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) / CT_PrintItems.Up)
END
END) * CT_PrintItems.PgRec
ELSE
(((CASE WHEN CT_PrintItems.Format = 'Signature' THEN
CT_PrintItems.Pages/2
ELSE
CT_PrintItems.Pages
END) + CT_PrintItems.Setup)/CT_PrintItems.Up) * (CASE WHEN CT_PrintItems.PgRec > 0 THEN
CT_PrintItems.Qty
WHEN (CT_PrintItems.PgRec = 0 AND CT_PrintItems.Up > 0) THEN
CASE WHEN ((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) % CT_PrintItems.Up) > 0 then
FLOOR((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) / CT_PrintItems.Up) + 1
ELSE
FLOOR((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) / CT_PrintItems.Up)
END
END) * CT_PrintItems.PgRec
END
ELSE
(CASE WHEN CT_PrintItems.PgRec > 0 THEN
CT_PrintItems.Qty
WHEN (CT_PrintItems.PgRec = 0 AND CT_PrintItems.Up > 0) THEN
CASE WHEN ((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) % CT_PrintItems.Up) > 0 then
FLOOR((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) / CT_PrintItems.Up) + 1
ELSE
FLOOR((((CT_PrintItems.Qty * OpenJob.OversAllowed) / 100) + CT_PrintItems.Qty + CT_PrintItems.Setup) / CT_PrintItems.Up)
END
END) * (CASE WHEN CT_PrintItems.Format = 'Signature' THEN
CT_PrintItems.Pages/2
ELSE
CT_PrintItems.Pages
END)
END),
Inserted.UpdateDate,
GetDate(),
'digicost'
FROM Inserted
INNER JOIN OpenJob
ON OpenJob.JobN=Inserted.JobN
INNER JOIN CT_PrintItems
ON CT_PrintItems.JobN=Inserted.JobN
INNER JOIN MatlCostCntr
ON MatlCostCntr.MccN=CT_PrintItems.RunMCCN
WHERE Inserted.StatusCode In (60599,96904)
And CT_PrintItems.RunMCCN > 0
ENDThis small CTE test isolates what I need but I don't know if it will work in here:
Code:
WITH SCCount_CTE
AS
(
SELECT JobN
FROM (SELECT JobN,COUNT(StatusCode) C
FROM JobStatus1
WHERE StatusCode in (60599,96904)
GROUP BY JobN,StatusCode) T
WHERE C <= 1
)