Consulta base estoque

[Rafael Jefferson]

Boa noite,
comecei a utilizar sql tem pouco tem nas minhas rotinas diarias,
hoje me deparei com um problema que a principio é simple, mas não dei conta ainda kkk

tenho uma base de estoque com MES, ANO, ITEM, QTDE INICIAL, ENTRADA, SAIDA, QTD FINAL.

MONTEI UM SCRIPT PRA PEGAR A POSIÇÃO FINAL DO MES. 
SELECT
COD_EMPRESA,
COD_ITEM,
MES_REF,
ANO_REF,
QTD_MES_ANT,
QTD_ENTRADA ,
QTD_SAIDA ,
QTD_FINAL,
CASE
           WHEN QTD_ENTRADA = 0 AND QTD_SAIDA = 0 AND QTD_MES_ANT = 0 THEN 'SEM MOVIMENTO' ELSE 'MOVIMENTO OK' END AS MOVIMENTO
FROM DW_ESTOQUE_HIST
WHERE ANO_REF IN ('2023', '2024')
AND COD_EMPRESA = '01'
AND MES_REF = '06'
ORDER BY ANO_REF , MES_REF

ENTRETANTO ESSA BASE DE DADOS, SÓ MOSTRA A QTD FINAL SE UM ITEM TIVER MOVIMENTAÇÃO NO MES, 
POR EXEMPLO, SE TIVER MOVIMENTO DE ENTRADA E SAIDA MESMO SE A QTDE FINAL FOR ZERO, ELA TRAZ A INFORMAÇÃO.
AGORA SE EU TIVER UM ITEM COM SALDO NO ESTOQUE MAS NÃO TIVE MOVIMENTAÇÃO DE ENTRADA E SAIDA, ELA NÃO TRAZ A INFORMAÇÃO.

TEM ALGUMA FORMA DE CRIAR UMA LOGICA PARA TRAZER O ULTIMO SALDO FINAL DO ITEM PARA OS MESES QUE NÃO TIVERAM MOVIMENTAÇÃO?
EXEMPLO, 

MES   -    ITEM  -   QTD FINAL -   ENTRADA -  SAIDA
02          AAA             100                     150             50
03         AAA             100
04       AAA               100
ATÉ TER MOVIMENTAÇÃO NOVAMENTE. 

[Alex Pereira]

Tente utilizar um CTE para fazer isso: faça um teste com essa query.

 

WITH CTE_ESTOQUE AS (
    SELECT
        COD_EMPRESA,
        COD_ITEM,
        MES_REF,
        ANO_REF,
        QTD_ENTRADA,
        QTD_SAIDA,
        QTD_FINAL,
        LAG(QTD_FINAL) OVER (PARTITION BY COD_EMPRESA, COD_ITEM ORDER BY ANO_REF, MES_REF) AS QTD_MES_ANT
    FROM DW_ESTOQUE_HIST
    WHERE ANO_REF IN ('2023', '2024')
    AND COD_EMPRESA = '01'
)
SELECT
    COD_EMPRESA,
    COD_ITEM,
    MES_REF,
    ANO_REF,
    CASE
        WHEN QTD_ENTRADA = 0 AND QTD_SAIDA = 0 THEN COALESCE(QTD_MES_ANT, 0)
        ELSE QTD_FINAL
    END AS QTD_FINAL,
    QTD_ENTRADA,
    QTD_SAIDA,
    CASE
        WHEN QTD_ENTRADA = 0 AND QTD_SAIDA = 0 AND COALESCE(QTD_MES_ANT, 0) = 0 THEN 'SEM MOVIMENTO'
        ELSE 'MOVIMENTO OK'
    END AS MOVIMENTO
FROM CTE_ESTOQUE
ORDER BY ANO_REF, MES_REF;

A função LAG permite acessar a linha anterior dentro da mesma partição de dados. Desta forma, você consegue buscar o saldo final do mês anterior (QTD_FINAL) para cada item. Na consulta principal, verificamos se não houve movimentação (QTD_ENTRADA = 0 AND QTD_SAIDA = 0). Se não houve movimentação, usamos o saldo final do mês anterior (QTD_MES_ANT). Se houve movimentação, usamos o saldo final atual (QTD_FINAL)

[Rafael Jefferson]

WITH CTE_ESTOQUE AS (

SELECT

COD_EMPRESA,

COD_ITEM,

MES_REF,

ANO_REF,

QTD_ENTRADA,

QTD_SAIDA,

QTD_MES_ANT,

LAG(QTD_MES_ANT) OVER (PARTITION BY COD_EMPRESA, COD_ITEM ORDER BY ANO_REF, MES_REF) AS QTD_MES_ANT_LAG

FROM DW_ESTOQUE_HIST

WHERE ANO_REF IN ('2023', '2024')

AND COD_ITEM = 'EXP.E78'

AND COD_EMPRESA = '01'

)

SELECT

COD_EMPRESA,

COD_ITEM,

MES_REF,

ANO_REF,

CASE

WHEN QTD_ENTRADA = 0 AND QTD_SAIDA = 0 THEN COALESCE(QTD_MES_ANT_LAG, 0)

ELSE QTD_MES_ANT

END AS QTD_FINAL,

QTD_ENTRADA,

QTD_SAIDA,

CASE

WHEN QTD_ENTRADA = 0 AND QTD_SAIDA = 0 AND COALESCE(QTD_MES_ANT_LAG, 0) = 0 THEN 'SEM MOVIMENTO'

ELSE 'MOVIMENTO OK'

END AS MOVIMENTO

FROM CTE_ESTOQUE

ORDER BY ANO_REF, MES_REF;




NESSE SCRIPT ELE TRAZ da mesma forma da anterior, esse item de exemplo, como a ultima movimentação fou uma entrada 2530 no mes 4, ele so puxa o saldo de 45550 até o mes 4, precisava de alguma forma replicar para os proximos meses até que tenha uma nova movimentação. 

[Denicio Junior]

Bom dia @Rafael Jefferson não sei se já resolveu mas esse codigo poderia resolver:

 

-- CTE para selecionar os dados relevantes e calcular o saldo do mês anterior
WITH CTE_ESTOQUE AS (
    SELECT
        COD_EMPRESA,
        COD_ITEM,
        MES_REF,
        ANO_REF,
        QTD_ENTRADA,
        QTD_SAIDA,
        QTD_MES_ANT,
        -- Calcula o saldo do mês anterior usando a função LAG
        LAG(QTD_MES_ANT) OVER (PARTITION BY COD_EMPRESA, COD_ITEM ORDER BY ANO_REF, MES_REF) AS QTD_MES_ANT_LAG
    FROM DW_ESTOQUE_HIST
    WHERE ANO_REF IN ('2023', '2024')
      AND COD_ITEM = 'EXP.E78'
      AND COD_EMPRESA = '01'
),

-- CTE para determinar o saldo final e o status de movimento
CTE_MOVIMENTO AS (
    SELECT
        COD_EMPRESA,
        COD_ITEM,
        MES_REF,
        ANO_REF,
        -- Determina o saldo final considerando as entradas e saídas
        CASE
            WHEN QTD_ENTRADA = 0 AND QTD_SAIDA = 0 THEN COALESCE(QTD_MES_ANT_LAG, 0)
            ELSE QTD_MES_ANT
        END AS QTD_FINAL,
        QTD_ENTRADA,
        QTD_SAIDA,
        -- Define o status de movimento
        CASE
            WHEN QTD_ENTRADA = 0 AND QTD_SAIDA = 0 AND COALESCE(QTD_MES_ANT_LAG, 0) = 0 THEN 'SEM MOVIMENTO'
            ELSE 'MOVIMENTO OK'
        END AS MOVIMENTO
    FROM CTE_ESTOQUE
),

-- CTE para calcular o saldo acumulado, replicando o saldo do mês anterior até que haja uma nova movimentação
CTE_SALDO AS (
    SELECT
        COD_EMPRESA,
        COD_ITEM,
        MES_REF,
        ANO_REF,
        QTD_FINAL,
        QTD_ENTRADA,
        QTD_SAIDA,
        MOVIMENTO,
        -- Calcula o saldo acumulado usando a função SUM com janela
        SUM(QTD_FINAL) OVER (PARTITION BY COD_EMPRESA, COD_ITEM ORDER BY ANO_REF, MES_REF ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SALDO_ACUMULADO
    FROM CTE_MOVIMENTO
)

-- Seleciona os resultados finais ordenados por ano e mês
SELECT
    COD_EMPRESA,
    COD_ITEM,
    MES_REF,
    ANO_REF,
    SALDO_ACUMULADO AS QTD_FINAL,
    QTD_ENTRADA,
    QTD_SAIDA,
    MOVIMENTO
FROM CTE_SALDO
ORDER BY ANO_REF, MES_REF;





 

Explicação

CTE_ESTOQUE:

Esta CTE (Common Table Expression) seleciona os dados relevantes da tabela DW_ESTOQUE_HIST para os anos de 2023 e 2024, filtrando pelo item EXP.E78 e pela empresa 01. Utiliza a função LAG para calcular o saldo do mês anterior (QTD_MES_ANT_LAG), particionando por COD_EMPRESA e COD_ITEM e ordenando por ANO_REF e MES_REF.

CTE_MOVIMENTO:

Esta CTE determina o saldo final (QTD_FINAL) para cada mês. Se não houver entradas e saídas (QTD_ENTRADA = 0 AND QTD_SAIDA = 0), o saldo final é o saldo do mês anterior (QTD_MES_ANT_LAG). Caso contrário, é o saldo do mês atual (QTD_MES_ANT).
Define o status de movimento (MOVIMENTO). Se não houver entradas e saídas e o saldo do mês anterior for zero, o status é 'SEM MOVIMENTO'. Caso contrário, é 'MOVIMENTO OK'.

CTE_SALDO:

Esta CTE calcula o saldo acumulado (SALDO_ACUMULADO) para cada mês, replicando o saldo do mês anterior até que haja uma nova movimentação.
Utiliza a função SUM com uma janela que vai desde o início (UNBOUNDED PRECEDING) até a linha atual (CURRENT ROW), particionando por COD_EMPRESA e COD_ITEM e ordenando por ANO_REF e MES_REF.

Seleção Final:

Seleciona os resultados finais, incluindo o saldo acumulado como QTD_FINAL, e ordena por ano (ANO_REF) e mês (MES_REF).