CREATE   VIEW [dbo].[ETIKET_ADDR]
AS
WITH QQ AS (
    -- LG_011_CLCARD tablosu için alt sorgu
    SELECT DISTINCT 
        CODE, 
        CONCAT(
		DEFINITION_
            ,' (', 
            UPPER(TOWN), 
            '/', 
            UPPER(CITY),
            ') - '
        ) AS COMPANY,
        CONCAT(ADDR1, ' ', ADDR2) AS ADDR, 
		ADDR1,
        TELNRS1, 
        UPPER(TOWN) AS TOWN, 
        DEFINITION_, 
        UPPER(CITY) AS CITY, 
        CC.ADRESSNO AS KARGO, 
        LOGICALREF
    FROM 
        [UNIVERSALDB].[dbo].[LG_011_CLCARD] AS CC
    WHERE 
        ACTIVE = 0 
        AND CODE NOT LIKE '%FIS%' 
        AND CODE NOT LIKE '%FSB%' 
        AND CODE NOT LIKE '%FS%' 
        AND CODE NOT LIKE '%SB.F%'
        AND CODE NOT LIKE '%S.F%'
        AND DEFINITION_ NOT LIKE '%PERAKENDE SATISLAR%'
		AND DEFINITION_<>''
		AND CODE NOT LIKE 'Z%'
		AND CODE<>'21,000'
		AND LOGICALREF NOT IN (1324)

    UNION ALL

    -- LG_011_SHIPINFO tablosu için alt sorgu
    SELECT DISTINCT 
        '' AS CODE,  
        CONCAT([NAME],
            ' (', 
            UPPER(TOWN), 
            '/', 
            UPPER(CITY),
            ') - '
        ) AS COMPANY,
        CONCAT(ADDR1, ' ', ADDR2) AS ADDR, 
		ADDR1,
        TELNRS1, 
        UPPER(TOWN) AS TOWN, 
        [NAME] AS DEFINITION_, 
        UPPER(CITY) AS CITY, 
        '' AS KARGO, 
        CLIENTREF AS LOGICALREF
    FROM 
        [UNIVERSALDB].[dbo].[LG_011_SHIPINFO] AS SP WHERE CLIENTREF NOT IN (1324)
)

SELECT
    COMPANY, 
    ADDR, 
    TELNRS1, 
    TOWN, 
    DEFINITION_, 
    CITY, 
    KARGO, 
    LOGICALREF,
	rn
FROM (
    SELECT 
        QQ.*, 
        ROW_NUMBER() OVER (PARTITION BY QQ.ADDR1 ORDER BY QQ.CODE DESC) AS rn
    FROM 
        QQ
) AS numbered
WHERE 
    rn = 1 OR LOGICALREF = 845