Não consegui entender o resultado que você quer obter... experimente executar o script abaixo e diga por favor qual deveria ser o resultado retornado:
declare @TbContrato table
(CdContrato int, CdProjeto int);
insert into @TbContrato values
(1, 26),
(2, 26);
declare @TbObra table
(CdObra int, CdContrato int);
insert into @TbObra values
(101, 1),
(102, 1),
(201, 2),
(202, 2);
declare @TbMedicaoContratoAditivo table
(CdObraOs int, CdMedicaoContratoAditivo int);
insert into @TbMedicaoContratoAditivo values
(101, 1011),
(101, 1012),
(102, 1021),
(102, 1022),
(201, 2011),
(201, 2012),
(202, 2021),
(202, 2022);
declare @TbMedicaoSequencia table
(CdMedicaoContratoAditivo int, PercentualConclusaoContrato float, ValorContratoComAditivo money, Status char(2));
insert into @TbMedicaoSequencia values
(1011, 11, 1000, 'AP'),
(1011, 12, 100, 'AP'),
(1012, 13, 1025, 'AP'),
(1012, 14, 125, 'AP'),
(1021, 21, 1000, 'AP'),
(1021, 22, 1001, 'AP'),
(1022, 23, 2000, 'AP'),
(1022, 24, 2002, 'AP'),
(2011, 16, 3000, 'AP'),
(2011, 17, 3003, 'AP'),
(2012, 18, 4000, 'AP'),
(2012, 19, 4004, 'AP'),
(2021, 26, 5000, 'AP'),
(2021, 27, 5005, 'AP'),
(2022, 28, 6000, 'AP'),
(2022, 29, 6006, 'AP');
with
CTE_M as
(
select
c.CdContrato,
o.CdObra,
m.CdMedicaoContratoAditivo,
ROW_NUMBER() OVER
(PARTITION BY c.CdContrato, o.CdObra
ORDER BY m.CdMedicaoContratoAditivo DESC) as RowNum
from @TbContrato as c
inner join @TbObra as o
on o.CdContrato = c.CdContrato
inner join @TbMedicaoContratoAditivo as m
on m.CdObraOs = o.CdObra
where
c.CdProjeto = 26
)
select
c.CdContrato,
c.CdObra,
c.CdMedicaoContratoAditivo,
sum(s.PercentualConclusaoContrato) as PercentualConclusaoContrato,
sum(s.ValorContratoComAditivo) as ValorContratoComAditivo
from CTE_M as c
inner join @TbMedicaoSequencia as s
on s.CdMedicaoContratoAditivo = c.CdMedicaoContratoAditivo
where
c.RowNum = 1 and
s.Status = 'AP'
group by
c.CdContrato,
c.CdObra,
c.CdMedicaoContratoAditivo
Assinatura: http://www.imoveisemexposicao.com.br
