How to use if statement with case in SQL Server 2014

Question

This my my table

INSERT INTO Paiment (PaimentDate, Champs1, Champs2, Champs3)
VALUES (2013, 90, 70, 60), (2011, 20, 30, 40), (2009, 50, 0, 60), 
       (2008, 60, 16, 60), (2007, 0, 36, 20), (2006, 30, 25, 63),
       (2017, 10, 70, 40), (2019, 20, 30, 32), (2030, 50, 0, 63), 
       (2020, 60, 16, 22), (2018, 0, 36, 33), (2005, 30, 25, 17)

When the entrydate is before 2005, make sum champs1 and champs2 with paimentDate lower to 2009.

Otherwise the entrydate is greater than or equal to 2005, make sum the amounts of champs1 with paimentDate lower than 2003 + the amount champs2 with paimentDate lower than 2001

This is my code , please anybody could help me.

 select  
     (Case 
         when DateofEntry < 2011 AND PaimentDate < 2005

            Then (IsNull(Champs1, 0) + IsNull(Champs2, 0) 
                                                    Else (case when DateofEntry >= 2001
               Then (
                    (CASE WHEN PaimentDate < 2009 THEN ISNULL(Champs1.E1, 0) ELSE 0 END 

                                        +

                                         CASE WHEN PaimentDate < 2011 THEN ISNULL(Champs2.E3, 0) ELSE 0 END)
                  )
          end)

    End)

this is my code . the probleme is when the paiementDate is lower than 2005, it must make sum of all element in champs1 + champs2 with paiment lower than 2005. but in this code , it makes sum of all champs1 + champs2 even if the paiement is higher than 2005 and that is not what i am looking for .

Any help please

`CASE` in T-SQL is an **expression** that evaluates to **exactly ONE, atomic value** - it's not a statement to optionally execute one code branch or another.... — marc_s, Sep 28 '16 at 15:34

Jaydip Jadhav · Accepted Answer · 2016-09-30T14:26:28.343

0

Re-formatted your SELECT statement

SELECT CASE WHEN entrydate < 2005 AND paimentDate < 2009
                   THEN  ISNULL(Champs1, 0) + ISNULL(Champs2, 0) 
                   WHEN entrydate >= 2005 
                   THEN (  
                          CASE WHEN  paimentDate < 2003  AND paimentDate > 2001 THEN  ISNULL(Champs1, 0) END
                          +
                          CASE WHEN  paimentDate <= 2001  THEN  ISNULL(Champs2, 0) END
                        )
       END

edited Sep 30 '16 at 14:26

answered Sep 28 '16 at 14:31

Jaydip Jadhav

12,179
6
24
40

hi @JaydipJ , the probleme is when the paiementDate is lower than 2005, it must make sum of all element in champs1 + champs2 with paiment lower than 2005. but in this code , it makes sum of all champs1 + champs2 even if the paiement is higher than 2005 and that is not what i am looking for . Any help please – Ilyas Sep 30 '16 at 13:58
@llyas Updated script as per your comment *"When the entrydate is before 2005, make sum champs1 and champs2 with paimentDate lower to 2009. Otherwise the entrydate is greater than or equal to 2005, make sum the amounts of champs1 with paimentDate lower than 2003 + the amount champs2 with paimentDate lower than 2001 "* – Jaydip Jadhav Sep 30 '16 at 14:27

How to use if statement with case in SQL Server 2014

1 Answers1