Case when Statement Not Working with my data

Question

While Working case statement i got stuck

For Eg i have below scenario

Amt         StartDate   EndDate     Port    Trade
10.00       9/21/2018   9/21/2020   NULL    NULL
54,523.00   11/14/2018  11/15/2018  NULL    NULL
756.00      11/14/2018  11/15/2018  NULL    NULL
456.00      11/14/2018  11/15/2018  NULL    NULL
86.00       11/14/2018  11/15/2018  NULL    NULL
86.00       11/14/2018  11/15/2018  NULL    NULL
453.00      11/14/2018  11/15/2018  NULL    NULL
786.00      11/14/2018  11/15/2018  NULL    NULL
86.00       11/14/2018  11/15/2018  NULL    NULL
568.00      11/14/2018  11/15/2018  NULL    NULL
12,358.00   11/14/2018  11/15/2018  NULL    NULL
45,388.00   11/5/2018   12/5/2018   NULL    NULL
75,368.00   8/9/2018    12/20/2018  call    collateral
783,678.00  7/13/2018   1/14/2019   NULL    NULL

• 1)what i am looking for is when startdate and enddate difference = 1 or Port like '%call%' and Trade='collateral' then amt
• 2) when startdate and enddate difference > 7 and Port not like '%call%' and Trade <> 'collateral' then amt

My First Condition Works

select CASE WHEN DATEDIFF(DAY,CAST(Startdate AS DATE),CAST(Enddate AS DATE))=1
      OR (Port like 'Call' and [Trade]='collateral') 
   THEN amt ELSE 0 END AS money1
   from tablename

But Second Condition not Working

select CASE WHEN (DATEDIFF(DAY,CAST(Startdate AS DATE),CAST(Enddate AS DATE))> 7 AND 
                DATEDIFF(DAY,CAST(Startdate AS DATE),CAST(Enddate AS DATE)) <= 9999) 
                and [Trade] <> 'collateral' and Portfolio not like '%Call%'
                THEN amt ELSE 0 END AS money2    from tablename

excepted o/p

Amt         StartDate   EndDate money1              money2
10.00       9/21/2018   9/21/2020                   10.00
54,523.00   11/14/2018  11/15/2018  54,523.00   
756.00      11/14/2018  11/15/2018  756.00  
456.00      11/14/2018  11/15/2018  456.00  
86.00       11/14/2018  11/15/2018  86.00   
86.00       11/14/2018  11/15/2018  86.00   
453.00      11/14/2018  11/15/2018  453.00  
786.00      11/14/2018  11/15/2018  786.00  
86.00       11/14/2018  11/15/2018  86.00   
568.00      11/14/2018  11/15/2018  568.00  
12,358.00   11/14/2018  11/15/2018  12,358.00   
45,388.00   11/5/2018   12/5/2018                   45,388.00
75,368.00   8/9/2018    12/20/2018  75,368.00   
783,678.00  7/13/2018   1/14/2019                   783,678.00

Need Help i am using Sql server 2012.

Answer 1

Check whether PortFolio and Trade are NULL.
So:

SELECT CASE WHEN DATEDIFF(DAY,CAST(Startdate AS DATE),CAST(Enddate AS DATE))> 7 AND 
                 DATEDIFF(DAY,CAST(Startdate AS DATE),CAST(Enddate AS DATE)) <= 9999 AND
                 ([Trade] IS NULL OR [Trade] <> 'collateral') AND
                 (Portfolio IS NULL OR Portfolio NOT LIKE '%call%')
            THEN amt ELSE 0 END AS money2
FROM tablename;

Answer 2

Your like operator should be '%Call%'

select CASE WHEN (DATEDIFF(DAY,CAST(Startdate AS DATE),CAST(Enddate AS DATE))> 7 AND 
                DATEDIFF(DAY,CAST(Startdate AS DATE),CAST(Enddate AS DATE)) <= 9999) 
                and [Trade] <> 'collateral' and Portfolio not like '%Call%'
                THEN amt ELSE 0 END AS money2 from tablename