rkordonsky 63916 - Thursday, December 14, 2017 8:26 AM

Dear colleagues,
I got a very peculiar situation. Here is my code:

Declare @startdate as datetime
Declare @enddate as datetime
Declare @newfee as money
DeclaRE @oldfee as money
Set @startdate = '2014-09-01'
Set @enddate = '2017-08-31'
Set @newfee = 595.00
Set @oldfee = 795.00

select
n.ID as IndID,
n.FULL_NAME,
u.YAB,
CASE WHEN n.MEMBER_TYPE IN('NLS','SFI','SFINL','SI','SIF') THEN 'Sponsor' ELSE 'Nonsponsor' END as Sponsor,
CASE WHEN ((select SUM(TOTAL_PAYMENTS) from Orders where ST_ID = n.ID and ORDER_DATE BETWEEN @startdate and @enddate) < 0) THEN 0 ELSE (select SUM(TOTAL_PAYMENTS) from Orders where ST_ID = n.ID and ORDER_DATE BETWEEN @startdate and @enddate) END as "3-Year Spending",
CASE
     WHEN DATEDIFF(YEAR, CAST(ISNULL(u.YAB,'2000') As Datetime), @enddate) < 3
     THEN (select SUM(TOTAL_PAYMENTS) from Orders where ST_ID = n.ID and ORDER_DATE BETWEEN @startdate and @enddate) / (DATEDIFF(YEAR,CAST(ISNULL(u.YAB,'2000') As Datetime),@enddate))
     ELSE (select SUM(TOTAL_PAYMENTS) from Orders where BT_ID = n.ID and ORDER_DATE BETWEEN @startdate and @enddate) / 3 END as average_annual,
n.WORK_PHONE,
n.EMAIL
from Name as n --inner join Orders as o on n.ID = o.BT_ID
/*
    CROSS APPLY (SELECT TOP 1 *
      FROM BM_OnlinePass_Subscription_Archive ba
      WHERE ba.ClientID = n.ID
      ORDER BY StartDate DESC) as b --on n.ID = b.clientID
*/
    INNER JOIN UD_Indiv_Data as u on n.ID = u.ID
    --LEFT OUTER JOIN BM_OnlinePass_Subscription_Archive as b on n.ID = b.clientID
where n.COMPANY_RECORD = 0
and Len(RTrim(LTrim(n.Full_Name))) > 0
and n.MEMBER_TYPE IN('NLS','SFI','SFINL','SI','SIF','NI','NIF','NL','SYSI','WEB')
and n.Status = 'A'
and (n.ID NOT IN(Select ClientID from BM_OnlinePass_Subscription) OR n.ID NOT IN(Select ClientID from BM_OnlinePass_Subscription_Archive))

In my case u.YAB has varchar datatype and sometimes has NULL value. What is peculiar this date conversion error I got when removed BM_OnlinePass_Subscription_Archive table. When this table was in a query I did not get any error.
Any idea why? Thank you.