SELECT DISTINCT
SL.CUSTACCOUNT,
SL.ITEMID,
CON.NAME,
CON.CONFIGID,
CON.BEA260DESIGNATIONCOMMERCIALE ,
CASE WHEN INV.NAMEALIAS = 'IXIO-DP1/DP3' AND (CON.BEA260DESIGNATIONCOMMERCIALE LIKE '%IXIO-DP1%' OR CON.BEA260DESIGNATIONCOMMERCIALE LIKE '%DDS-B%') THEN 'IXIO-DP1'
WHEN INV.NAMEALIAS = 'IXIO-DP1/DP3' AND (CON.BEA260DESIGNATIONCOMMERCIALE LIKE '%IXIO-DP3%' OR CON.BEA260DESIGNATIONCOMMERCIALE LIKE '%DDS-A%') THEN 'IXIO-DP3'
WHEN INV.NAMEALIAS = 'IXIO-DT1/DO1' AND (CON.BEA260DESIGNATIONCOMMERCIALE LIKE '%IXIO-DT1%' or CON.BEA260DESIGNATIONCOMMERCIALE NOT LIKE '%IR/Radar AiS1 DO%' AND CON.BEA260DESIGNATIONCOMMERCIALE NOT LIKE '%IXIO-DO1%') THEN 'IXIO-DT1'
WHEN INV.NAMEALIAS = 'IXIO-DT1/DO1' AND (CON.BEA260DESIGNATIONCOMMERCIALE LIKE '%IXIO-DO1%' or CON.BEA260DESIGNATIONCOMMERCIALE LIKE '%IR/Radar AiS1 DO%') THEN 'IXIO-DO1'
WHEN INV.NAMEALIAS LIKE '%LZR%' THEN LEFT(CON.BEA260DESIGNATIONCOMMERCIALE,9)
ELSE INV.NAMEALIAS
END AS NAMEALIAS,
CAST(SL.SALESPRICE as NUMERIC(18,2)) as LASTOFFEREDPRICE,
CAST(SL.CONFIRMEDDLV AS DATE) AS DATE_MAX

FROM SALESLINE SL
LEFT JOIN CUSTTABLE CUS ON CUS.ACCOUNTNUM = SL.CUSTACCOUNT AND SL.DATAAREAID = CUS.DATAAREAID
LEFT JOIN INVENTTABLE INV on INV.ITEMID = SL.ITEMID AND INV.ITEMGROUPID = 'TOC' AND SL.DATAAREAID = INV.DATAAREAID
LEFT JOIN INVENTDIM DIM ON DIM.INVENTDIMID = SL.INVENTDIMID AND SL.DATAAREAID = DIM.DATAAREAID
LEFT JOIN CONFIGTABLE CON ON CON.CONFIGID = DIM.ConfigID AND CON.ITEMID = SL.ITEMID AND SL.DATAAREAID = CON.DATAAREAID
INNER JOIN
(
	SELECT SL3.CUSTACCOUNT, SL3.ITEMID, CON.CONFIGID, CAST(MAX(SL3.CONFIRMEDDLV) AS DATE) AS DATE_MAX
    FROM SALESLINE SL3
    LEFT JOIN INVENTDIM DIM ON DIM.INVENTDIMID = SL3.INVENTDIMID AND SL3.DATAAREAID = DIM.DATAAREAID
	LEFT JOIN CONFIGTABLE CON ON CON.CONFIGID = DIM.ConfigID AND CON.ITEMID = SL3.ITEMID AND SL3.DATAAREAID = CON.DATAAREAID
    WHERE
    SALESPRICE  > '1'
	AND SALESSTATUS <> '4'
	AND LINEPERCENT = '0'
	AND SL3.ITEMID LIKE '0%'
	AND BEA269ECHANTILLON = '0'
	AND SL3.DATAAREAID = 'BEA'
	AND CURRENCYCODE = 'EUR'
	AND SL3.CREATEDDATETIME > '20070401'
    GROUP BY CUSTACCOUNT, SL3.ITEMID, CON.CONFIGID
    ) SL2 ON SL.CUSTACCOUNT = SL2.CUSTACCOUNT AND SL.ITEMID = SL2.ITEMID and CON.CONFIGID = SL2.CONFIGID and CAST(SL.CONFIRMEDDLV AS DATE) = SL2.DATE_MAX
WHERE
SL.SALESPRICE  > '1'
AND SL.SALESSTATUS <> '4'
AND SL.LINEPERCENT = '0'
AND SL.ITEMID LIKE '0%'
AND SL.BEA269ECHANTILLON = '0'
AND SL.DATAAREAID = 'BEA'
AND SL.CURRENCYCODE = 'EUR'
AND SL.CREATEDDATETIME > '20070401'
AND CUS.CUSTGROUP = 'C'
AND INV.BEAARTICLEVIVANT = '1'
order by CUSTACCOUNT