Count of two SQL tables and add when different

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 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

What I am trying to do is that in this Count COUNT(DISTINCT t.SI_Ubicacion) SI_Ubicacion I have these 2 locations cc3111, ee3091 that is in the table SI_Inventario_Teorico_QAD t and I need to add the location that you enter in the table SI_Conteo c which has 3 locations cc3111, ee3091, ee2102 as you see has 2 locations that are the same but you must add the one that is different which is ee2102 in total should show me that SI_Ubicacion has 3.

    
asked by Eduard 05.07.2017 в 15:13
source

0 answers