First sorry if my english is bad, it's not my native language.
I have two tables (A and B) with the following columns:
A: PRENUMERO (ID), DATA, ARMAZEM, TIPO
and
B: Autoreg (ID), PRENUMERO, PRODUTO
I want a result like:
*CountA, CountB, CountC, DATE *
CountA is when PRODUTO is equal to 1.
CountB is when PRODUTO is > than 1.
CountC is when PRODUTO has both 1 and > 1 in the same PRENUMERO.
I joined the tables like this:
SELECT DISTINCT b.[Autoreg], a.[PRENUMERO], b.[PRENUMERO], b.[PRODUTO] FROM b.[VendasPOS_Linhas] JOIN a ON b.[PRENUMERO]=a.[PRENUMERO] WHERE a.[DATA] > '2015-06-01 00:00:00.000' AND a.[Armazem] = '111'
And this is the result:
So if I do [PRENUMERO] = '1' (for CountA) the result should be for example 1000 and if I do [PRENUMERO] > '1' (for CountB) the result should be for example 1100 and the CountC should be the rest (667). But I do not know how to do it.
As you can see in the line 11 and 12, they have the same PRENUMERO, but the PRODUTO is 1 and the other is 10900... so this is for CountC. If the PRENUMERO is the same (several exactly PRENUMERO) but the PRODUTO is 1 (CountA (like line 8 and 9)) or >1 (CountB) this count only as 1 (DICTINCT here?)
(IF PRODUTO = 1 THEN CountA; IF PRODUTO >1 THEN CountB)
Aucun commentaire:
Enregistrer un commentaire