Para maestros en SQL Server

[haroldjpa]

Hola a todos
Tengo la siguiente consulta
declare @LOYALTY_GROUP_ID numeric(4)
declare @DATE1 varchar(100)
declare @DATE2 varchar(100)
declare @IDENTIFICATION_ID varchar(100)
declare @COMPANY_OFFICE_ID numeric(4)

set @LOYALTY_GROUP_ID = 4
set @DATE1 = '01/01/2011'
set @DATE2 = '24/05/2011'
set @IDENTIFICATION_ID = ''
set @COMPANY_OFFICE_ID = 1


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,
LF_CLIENT_IDENTIFICATION.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.IDENTIFICA… +' '+LF_CLIENT.LAST_NAME,LF_CLIENT.EXTERNAL… LF_CLIENT_IDENTIFICATION.CODE

Esta arroja el siguiente resultado


CLIENT_ID IDENTIFICATION_ID CLIENT_FUL… EXTERNAL_ID CODE SUM_VALUE SUM_QUANTITY
6 1017167504 JULIAN MESA FHC936 810 3.469.090.000 349.628
6 1017167504 JULIAN MESA FHC936 FHC936 3.469.090.000 349.628
10 10238095 GUILLERMO OCHOA CASTRILLON TPX041 218 6.354.000.000 753.459
10 10238095 GUILLERMO OCHOA CASTRILLON TPX041 TPX041 6.354.000.000 753.459
11 102465985 CONSTRUMADERAS LTDA. ELL364 328 2.689.730.000 945.933
11 102465985 CONSTRUMADERAS LTDA. ELL364 329 2.689.730.000 945.933
11 102465985 CONSTRUMADERAS LTDA. ELL364 330 2.689.730.000 945.933



Mi pregunta es, que debo colocar en la consulta SQL y en donde, para que solo me seleccione el primer registro de cada cliente?

[Libras]

Prueba con la funcion ROW_NUMBER ()

Saludos!

[iislas]

Ya hemos comentado que las fechas deben manejarse en formato ANSI (yyyy-mm-dd)

[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

[abel_c_b]

Iniciado por iislas

Ya hemos comentado que las fechas deben manejarse en formato ANSI (yyyy-mm-dd)

Volvistes??? o soy yo el que tenía días de no haber llegado por acá......

[iislas]

Si, regrese el lunes pasado a mi terruño querido, fueron 12 horas de vuelo, Paris-Mexico y la verdad, que estuvo muy satisfactorio el viaje, gracias.....