I'm trying to write a query which will do some calculations on a transaction if that specific transaction meets a certain requirement (in this case that it's total quantity != 0).
IF EXISTS(
SELECT DISTINCT
T0.[ItemCode],
T0.[ItemName],
T0.[DocDate],
CAST(CAST(T0.[CreateTime] / 100 AS varchar) + ':' + CAST(T0.[CreateTime] % 100 AS varchar) AS time) AS [CreateTime],
T0.[DocType],
T0.[DocEntry],
T0.[DocNum],
T0.[LocCode],
T1.[MdAbsEntry],
T4.[AbsEntry],
T4.[DistNumber] AS [BatchNumber],
T1.[Quantity],
T5.[CalcPrice] AS [Price],
T3.[U_ORC_BE_Variance]
FROM OITL T0
INNER JOIN ITL1 T1 ON T1.[LogEntry] = T0.[LogEntry]
INNER JOIN OITM T2 ON T2.[ItemCode] = T0.[ItemCode]
INNER JOIN IGE1 T3 ON T3.[DocEntry] = T0.[DocEntry]
INNER JOIN OBTN T4 ON T1.[MdAbsEntry] = T4.[AbsEntry]
INNER JOIN OINM T5 ON T0.[ItemCode] = T5.[ItemCode]
AND T0.[LocCode] = T5.[Warehouse]
AND T0.[AppDocNum] = T5.[BASE_REF]
WHERE T0.[ItemCode] LIKE 'BB%' -- BBT items only
AND T3.[U_ORC_BE_Variance] = 'M3'-- Code issued upon BBT empty
AND CAST(CAST(T0.[CreateTime] / 100 AS varchar) + ':' + CAST(T0.[CreateTime] % 100 AS varchar) AS time)
BETWEEN CAST(DATEADD(MINUTE,-11,GETDATE())AS TIME) AND CONVERT(varchar(10), GETDATE(), 108) -- Last 10 minutes
AND T0.[DocDate] = CAST(LEFT(GETDATE(), 11) AS DATETIME) -- Today
)
BEGIN
SELECT DISTINCT
'BBT ALERT - ' + convert(varchar(10), GETDATE(), 108) + ' - Check for errors',
'Difference HL:',
SUM(T1.[Quantity]),
'Percentage Difference:',
((((SUM(ABS(T1.[Quantity])) - MAX(T1.[Quantity])) / MAX(T1.[Quantity])) * 100) - 100)
FROM OITL T0
INNER JOIN ITL1 T1 ON T1.[LogEntry] = T0.[LogEntry]
INNER JOIN OITM T2 ON T2.[ItemCode] = T0.[ItemCode]
INNER JOIN IGE1 T3 ON T3.[DocEntry] = T0.[DocEntry]
INNER JOIN OBTN T4 ON T1.[MdAbsEntry] = T4.[AbsEntry]
INNER JOIN OINM T5 ON T0.[ItemCode] = T5.[ItemCode]
AND T0.[LocCode] = T5.[Warehouse]
AND T0.[AppDocNum] = T5.[BASE_REF]
WHERE
(SELECT SUM([Quantity]) FROM ITL1 WHERE
(SELECT DISTINCT
T4.[AbsEntry]
FROM OITL T0
INNER JOIN ITL1 T1 ON T1.[LogEntry] = T0.[LogEntry]
INNER JOIN OITM T2 ON T2.[ItemCode] = T0.[ItemCode]
INNER JOIN IGE1 T3 ON T3.[DocEntry] = T0.[DocEntry]
INNER JOIN OBTN T4 ON T1.[MdAbsEntry] = T4.[AbsEntry]
INNER JOIN OINM T5 ON T0.[ItemCode] = T5.[ItemCode]
AND T0.[LocCode] = T5.[Warehouse]
AND T0.[AppDocNum] = T5.[BASE_REF]
WHERE T0.[ItemCode] LIKE 'BB%' -- BBT items only
AND T3.[U_ORC_BE_Variance] = 'M3'-- Code issued upon BBT empty
AND CAST(CAST(T0.[CreateTime] / 100 AS varchar) + ':' + CAST(T0.[CreateTime] % 100 AS varchar) AS time)
BETWEEN CAST(DATEADD(MINUTE,-11,GETDATE())AS TIME) AND CONVERT(varchar(10), GETDATE(), 108) -- Last 10 minutes
AND T0.[DocDate] = CAST(LEFT(GETDATE(), 11) AS DATETIME) -- Today
) = [MdAbsEntry]
) <> 0
SELECT DISTINCT
T1.[MdAbsEntry],
T0.[DocNum],
T1.[Quantity]
FROM OITL T0
INNER JOIN ITL1 T1 ON T1.[LogEntry] = T0.[LogEntry]
INNER JOIN OITM T2 ON T2.[ItemCode] = T0.[ItemCode]
INNER JOIN IGE1 T3 ON T3.[DocEntry] = T0.[DocEntry]
INNER JOIN OBTN T4 ON T1.[MdAbsEntry] = T4.[AbsEntry]
INNER JOIN OINM T5 ON T0.[ItemCode] = T5.[ItemCode] AND T0.[LocCode] = T5.[Warehouse]
AND T0.[AppDocNum] = T5.[BASE_REF]
WHERE
(SELECT SUM([Quantity]) FROM ITL1 WHERE
(SELECT DISTINCT
T4.[AbsEntry]
FROM OITL T0
INNER JOIN ITL1 T1 ON T1.[LogEntry] = T0.[LogEntry]
INNER JOIN OITM T2 ON T2.[ItemCode] = T0.[ItemCode]
INNER JOIN IGE1 T3 ON T3.[DocEntry] = T0.[DocEntry]
INNER JOIN OBTN T4 ON T1.[MdAbsEntry] = T4.[AbsEntry]
INNER JOIN OINM T5 ON T0.[ItemCode] = T5.[ItemCode]
AND T0.[LocCode] = T5.[Warehouse]
AND T0.[AppDocNum] = T5.[BASE_REF]
WHERE T0.[ItemCode] LIKE 'BB%' -- BBT items only
AND T3.[U_ORC_BE_Variance] = 'M3'-- Code issued upon BBT empty
AND CAST(CAST(T0.[CreateTime] / 100 AS varchar) + ':' + CAST(T0.[CreateTime] % 100 AS varchar) AS time)
BETWEEN CAST(DATEADD(MINUTE,-11,GETDATE())AS TIME) AND CONVERT(varchar(10), GETDATE(), 108) -- Last 10 minutes
AND T0.[DocDate] = CAST(LEFT(GETDATE(), 11) AS DATETIME) -- Today
) = [MdAbsEntry]
) <> 0
END
If I run the first part in isolation I get the following (which is correct):
However if I run the query in full I get the following:
I would expect it to only list results with one value in MdAbsEntry (in the case 5618).
It looks to me that it's not matching on the MdAbsEntry value like I want it to, and so pulling results for all data instead of just for this one specific transaction.
Can anyone give me any advice on why it might be doing this? I feel like I'm getting nowhere.
Many thanks.
Aucun commentaire:
Enregistrer un commentaire