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.