Reputation: 57
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
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.
Upvotes: 1
Views: 69
Reputation: 6038
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;
Upvotes: 1
Reputation: 37493
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
Upvotes: 1