I'm trying to get a list of products sold in the last month, with a certain feature, which in this case would be your classification code. The question is that I have 4 related tables, Products, ClassificationProducts, DetailVentas, EncavesadoVentas.
Now, this I have done only with three tables that would be like this:
SELECT DISTINCT( p.CodPro) AS 'Code',p.NomPro AS 'Product Name', CONVERT(DECIMAL(10,0),p.PreVta) AS 'Price' FROM Productos p INNER JOIN DetVtas d ON p.CodPro = d.CodPro WHERE p.CodCla IN (01,02,05) AND p.Desactivado = 0 ORDER BY p.CodPro ASC;
As you can see, this gives me a list of the products sold, which I can find in details of sales with certain classification that I want.
Now, I want to do the same but with a certain length of date, obtaining a list of the month that I want, and in this case to relate the table EncVtas (Head Sales) where the date of the invoice is recorded with the table DetVtas ( Sales detail) which is the one that records the details of the invoice.
This is the example diagram, I do not enter all the fields because the query would be very big.
Someone if you can guide me please, where the thing is going.