SELECT
	CASE T0.[CardType] WHEN 'L' THEN 'Lead' WHEN 'C' THEN 'Customer' WHEN 'S' THEN 'Supplier' END 'BP Type'
	,T0.[frozenFor] 'Inactive'
	,T0.[CardCode]
    ,T0.[CardName]
    ,T0.[CntctPrsn] 'Main Contact'
    ,T1.[GroupName]
    ,T0.[Phone1] 'Telephone 1'
    ,T0.[Phone2] 'Telephone 2'
    ,CASE 
      WHEN T0.[E_Mail] IS NOT NULL THEN T0.[E_Mail] 
      WHEN T0.[E_Mail] IS NULL THEN (SELECT TOP 1 S1.[E_MailL] FROM OCPR S1 WHERE T0.[CntctPrsn] = S1.[Name] AND S1.[E_MailL] IS NOT NULL AND S1.[E_MailL] <> '')
      ELSE NULL END 'E-mail'
    ,T0.[Notes] 'Quick Remarks'
    ,T0.[Currency]
    ,T2.[PymntGroup]
    ,T6.[Descript] 'Payment Method'
    ,T3.[ListName]
    ,T4.[SlpName] 'Sales Rep.'
    ,T0.[CreditLine] 'Credit Limit'       
    ,T7.[TrnspName] 'Preferred Transportation'
    ,CASE 
      WHEN T0.[FatherType] = 'D' THEN 'Delivery'
      WHEN T0.[FatherType] = 'P' THEN 'Payment'
    ELSE NULL END 'Consolidation Type'

    ,T0.[FatherCard] 'Consolidation Partner Code'
    ,T5.[CardName] 'Consolidation Partner Name'

    ,T0.[ShipToDef] 'Ship-To Address Name'
    ,T0.[MailAddres] 'Ship-To Address Line 1'
    ,T0.[MailStrNo] 'Ship-To Address Line 2'
    ,T0.[MailCity]
    ,T0.[State2]
    ,T0.[MailZipCod]
    ,T0.[MailCountr]

    ,T0.[BillToDef] 'Bill-To Address Name'
    ,T0.[Address] 'Bill-To Address Line 1'
    ,T0.[StreetNo] 'Bill-To Address Line 2'
    ,T0.[City]
    ,T0.[State1]
    ,T0.[ZipCode]
    ,T0.[Country]
    ,T0.[QryGroup1] 'Liquidation Sale List'
    ,T0.[QryGroup2] 'Trade Show Display Sale List'    
    ,T0.[QryGroup3] 'Become a Dealer Leads'
 --   ,T0.[QryGroup4] 'Collections 2014 - No Binder'
 --   ,T0.[QryGroup5] 'Collections 2014 - Including Binder'
    ,T0.[QryGroup6] 'Email Unsubscribes'   
 --   ,T0.[QryGroup7] 'HPMKT Sprint 2015 - Card Leads'   
    ,T0.[QryGroup8] 'Beads & Strings'
	,T0.[QryGroup9] 'Personal Purchase'
	,T0.[QryGroup11] 'E-commerce Only'
	,T0.[QryGroup12] 'Hide from Sales Budgets'
,COALESCE((SELECT Sum(IsNull(S1.[DocTotal] + S1.[DiscSum] + S1.[DpmAmnt] - S1.[TotalExpns] - S1.[VatSum],0))
FROM OCRD S0
Left JOIN OINV S1 ON S0.CardCode = S1.CardCode 
WHERE S0.CardType ='C' AND S1.[CANCELED] = 'N'
And S0.CardCode = T0.CardCode AND DATEPART(yyyy,S1.DocDate) = 2018
),0)

-

COALESCE((SELECT Sum(IsNull(S1.[DocTotal] + S1.[DiscSum] + S1.[DpmAmnt] - S1.[TotalExpns] - S1.[VatSum],0))
FROM OCRD S0
Left JOIN ORIN S1 ON S0.CardCode = S1.CardCode 
WHERE S0.CardType ='C' AND S1.[CANCELED] = 'N' 
And S0.CardCode = T0.CardCode AND DATEPART(yyyy,S1.DocDate) = 2018
),0) 'YTD 2018 Sales'
,COALESCE((SELECT Sum(IsNull(S1.[DocTotal] + S1.[DiscSum] + S1.[DpmAmnt] - S1.[TotalExpns] - S1.[VatSum],0))
FROM OCRD S0
Left JOIN OINV S1 ON S0.CardCode = S1.CardCode 
WHERE S0.CardType ='C' AND S1.[CANCELED] = 'N'
And S0.CardCode = T0.CardCode AND DATEPART(yyyy,S1.DocDate) = 2019
),0)

-

COALESCE((SELECT Sum(IsNull(S1.[DocTotal] + S1.[DiscSum] + S1.[DpmAmnt] - S1.[TotalExpns] - S1.[VatSum],0))
FROM OCRD S0
Left JOIN ORIN S1 ON S0.CardCode = S1.CardCode 
WHERE S0.CardType ='C' AND S1.[CANCELED] = 'N' 
And S0.CardCode = T0.CardCode AND DATEPART(yyyy,S1.DocDate) = 2019
),0) 'YTD 2019 Sales'
,COALESCE((SELECT Sum(IsNull(S1.[DocTotal] + S1.[DiscSum] + S1.[DpmAmnt] - S1.[TotalExpns] - S1.[VatSum],0))
FROM OCRD S0
Left JOIN OINV S1 ON S0.CardCode = S1.CardCode 
WHERE S0.CardType ='C' AND S1.[CANCELED] = 'N'
And S0.CardCode = T0.CardCode AND DATEPART(yyyy,S1.DocDate) = 2020
),0)

-

COALESCE((SELECT Sum(IsNull(S1.[DocTotal] + S1.[DiscSum] + S1.[DpmAmnt] - S1.[TotalExpns] - S1.[VatSum],0))
FROM OCRD S0
Left JOIN ORIN S1 ON S0.CardCode = S1.CardCode 
WHERE S0.CardType ='C' AND S1.[CANCELED] = 'N' 
And S0.CardCode = T0.CardCode AND DATEPART(yyyy,S1.DocDate) = 2020
),0) 'YTD 2020 Sales'

FROM 
    OCRD T0  
    LEFT OUTER JOIN OCRG T1 ON T0.GroupCode = T1.GroupCode 
    LEFT OUTER JOIN OCTG T2 ON T0.GroupNum = T2.GroupNum 
    LEFT OUTER JOIN OPLN T3 ON T0.ListNum = T3.ListNum 
    LEFT OUTER JOIN OSLP T4 ON T0.SlpCode = T4.SlpCode 
    LEFT OUTER JOIN OCRD T5 ON T0.FatherCard = T5.CardCode 
    LEFT OUTER JOIN OPYM T6 ON T0.PymCode = T6.PayMethCod
    LEFT OUTER JOIN OSHP T7 ON T0.ShipType = T7.TrnspCode

WHERE T0.[CardType] IN ('C','L')

ORDER BY T0.[CardCode]