Fórum Select - Dúvidas com Ultimo registro #465061

with ultimo as (
select max(HistFilaConj.Idhist) from HistFilaConj
)
SELECT DISTINCT (HistFilaConj.Idhist )
HistFilaConj.Conjugacao,
CAST(LEFT(Ofs.OP,(CHARINDEX('-',Ofs.OP,1)-1))AS INTEGER) AS NumeroPedido,
CAST(SUBSTRING(Ofs.OP,7,2)AS INTEGER) AS ItemOP,
CONVERT(varchar(10), Inicio, 103) DataInicio,
CAST(CONVERT(float,Inicio)AS INTEGER) AS HoraInicio,
CONVERT(varchar(10),DataExpedicao, 103) AS DataExpedicao,
CAST(CONVERT(float,DataExpedicao)AS INTEGER) AS HoraExpedicao,
OFs.Op,
HistFilaConj.Boletim,
HistFilaConj.Lote,


FROM Trimbox.dbo.HistFilaConj
INNER JOIN OFS ON Ofs.OP = HistFilaConj.OF1
WHERE
OFS.OP LIKE '%[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]/%'
AND OFS.OP LIKE '%[0-9][0-9][0-9][0-9]-[0-9]%'
AND OFS.OP LIKE '%[0-9][0-9]-[0-9]%'
AND OFS.OP LIKE '%[0-9][0-9][0-9]-[0-9]%'
AND OFS.OP IS NOT NULL
AND HistFilaConj.Inicio IS NOT NULL
AND DataExpedicao IS NOT NULL
AND HistFilaConj.Inicio IS NOT NULL
AND DataExpedicao IS NOT NULL
AND HistFilaConj.Boletim IS NOT NULL
AND HistFilaConj.Lote IS NOT NULL
AND HistFilaConj.Conjugacao IS NOT NULL
and HistFilaConj.Idhist = select(id from ultimo)
GROUP BY
HistFilaConj.IDHist,
HistFilaConj.Inicio,
OFs.OP,
OFs.DataExpedicao,
HistFilaConj.Lote,
HistFilaConj.Boletim

ORDER BY HistFilaConj.IdHist DESC

SELECT 
   CASE 
      WHEN I.CODLOJ = '001' THEN '001 - LAPA'
      WHEN I.CODLOJ = '002' THEN '002 - FREGUESIA'
      WHEN I.CODLOJ = '003' THEN '003 - BUTANTA'
      WHEN I.CODLOJ = '004' THEN '004 - JUNDIAI'
      WHEN I.CODLOJ = '005' THEN '005 - TATUAPE'
   ELSE 'SEM LOJA'
   END   AS LOJA,
   CASE WHEN S.CODDEP = '' OR S.CODDEP IS NULL THEN '' 
        ELSE S.CODDEP+' - '+DP.NOME 
   END AS DEPTO,
   I.CODSTK   AS CODIGO,
   S.CODFAB   AS NUMFAB,
   UPPER(SUBSTRING(S.MARCA,1,20)) AS FAB,
   S.ESTOQUE  AS ESTOQUE,
   S.ESTMIN   AS ESTMIN,
   S.ESTMAX   AS ESTMAX,
   S.CONSUMO  AS MDVDA,
   (S.ESTMAX - S.ESTOQUE) AS SUGESTMAX,
   S.PRCCUE   AS CUSTO,
   ISNULL(
          (SELECT 
              SUM(IC2.QTDADE)
           FROM  AS IC2
           LEFT JOIN  AS RC2 ON (RC2.SID = IC2.SID)
           WHERE RC2.TIPOREQ = 'PC'
              AND (IC2.CODSTK = I.CODSTK)
           GROUP BY IC2.CODSTK),0) AS PEDPEND,
   ISNULL(
          (SELECT 
              SUM(IC3.QTDADE)
           FROM ITEMCOM AS IC3
           LEFT JOIN REQCOM AS RC3 ON (RC3.SID = IC3.SID)
           WHERE RC3.TIPOREQ = 'PN'
              AND (IC3.CODSTK = I.CODSTK)
           GROUP BY IC3.CODSTK),0) AS PRENOTA,
   (SELECT 
       MAX(IC.DATEMI) 
    FROM  AS IC 
    LEFT JOIN  AS RC ON (RC.SID = IC.SID)
    WHERE IC.CODSTK = I.CODSTK
       AND RC.TIPOREQ = 'RC') AS DTULTCMP,
   (SELECT 
       TOP 1 RC2.CODCLI+' - '+F.NOME 
    FROM  AS RC2
    LEFT JOIN  AS IC2 ON (IC2.SID = RC2.SID)
    LEFT JOIN  AS F ON (F.CODCLI = RC2.CODCLI)
    WHERE RC2.TIPOREQ = 'RC'
       AND (IC2.CODSTK = I.CODSTK)
    ORDER BY RC2.DATEMI DESC) AS FORN
FROM  AS I
LEFT JOIN  AS S ON (S.CODSTK = I.CODSTK)
LEFT JOIN  AS R ON (R.SID = I.SID)
LEFT JOIN  AS DP ON (DP.CODDEP = S.CODDEP)
WHERE (1=1)
   AND S.CODDEP = [DADO8]
   AND S.CODDEP NOT IN [DADO9]
   AND R.TIPOREQ = 'VD'
   AND R.CODCPV NOT IN [DADO6]
   AND I.DATEMI >= [DADO1]
   AND I.DATEMI <= [DADO2]
   AND I.MARCA = [DADO4]
   AND R.CODVEN = [DADO3]
   AND (S.ESTOQUE <= 0 
   OR S.ESTOQUE <= S.ESTMIN)
   AND ((S.ESTMAX - S.ESTOQUE) <> 0)
GROUP BY I.CODLOJ,S.CODDEP,DP.NOME,I.CODSTK,S.CODFAB,S.MARCA,S.ESTOQUE,S.ESTMIN,S.ESTMAX,S.CONSUMO,S.PRCCUE
ORDER BY 1,2,3