Para maestros en SQL Server

[haroldjpa]

Finalmente la solucion fue usar la funcion max()

MAX(LF_CLIENT_IDENTIFICATION.CODE)

Necesitaba solo un registro por cada cliente sin importar cual fuera en este caso.


select distinct LF_RECORD.CLIENT_ID,LF_CLIENT.IDENTIFICATION_ID,LF _CLIENT.FIRST_NAME +' '+LF_CLIENT.LAST_NAME as CLIENT_FULL_NAME,LF_CLIENT.EXTERNAL_ID,MAX(LF_CLIE NT_IDENTIFICATION.CODE) as CODE,
SUM(LF_RECORD.VALUE) as SUM_VALUE, SUM(LF_RECORD.QUANTITY) as SUM_QUANTITY
from
LF_RECORD inner join
LF_CLIENT on LF_CLIENT.CLIENT_ID = LF_RECORD.CLIENT_ID inner join
BA_COMPANY_OFFICE on BA_COMPANY_OFFICE.COMPANY_OFFICE_ID = LF_RECORD.COMPANY_OFFICE_ID inner join
BA_COMPANY on BA_COMPANY.COMPANY_ID = BA_COMPANY_OFFICE.COMPANY_ID inner join
LF_CLIENT_IDENTIFICATION on LF_CLIENT_IDENTIFICATION.CLIENT_ID = LF_CLIENT.CLIENT_ID
where
LF_RECORD.LOYALTY_GROUP_ID = @LOYALTY_GROUP_ID and LF_RECORD.RECORD_TYPE='1'
and LF_RECORD.DOCUMENT_DATE BETWEEN @DATE1 AND @DATE2
and LF_CLIENT.IDENTIFICATION_ID like '%' + CAST((CASE WHEN @IDENTIFICATION_ID = '' THEN IDENTIFICATION_ID ELSE @IDENTIFICATION_ID END) AS VARCHAR) + '%'
and LF_RECORD.COMPANY_OFFICE_ID in (@COMPANY_OFFICE_ID)
group by LF_RECORD.CLIENT_ID,LF_CLIENT.IDENTIFICATION_ID,LF _CLIENT.FIRST_NAME +' '+LF_CLIENT.LAST_NAME,LF_CLIENT.EXTERNAL_ID


Muchas gracias a Todos