vendredi 26 août 2016

SQL Join nested IF

I'm trying to write a query which will check for a certain condition on a transaction, and then if that condition is met it will do a calculation on related transactions to determine if the quantity is 0 or not.

The idea is that a number of transactions are created in each batch which should all equal out to 0 at the end when emptied. The goal is to check to see if a batch has been marked as finished in the previous 10 minutes, and then do a calculation to verify that the quantity zeroes out on that specific batch.

I have written the below to try and accomplish this:

 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],
    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,-240,GETDATE())AS TIME) AND CONVERT(varchar(10), GETDATE(), 108)
--Last 10 minutes
    AND T0.[DocDate] = CAST(LEFT(GETDATE(), 11) AS DATETIME)
--Today
  )


BEGIN

IF(
    (SELECT SUM([Quantity]) FROM ITL1 ) <> '0'
  )

BEGIN
    SELECT 'ALERT'
END

END

What I need to do now is make sure that the second IF (which can call the ALERT) only checks against the relevant [BatchNumber] which would tie the transactions together.

I was thinking that I could try joining the first IF onto the 2nd but I'm not sure that that would give the result that I'm after? It also seems like it might start getting very messy if I do that.

Many thanks.

Aucun commentaire:

Enregistrer un commentaire