Início / dba / Perguntas / 344288
Accepted
Daylon Hunt
Asked: 2024-12-19 06:04:19 +0800 CST

Campo Máximo da Tabela Agrupada

  • 772

Tenho uma tabela agrupada que se parece com esta:

Data_final_do_mês VersãoId
2024-06-30 1
2024-06-30 2
2024-07-31 1
2024-07-31 2
2024-07-31 3
2024-07-31 5

Para cada fim de mês, quero puxar o Max Version Id para que no final eu tenha datas de Fim de mês exclusivas e max(VersionId)'s . Tentei escrever o código, mas não está funcionando corretamente.

select distinct c.END_OF_MONTH_DATE, c.VERSION_ID, 'Assets'
    from dbo.Contracts_Version_Details c
        inner join(
            select distinct max(c1.Version_Id) as MaxVersionId, c1.END_OF_MONTH_DATE
            from dbo.Contracts_Version_Details c1
            group by c1.End_of_month_date) mv
            on c.VERSION_ID = mv.MaxVersionId
        inner join(
                    select distinct max(c2.END_OF_MONTH_DATE) as MaxDate, c2.VERSION_ID
                    from dbo.Contracts_Version_Details c2
                    group by c2.VERSION_ID
                ) as md
                on c.END_OF_MONTH_DATE = md.MaxDate
sql-server

3 respostas

  1. Best Answer

    Para cada final de mês... datas exclusivas de final de mês e IDs de versão (máx.).

    Que tal isso?

    select 
  End_of_Month_Date
, max( VersionId ) 
from Contracts_Version_Details 
group by End_of_Month_Date
order by End_of_Month_Date 
;
    • 1

  2. Isso pode te deixar mais perto:

    SELECT DISTINCT c.END_OF_MONTH_DATE, c.VERSION_ID, mv.max_version_id, meom.max_EOM, 'Assets'
from dbo.Contracts_Version_Details c
INNER JOIN (
    SELECT END_OF_MONTH_DATE, max(x.version_id) as max_version_id
    FROM dbo.Contracts_Version_Details as x
    GROUP BY END_OF_MONTH_DATE
) as mv
    ON c.END_OF_MONTH_DATE = mv.END_OF_MONTH_DATE
INNER JOIN (
    SELECT x.version_id, max(x.END_OF_MONTH_DATE) as max_EOM
    FROM dbo.Contracts_Version_Details as x
    GROUP BY version_id
) as meom
    ON c.version_id = meom.version_id
    • 0

  3. Para cada final de mês, quero obter o Max Version Id

    MAX(VersionId) OVER (PARTITION BY End_of_Month_Date)é o suficiente.

    • 0

relate perguntas