Selecting Active Batch

Question

I’m studying about appointments at Sqlserver and have the following scenario:

I have a lot table that has the following fields:

Table of Lot (Lot)

Id INT  
ProdutoID INT  
Preco SMALLMONEY  
Inicio DATETIME  
Fim DATETIME

Table of Products (Products)

Id INT  
Name VARCHAR  
Tipo INT  

Table of Types (Types)

Id INT  
Name VARCHAR  

NOTE: And a well summarized and example table

I need to perform a query that gives me the current price of the product. Ex.:

Types

Id | Name
01 | Teste  
02 | Testando  

Products

Id | Name    | Tipo
01 | Celular | 01  
02 | Câmera  | 02

Batches

Id | ProdutoId | Preco | Inicio                  | Fim  
1  |         1 |    50 | 2017-08-14 13:10:57.980 | 2017-08-16 13:10:57.980
2  |         1 |   100 | 2017-08-12 13:10:57.980 | NULL

As you can see, Lots has 2 Records the record with NULL is the official price of the product and the first registration is a promotion that starts on the 14th and ends on the 16th. Taking into account that the date of publication of this question is 15/08 the current price would be 50.

How do I select Product Name, Product Type, and Current Price? (Taking into account the date of promotion)

NOTE: Case should work also with these records:

Id | ProdutoId | Preco | Inicio                  | Fim  
1  |         1 |    50 | 2017-08-14 13:10:57.980 | 2017-08-16 13:10:57.980
2  |         1 |   100 | 2017-08-12 13:10:57.980 | NULL
3  |         1 |   150 | 2017-08-17 13:10:57.980 | NULL

In this case, the value of that product will increase on the 17th.

Just so it doesn’t go blank, I’m doing the second consultation:

select p.Nome as 'Produto', t.Nome as 'Tipo', l.Preco, MAX(p.Inicio)
from Lote as l inner join Produtos as p on l.ProdutoId = p.Id
inner join Tipos as t on p.TipoId = t.Id
group by p.Nome, t.Nome, l.preco

But it doesn’t work in the right way obviously, I thank you already!

Answer 1

Here is the suggestion that uses the product table as a starting point.

-- código #1 v3
declare @Hoje datetime;
set @Hoje= cast(current_timestamp as date);

--
with ctePreços as (
SELECT P.Id, P.Name, T.Name as Tipo, 
       (SELECT top (1) Preco 
          from Lot as L1 
          where L1.ProdutoId = P.Id
                and L1.Fim is NULL
                and @Hoje >= L1.Inicio 
          order by L1.Inicio desc) as Oficial,
       (SELECT Preco 
          from Lot as L2 
          where L2.ProdutoId = P.Id
                and @Hoje between L2.Inicio and L2.Fim) as Oferta
  from Products as P
       inner join Types as T on T.Id = P.Tipo
)
SELECT Id, 
       Name as [Nome do produto], 
       Tipo as [Tipo de produto],
       coalesce(Oferta, Oficial) as [Preço atual]
  from ctePreços;

---

Here is another approach, which uses the batch table as a starting point. In this case, only products that have information in the Lot table are listed.

-- código #2
declare @Hoje datetime;
set @Hoje= cast(current_timestamp as date);

--
with cteAtual as (
SELECT L.*,
       Seq= row_number() over (partition by L.ProdutoId order by L.Inicio desc)
  from Lot as L
  where L.Inicio <= @Hoje
        and (L.Fim is NULL or L.Fim >= @Hoje)
)
SELECT A.ProdutoId, 
       P.Name as [Nome do produto], 
       T.Name as [Tipo de produto],
       A.Preco as [Preço atual]
  from cteAtual as A
       inner join Products as P on P.Id = A.ProdutoId
       inner join Types as T on T.Id = P.Tipo
  where A.Seq = 1;         

Answer 2

I believe the following query solves your problem: I didn’t put the guys in because it’s irrelevant to the problem, just be added, and only a matter of names, I believe that lot is not the correct name for this table. A lot must have an identification, date of manufacture and validity. This table seems to me more like a price list.

Select distinct
    p.nome,
    coalesce(lp.preco, l.preco,0) as preco
from produtos p 
left outer join lote lp on lp.produtoid = p.id 
                        and lp.id = (select 
                                        max(x.id) 
                                     from lote x 
                                     where x.produtoid = lp.produtoid 
                                     and x.inicio <= getdate() 
                                     and x.fim >= getdate())
left outer join lote l on l.produtoid = p.id 
                       and l.inicio <= getdate() 
                       and l.fim is null

Answer 3

Use the expression WITH to prioritize prices together with the function ROW_NUMBER:

WITH Prices (Id,
             ProdutoID,
             Preco,
             Inicio,
             Fim,
             Prioridade)
AS (
  SELECT lot.Id,
         lot.ProdutoID,
         lot.Preco,
         lot.Inicio,
         lot.Fim,
         ROW_NUMBER() OVER(PARTITION BY lot.ProdutoID ORDER BY lot.Inicio) AS Prioridade
    FROM Lot lot
   WHERE GETDATE() BETWEEN lot.Inicio AND ISNULL(lot.Fim, GETDATE())
)
SELECT prod.Name  AS 'Nome do Produto',
       type.Name  AS 'Tipo do produto',
       pric.Preco AS 'Preço atual'
  FROM Products prod
       INNER JOIN Types type ON type.Id = prod.Tipo
       INNER JOIN Prices pric ON pric.ProdutoID = prod.Id
 WHERE pric.Prioridade = 1;