mardi 6 septembre 2016

SQL Join on conditions

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): previous transaction

However if I run the query in full I get the following: query result

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