none
Suma con un case y subquery RRS feed

  • Pregunta

  • Hola buenas tardes tengo un case y un subquery quiero meter mi case en el subquery para sumar mis valores pero no se como hacerlo, el parte del subquery de COALESCE(SUM(S0.Quantity),0) me esta sumando por separado, necesito meter mi case para que no me sume solo sal sino 'EST' Y 'FOR'
    WITH tempTable AS
    (
    SELECT 
    T3.[ItemCode]
    ,T3.[WhsCode]
    ,T3.[OnHand]
    ,T3.[MinStock]
    ,T3.[OnOrder]
    ,T3.[IsCommited]
    ,T3.[MaxStock]
    ,CASE 
       WHEN T1.[WhsCode] IN ('EST','FOR') THEN 'SAL'
       ELSE T1.[WhsCode] END  'Almacen'
    ,((SELECT COALESCE(SUM(S0.Quantity),0) 
    	FROM INV1 S0 
    	WHERE S0.ItemCode = T3.ItemCode 
    	AND T3.WhsCode = S0.WhsCode 
    	AND DATEDIFF(DD, S0.[DocDate]  , GETDATE()) <= 180)/6) AS 'Ventas' 
    
    FROM OINV T0 
    
    INNER JOIN INV1  T1 ON T0.[DocEntry] = T1.[DocEntry]
    INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] 
    INNER JOIN OITW T3 ON T2.[ItemCode] = T3.[ItemCode]
     
    WHERE T0.[CANCELED]  <>'Y'  AND T3.MaxStock>0 AND  T1.[WhsCode]  =  T3.WhsCode 
    
    GROUP BY T3.[ItemCode],T3.[WhsCode],T3.[MinStock],T3.[OnHand],T3.[OnOrder],T3.[IsCommited],T3.[MaxStock],T1.[WhsCode]
    )
    SELECT ItemCode,WhsCode,Almacen, OnHand,MinStock,OnOrder,IsCommited,MaxStock,Ventas FROM tempTable
    GROUP BY ItemCode,WhsCode,OnHand,MinStock,OnOrder,IsCommited,MaxStock,Almacen,Ventas

    martes, 18 de junio de 2019 21:30

Respuestas

  • Hola Eliza_09:

    Los dos están escritos al vuelo, porque yo no tengo la sentencia de creación de tus tablas

    Para el primero, así a bote pronto no veo el error de sintaxis.

    Para el segundo, 

    WITH tempTable
         AS (SELECT T3.[ItemCode], 
                    T3.[WhsCode], 
                    T3.[OnHand], 
                    T3.[MinStock], 
                    T3.[OnOrder], 
                    T3.[IsCommited], 
                    T3.[MaxStock],
                    CASE
                        WHEN T1.[WhsCode] IN('EST', 'FOR')
                        THEN 'SAL'
                        ELSE T1.[WhsCode]
                    END 'Almacen'
             FROM OINV T0
                  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]
                  INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]
                  INNER JOIN OITW T3 ON T2.[ItemCode] = T3.[ItemCode]
             WHERE T0.[CANCELED] <> 'Y'
                   AND T3.MaxStock > 0
                   AND T1.[WhsCode] = T3.WhsCode), Ventas As (
    
    			SELECT (SUM(S0.Quantity)/6) as quantity, S0.ItemCode, S0.WhsCode
    				 FROM INV1 S0
    				 WHERE 
    					  DATEDIFF(DD, S0.[DocDate], GETDATE()) <= 180
    				GROUP BY S0.ItemCode, S0.WhsCode
    			  ) 
         SELECT tempTable.ItemCode, 
                tempTable.WhsCode, 
                tempTable.Almacen, 
                tempTable.OnHand, 
                tempTable.MinStock, 
                tempTable.OnOrder, 
                tempTable.IsCommited, 
                tempTable.MaxStock, 
    		    S0.quantity
         FROM tempTable
    	LEFT JOIN Ventas S0 ON S0.ItemCode = tempTable.ItemCode
    					  AND tempTable.WhsCode = S0.WhsCode

    • Marcado como respuesta Louisa_E jueves, 20 de junio de 2019 15:15
    miércoles, 19 de junio de 2019 15:25

Todas las respuestas

  • Hola Eliza_09:

    Tienes un poco de lio, demasiados group by

    WITH tempTable
         AS (SELECT T3.[ItemCode], 
                    T3.[WhsCode], 
                    T3.[OnHand], 
                    T3.[MinStock], 
                    T3.[OnOrder], 
                    T3.[IsCommited], 
                    T3.[MaxStock],
                    CASE
                        WHEN T1.[WhsCode] IN('EST', 'FOR')
                        THEN 'SAL'
                        ELSE T1.[WhsCode]
                    END 'Almacen'
             FROM OINV T0
                  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]
                  INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]
                  INNER JOIN OITW T3 ON T2.[ItemCode] = T3.[ItemCode]
             WHERE T0.[CANCELED] <> 'Y'
                   AND T3.MaxStock > 0
                   AND T1.[WhsCode] = T3.WhsCode)
         SELECT ItemCode, 
                WhsCode, 
                Almacen, 
                OnHand, 
                MinStock, 
                OnOrder, 
                IsCommited, 
                MaxStock, 
                (
    			  (
    				 SELECT COALESCE(SUM(S0.Quantity), 0)
    				 FROM INV1 S0
    				 WHERE S0.ItemCode = tempTable.ItemCode
    					  AND tempTable.WhsCode = S0.WhsCode
    					  AND DATEDIFF(DD, S0.[DocDate], GETDATE()) <= 180
    			  ) / 6) AS 'Ventas'
         FROM tempTable;
    

    Esta sería con la subconsulta, en la salida del primer conjunto, pero sin group by.

    Otra opción, sin la subconsulta.

    WITH tempTable
         AS (SELECT T3.[ItemCode], 
                    T3.[WhsCode], 
                    T3.[OnHand], 
                    T3.[MinStock], 
                    T3.[OnOrder], 
                    T3.[IsCommited], 
                    T3.[MaxStock],
                    CASE
                        WHEN T1.[WhsCode] IN('EST', 'FOR')
                        THEN 'SAL'
                        ELSE T1.[WhsCode]
                    END 'Almacen'
             FROM OINV T0
                  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]
                  INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]
                  INNER JOIN OITW T3 ON T2.[ItemCode] = T3.[ItemCode]
             WHERE T0.[CANCELED] <> 'Y'
                   AND T3.MaxStock > 0
                   AND T1.[WhsCode] = T3.WhsCode), Ventas As (
    
    			SELECT (SUM(S0.Quantity)/6) as quantity, S0.ItemCode, S0.WhsCode
    				 FROM INV1 S0
    				 WHERE 
    					  DATEDIFF(DD, S0.[DocDate], GETDATE()) <= 180
    				GROUP BY S0.ItemCode, S0.WhsCode
    			  ) 
         SELECT ItemCode, 
                WhsCode, 
                Almacen, 
                OnHand, 
                MinStock, 
                OnOrder, 
                IsCommited, 
                MaxStock, 
    		  S0.quantity
         FROM tempTable
    	LEFT JOIN Ventas S0 ON S0.ItemCode = tempTable.ItemCode
    					  AND tempTable.WhsCode = S0.WhsCode
    Espero te ayude

    miércoles, 19 de junio de 2019 4:37
  • Hola, en el primer query me sale un error incorrect syntax near the keyword 'FOR'

    Y en el segundo que son ambiguos ItemCode y WhsCode

    miércoles, 19 de junio de 2019 14:25
  • Hola Eliza_09:

    Los dos están escritos al vuelo, porque yo no tengo la sentencia de creación de tus tablas

    Para el primero, así a bote pronto no veo el error de sintaxis.

    Para el segundo, 

    WITH tempTable
         AS (SELECT T3.[ItemCode], 
                    T3.[WhsCode], 
                    T3.[OnHand], 
                    T3.[MinStock], 
                    T3.[OnOrder], 
                    T3.[IsCommited], 
                    T3.[MaxStock],
                    CASE
                        WHEN T1.[WhsCode] IN('EST', 'FOR')
                        THEN 'SAL'
                        ELSE T1.[WhsCode]
                    END 'Almacen'
             FROM OINV T0
                  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]
                  INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]
                  INNER JOIN OITW T3 ON T2.[ItemCode] = T3.[ItemCode]
             WHERE T0.[CANCELED] <> 'Y'
                   AND T3.MaxStock > 0
                   AND T1.[WhsCode] = T3.WhsCode), Ventas As (
    
    			SELECT (SUM(S0.Quantity)/6) as quantity, S0.ItemCode, S0.WhsCode
    				 FROM INV1 S0
    				 WHERE 
    					  DATEDIFF(DD, S0.[DocDate], GETDATE()) <= 180
    				GROUP BY S0.ItemCode, S0.WhsCode
    			  ) 
         SELECT tempTable.ItemCode, 
                tempTable.WhsCode, 
                tempTable.Almacen, 
                tempTable.OnHand, 
                tempTable.MinStock, 
                tempTable.OnOrder, 
                tempTable.IsCommited, 
                tempTable.MaxStock, 
    		    S0.quantity
         FROM tempTable
    	LEFT JOIN Ventas S0 ON S0.ItemCode = tempTable.ItemCode
    					  AND tempTable.WhsCode = S0.WhsCode

    • Marcado como respuesta Louisa_E jueves, 20 de junio de 2019 15:15
    miércoles, 19 de junio de 2019 15:25