Sum without distinct and do not add data from another table

0

I have this query

SELECT 
t.SI_Articulo, 
m.SI_Descripcion,  
t.SI_UM,
(CASE WHEN c.SI_OV IS NOT NULL THEN '*' ELSE NULL END) SI_OV, 
COUNT(DISTINCT ISNULL(c.SI_Ubicacion,t.SI_Ubicacion)) SI_Ubicacion, 
SUM(DISTINCT t.SI_Existencia) SI_Existencia, 
(CASE WHEN SUM(DISTINCT c.SI_Cantidad) IS NULL THEN 0 ELSE SUM(DISTINCT c.SI_Cantidad)END )SI_Cantidad,
(CASE WHEN SUM(DISTINCT c.SI_Cantidad) - SUM(DISTINCT t.SI_Existencia) IS NULL THEN 0 - SUM(DISTINCT t.SI_Existencia) 
ELSE SUM(DISTINCT c.SI_Cantidad) - SUM(DISTINCT t.SI_Existencia) END ) SI_Dif,
(CASE WHEN SUM(DISTINCT c.SI_Cantidad) - SUM(DISTINCT t.SI_Existencia)* m.SI_Costo_Promedio IS NULL 
THEN 0 - SUM(DISTINCT t.SI_Existencia) * m.SI_Costo_Promedio ELSE SUM(DISTINCT c.SI_Cantidad) - SUM(DISTINCT t.SI_Existencia)* m.SI_Costo_Promedio END) SI_Dif_Dinero 
FROM SI_Inventario_Teorico_QAD t 
INNER JOIN SI_Maestro_Ref_QAD m ON m.SI_Num_Articulo = t.SI_Articulo 
LEFT JOIN SI_Conteo c ON c.SI_Num_Articulo = t.SI_Articulo AND c.SI_Num_Inventario = 1
LEFT JOIN SI_Consecutivo cs ON cs.SI_Num_Inventario = c.SI_Num_Inventario
GROUP BY t.SI_Articulo, 
         m.SI_Descripcion, 
         t.SI_UM, 
         (CASE WHEN c.SI_OV IS NOT NULL THEN '*' ELSE NULL END),
         m.SI_Costo_Promedio

In which I am having the error that adding SUM(DISTINCT t.SI_Existencia) SI_Existencia will get into the tables of SI_Maestro_Ref_QAD and SI_Conteo c and it brings me a very large sum and if I put the DISTINCT it will only tell me that adds one since the article is repeated in the table.

Sorry Lamak

    
asked by Eduard 05.07.2017 в 20:30
source

0 answers