SELECT DISTINCT TOP 100
ContactPerson.lukLoyaltyCardBusinessUnit,
DirPartyTableLoyalty.OMOperatingUnitNumber,
ContactPerson.ContactPersonId                                                   [ContactPersonId],
-- WHERE-TO-SEND --
/*(SELECT TOP 1 LogisticsElectronicAddress.Locator
    FROM [AX12].[AX2012R3_PROD].[DBO].LogisticsElectronicAddress
    JOIN [AX12].[AX2012R3_PROD].[DBO].DirPartyLocation
    ON LogisticsElectronicAddress.Location                                      = DirPartyLocation.Location
    AND DirPartyLocation.Party                                                  = ContactPerson.Party
    WHERE LogisticsElectronicAddress.Type                                       = 2 --Email
    ORDER BY LogisticsElectronicAddress.IsPrimary                               DESC
) as                                                                            [EmailAddress], -- NOT MANDATORY, CAN BE NULL
ISNULL(
        (SELECT TOP 1 LogisticsElectronicAddress.Locator
         FROM [AX12].[AX2012R3_PROD].[DBO].[LogisticsElectronicAddress]
         JOIN [AX12].[AX2012R3_PROD].[DBO].[DirPartyLocation]
         ON [LogisticsElectronicAddress].Location                               = [DirPartyLocation].Location
         AND [DirPartyLocation].Party                                           = ContactPerson.Party
         WHERE [LogisticsElectronicAddress].IsMobilePhone                       = 1
         AND [LogisticsElectronicAddress].Type                                  = 100 --lukSMS
        ),
        (SELECT TOP 1 LogisticsElectronicAddress.Locator
         FROM [AX12].[AX2012R3_PROD].[DBO].[LogisticsElectronicAddress]
         JOIN [AX12].[AX2012R3_PROD].[DBO].[DirPartyLocation]
         ON [LogisticsElectronicAddress].Location                               = [DirPartyLocation].Location
         AND [DirPartyLocation].Party                                           = ContactPerson.Party
         WHERE [LogisticsElectronicAddress].IsMobilePhone                       = 1
         AND [LogisticsElectronicAddress].Type                                  = 1 --Phone
        )
      ) as                                                                      [MobilePhone],*/
ContactPerson.lukRegistrationDateML                                             [RegistrationDate],
ContactPerson.lukLoyaltyMemberSource                                            [LoyaltyMemberSource],

DirPartyTable.NameAlias                                                         [NameAlias],
CASE ContactPerson.lukWebCity
    WHEN 0 THEN NULL
    ELSE CASE DirPartyTableLoyalty.OMOperatingUnitNumber
         WHEN '' THEN NULL
         ELSE CONCAT(DirPartyTableLoyalty.OMOperatingUnitNumber collate Cyrillic_General_CI_AS ,N's')
    END
END AS                                                                          [LoyaltyShopNum],
ISNULL(lukTreatmentTable.CustAccount, 0) as                                     [TreatmentAccount],
ContactPerson.CustAccount                                                       [CustAccount],
ISNULL(DirPartyTable.lukBirthDate,
       DATEFROMPARTS(   DirPartyTable.BirthYear,
                        DirPartyTable.BirthMonth,
                        DirPartyTable.BirthDay)
       ) as                                                                     [BirthDate],
DirPartyTable.Gender                                                            [GenderCode],


-- SUBSCRIPTIONS --
/*(SELECT TOP 1 Value
FROM [AX12].[AX2012R3_PROD].[DBO].lukDirPartyTableSubscribe
WHERE lukDirPartyTableSubscribe.RefTableId                                      = 2303 --DirPartyTable.TableId
AND lukDirPartyTableSubscribe.RefRecId                                          = DirPartyTable.RecId
AND lukDirPartyTableSubscribe.SubscribeType                                     = 0 --lukSubscribeType::AgreementOnSMS
) as                                                                            [SendSMS],
(SELECT TOP 1 SubscribeDate
FROM [AX12].[AX2012R3_PROD].[DBO].lukDirPartyTableSubscribe
WHERE lukDirPartyTableSubscribe.RefTableId                                      = 2303 --DirPartyTable.TableId
AND lukDirPartyTableSubscribe.RefRecId                                          = DirPartyTable.RecId
AND lukDirPartyTableSubscribe.SubscribeType                                     = 0 --lukSubscribeType::AgreementOnSMS
) as                                                                            [SendSMSDate],
CASE (SELECT TOP 1 Value
      FROM [AX12].[AX2012R3_PROD].[DBO].lukDirPartyTableSubscribe
      WHERE lukDirPartyTableSubscribe.RefTableId                                = 2303 --DirPartyTable.TableId
      AND lukDirPartyTableSubscribe.RefRecId                                    = DirPartyTable.RecId
      AND lukDirPartyTableSubscribe.SubscribeType                               = 1 --lukSubscribeType::AgreementOnEmail
     )
    WHEN NULL THEN 1
    ELSE 0
END AS                                                                          [DoNotSendEmail],
(SELECT TOP 1 SubscribeDate
FROM [AX12].[AX2012R3_PROD].[DBO].lukDirPartyTableSubscribe
WHERE lukDirPartyTableSubscribe.RefTableId                                      = 2303 --DirPartyTable.TableId
AND lukDirPartyTableSubscribe.RefRecId                                          = DirPartyTable.RecId
AND lukDirPartyTableSubscribe.SubscribeType                                     = 1 --lukSubscribeType::AgreementOnEmail
) as                                                                            [SendEmailDate],
(SELECT TOP 1 Value
FROM [AX12].[AX2012R3_PROD].[DBO].lukDirPartyTableSubscribe
WHERE lukDirPartyTableSubscribe.RefTableId                                      = 2303 --DirPartyTable.TableId
AND lukDirPartyTableSubscribe.RefRecId                                          = DirPartyTable.RecId
AND lukDirPartyTableSubscribe.SubscribeType                                     = 2 --lukSubscribeType::BlockSubscribe
) as                                                                            [DoNotSend],
(SELECT TOP 1 SubscribeDate
FROM [AX12].[AX2012R3_PROD].[DBO].lukDirPartyTableSubscribe
WHERE lukDirPartyTableSubscribe.RefTableId                                      = 2303 --DirPartyTable.TableId
AND lukDirPartyTableSubscribe.RefRecId                                          = DirPartyTable.RecId
AND lukDirPartyTableSubscribe.SubscribeType                                     = 2 --lukSubscribeType::BlockSubscribe
) as                                                                            [DoNotSendDate],
(SELECT TOP 1 PersonnelNumber
FROM [AX12].[AX2012R3_PROD].[DBO].lukDirPartyTableSubscribe
WHERE lukDirPartyTableSubscribe.RefTableId                                      = 2303 --DirPartyTable.TableId
AND lukDirPartyTableSubscribe.RefRecId                                          = DirPartyTable.RecId
AND lukDirPartyTableSubscribe.Value                                             = 1
) as                                                                            [PersonnelNumberSubscribe],*/
ContactPerson.lukWebCity                                                        [WebCity]







FROM
-- 3 main tables
[AX12].[AX2012R3_PROD].[DBO].[ContactPerson]                                    ContactPerson
JOIN [AX12].[AX2012R3_PROD].[DBO].[DirPersonName]                               DirPersonName
ON DirPersonName.Person                                                         = ContactPerson.Party
JOIN [AX12].[AX2012R3_PROD].[DBO].[DirPartyTable]                               DirPartyTable
ON DirPartyTable.RecId                                                          = ContactPerson.Party

-- left join loyalty (may not exist)
LEFT JOIN [AX12].[AX2012R3_PROD].[DBO].[DirPartyTable]                          DirPartyTableLoyalty
ON DirPartyTableLoyalty.RecId                                                   = ContactPerson.lukLoyaltyCardBusinessUnit

-- left join treatmentaccount (may not exist)
LEFT JOIN [AX12].[AX2012R3_PROD].[DBO].[CustTable]                              CustTableTreatment
ON CustTableTreatment.Party                                                     = ContactPerson.ContactForParty
JOIN [AX12].[AX2012R3_PROD].[DBO].[lukTreatmentTable]                           lukTreatmentTable
ON lukTreatmentTable.CustAccount                                                = CustTableTreatment.AccountNum
AND EXISTS (SELECT *
            FROM [AX12].[AX2012R3_PROD].[DBO].[lukTreatmentStatusTable]         lukTreatmentStatusTable
            WHERE lukTreatmentStatusTable.RecId                                 = lukTreatmentTable.StatusRecId
            AND lukTreatmentStatusTable.Status                                  = 0) --lukTreatmentStatusState::Opened
            --order by 1 desc
WHERE ContactPerson.lukLoyaltyCardBusinessUnit <> 0
-- overwritten field
/*
-- outer apply first (earliest) address from RetailTransactionTable (may not exist)
OUTER APPLY (SELECT TOP 1   RetailTransactionTable.Address,
                            RetailTransactionTable.TransDate,
                            RetailTransactionTable.RecId
                FROM [AX12].[AX2012R3_PROD].[DBO].[CustTable]                   CustTable
                JOIN [AX12].[AX2012R3_PROD].[DBO].[RetailTransactionTable]      RetailTransactionTable
                ON RetailTransactionTable.CustAccount                           = CustTable.AccountNum
                AND RetailTransactionTable.LoyaltyCardId                        <> ''
                WHERE CustTable.Party                                           = ContactPerson.ContactForParty
                ORDER BY    TransDate                                           ASC,
                            RecId                                               ASC
            ) as                                                                FinalAddress*/




/*JOIN [AX12].[AX2012R3_PROD].[DBO].[CustTable]                         CustTable
ON CustTable.Party                                                      = ContactPerson.ContactForParty
JOIN [AX12].[AX2012R3_PROD].[DBO].[lukTreatmentTable]                   TreatmentTable
ON TreatmentTable.CustAccount                                           = CustTable.AccountNum
AND EXISTS (SELECT *
            FROM [AX12].[AX2012R3_PROD].[DBO].[lukTreatmentStatusTable] TreatmentStatusTable
            WHERE TreatmentStatusTable.RecId                            = TreatmentTable.StatusRecId
            AND TreatmentStatusTable.State                              = 0) --lukTreatmentStatusState::Opened
--TOP 1 RetailTransactionTable.Store ORDER BY RetailTransactiontable.TransDate ASC, RetailTransactionTable.RecId ASC
JOIN [AX12].[AX2012R3_PROD].[DBO].[RetailTransactionTable]              RetailTransactionTable
ON RetailTransactionTable.CustAccount                                   = CustTable.AccountNum
AND RetailTransactionTable.LoyaltyCardId                                <> ''

JOIN [AX12].[AX2012R3_PROD].[DBO].[DirPartyTable]                       DirPartyTableLoyalty
ON DirPartyTableLoyalty.RecId                                           = ContactPerson.lukLoyaltyCardBusinessUnit
JOIN [AX12].[AX2012R3_PROD].[DBO].[DirPartyLocation]                    DirPartyLocation
ON DirPartyLocation.Party                                               = ContactPerson.Party
LEFT JOIN [AX12].[AX2012R3_PROD].[DBO].[lukDirPartyTableSubscribe]  DirPartyTableSubscribeEmail
ON DirPartyTableSubscribeEmail.RefTableId                               = 2303 --DirPartyTable.TableId
AND DirPartyTableSubscribeEmail.RefRecId                                = DirPartyTable.RecId
AND DirPartyTableSubscribeEmail.SubscribeType                           = 1 --lukSubscribeType::AgreementOnEmail
LEFT JOIN [AX12].[AX2012R3_PROD].[DBO].[LogisticsElectronicAddress]         LogisticsElectronicAddressEmail
ON LogisticsElectronicAddressEmail.Location                             = DirPartyLocation.Location
AND LogisticsElectronicAddressEmail.Type                                    = 2 --LogisticsElectronicAddressMethodType::Email
LEFT JOIN [AX12].[AX2012R3_PROD].[DBO].[lukDirPartyTableSubscribe]              DirPartyTableSubscribeSMS
ON DirPartyTableSubscribeSMS.RefTableId                                 = 2303 --DirPartyTable.TableId
AND DirPartyTableSubscribeSMS.RefRecId                                  = DirPartyTable.RecId
AND DirPartyTableSubscribeSMS.SubscribeType                             = 0 --lukSubscribeType::AgreementOnSMS
JOIN [AX12].[AX2012R3_PROD].[DBO].[LogisticsElectronicAddress]          LogisticsElectronicAddressSMS
ON LogisticsElectronicAddressSMS.Location                               = DirPartyLocation.Location
AND LogisticsElectronicAddressSMS.Type                                  = 100 --LogisticsElectronicAddressMethodType::lukSMS
LEFT JOIN [AX12].[AX2012R3_PROD].[DBO].[lukDirPartyTableSubscribe]              DirPartyTableSubscribeBlocked
ON DirPartyTableSubscribeBlocked.RefTableId                             = 2303 --DirPartyTable.TableId
AND DirPartyTableSubscribeBlocked.RefRecId                              = DirPartyTable.RecId
AND DirPartyTableSubscribeBlocked.SubscribeType                         = 2 --lukSubscribeType::BlockSubscribe
JOIN [AX12].[AX2012R3_PROD].[DBO].[LogisticsElectronicAddress]          LogisticsElectronicAddressPhone
ON LogisticsElectronicAddressPhone.Location                             = DirPartyLocation.Location
AND LogisticsElectronicAddressPhone.Type                                = 1 --LogisticsElectronicAddressMethodType::Phone*/



--WHERE ContactPerson.ContactPersonId = N'E000015197'
            --ORDER BY  RetailTransactionTable.TransDate                ASC,
            --          RetailTransactionTable.RecId                    ASC
--          LogisticsElectronicAddress.IsPrimary                DESC