I want to make a query of 1 table that has the records:
Predio | Local | Produto | FIFO
P01 | WH01 | Maçã | 21/02/17
P01 | WH01 | Maçã | 22/02/17
P01 | WH01 | Maçã | 23/02/17
P01 | WH01 | Maçã | 28/02/17
P01 | WH01 | Maçã | 01/03/17
P01 | WH01 | Pera | 10/10/17
P01 | WH01 | Pera | 11/10/17
P01 | WH01 | Pera | 12/10/17
P01 | WH01 | Pera | 14/10/17
P01 | WH01 | Pera | 15/10/17
P01 | WH01 | Pera | 28/10/17
P01 | WH01 | Pera | 29/10/17
P01 | WH01 | Manga | 05/11/17
P01 | WH01 | Manga | 08/11/17
P01 | WH01 | Abacaxi| 01/12/17
P01 | WH01 | Abacaxi| 10/12/17
Where by building and location, the maximum amount of products is 3, what I wanted was to discard the FIFO column to bring no more than 1 result of the products.
I tried Query:
SELECT DISTINCT
Predio,
Local,
Produto,
count(Produto) Qnt_de_PNs
FROM
Controle_Prod_P_Local_E_Predio
WHERE
Local BETWEEN ('LOCA"')
AND ('LOCA_FINAL')
AND Predio BETWEEN ('Predio')
AND ('Predio_FINAL')
AND Qnt_de_PNs > 3
GROUP BY Predio, Local, Produto
Result:
Predio | Local | Produto | Qnt_de_PNs
P01 | WH01 | Maçã | 5
P01 | WH01 | Pera | 7
But we know that there are 4 products (Apple, Pear, Mango and Pineapple), what I need and that the Qnt_de_PNs field brings me the result of 4. This is because the count result is based on FIFO records.
Can you help me?