locked
ADD and SUBTRACT depending on the condition is CASE STATEMENT RRS feed

  • Question









  • SELECT CASE WHEN dbo.VU_HRG2.CC_Start_Date_2 = dbo.VU_HRG2.CC_Dis_Date_1 AND dbo.VU_HRG2.CCBDay1 is not NULL THEN 'TRUE'--ISNULL(dbo.VU_HRG2.CCBedDay2,0) - 1 ELSE 'FALSE'--ISNULL(dbo.VU_HRG2.CCBedDay1,0) + 1 END AS CCBDay1 FROM dbo.VU_HRG2
    In this statement the values in the columns are as follows:
    CC_Start_Date_2 = 28-03-2009
    CC_Dis_Date_1 = 28-03-2009
    CCBDay1 = 1
    CCBDay2 = 1
    According to these values the first part of a statement should return True. It is returning False which mean it is going in ELSE Part of the case statement. why it is doing that? any idea?

    For the time being i have commented the add and subtract statement just to verify the condition is working. are these ADD and SUBTRACT Functions fine?
    If uncomment the statement instead of TRUE then it should return  1-1 = 0
    Friday, January 15, 2010 9:41 AM

Answers

  • Something like this?

    SELECT  
    CASE 
    	WHEN  DATEDIFF(D,dbo.VU_HRG2.CC_Start_Date_2, dbo.VU_HRG2.CC_Dis_Date_1) = 0 AND dbo.VU_HRG2.CCBDay1 IS NOT NULL THEN
    'TRUE'
    ELSE  
    'FALSE' --DATEADD(D, 1, ISNULL(dbo.VU_HRG2.CCBedDay1,0)) 
    END AS CCBDay1
    FROM         dbo.VU_HRG2
    

    every day is a school day
    • Proposed as answer by Arif Hasan Friday, January 15, 2010 10:13 AM
    • Marked as answer by MustafaH Friday, January 15, 2010 1:20 PM
    Friday, January 15, 2010 9:58 AM
  • >>If i will use datediff function then it will return me the numbers not date. numbers can be different. Why it is not comparing the date values?

    you want to compare only date but you are comparing date and time. in your case date is same but what about time.

    by using datediff funcation and passing D (date) you compare only date not time and if date is same it will return  0 (zero) otherwise it will return some number.

    Arif
    • Marked as answer by MustafaH Friday, January 15, 2010 1:19 PM
    Friday, January 15, 2010 10:28 AM
  • Testing using the following data produces the correct results for me:

    create table VU_HRG2 (CC_Start_Date_2 datetime, CC_Dis_Date_1 DATETIME, CCBDAy1 INT, CCBDay2 INT)
    
    insert into VU_HRG2 SELECT '28 mar 2009', '28 mar 2009', 1, 1
    insert into VU_HRG2 SELECT '8 jul 2009', '9 jul 2009', NULL, 1
    insert into VU_HRG2 SELECT '6 may 2009', '6 may 2009', 1, 1
    
    SELECT  
    CASE WHEN  
    	(dbo.VU_HRG2.CC_Start_Date_2 = dbo.VU_HRG2.CC_Dis_Date_1) AND dbo.VU_HRG2.CCBDay1 is not NULL  THEN
    'TRUE'
    ELSE  
    'FALSE'
    END AS CCBDay1
    FROM         dbo.VU_HRG2
    

    every day is a school day
    • Marked as answer by MustafaH Friday, January 15, 2010 1:19 PM
    Friday, January 15, 2010 12:05 PM
  • You should do datediff (dd, Date1, Date2) = 0 .

    If you are not interested in the TIME part, you should use DATE data types in SQL Server 2008. That way, you can use the equal operator(=).

    Can you test the following?

    DECLARE @StartDate datetime, @DischargeDate datetime
    SET @StartDate = '2009-08-12 00:00:00.000'
    SET @DischargeDate = '2009-08-12 00:00:00.000'
    
    SELECT CASE
            WHEN @StartDate = @DischargeDate THEN 'True'
            ELSE 'False' END
    GO
    -- True

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by MustafaH Friday, January 15, 2010 1:19 PM
    Friday, January 15, 2010 12:05 PM

All replies

  • Is the datecolumn contains only date or datetime?? Did you check that?

    Friday, January 15, 2010 9:45 AM
  • Thanks for your reply Selva.


    THE DATA TYPE OF DATE COLUMN IS DATETIME.

    Friday, January 15, 2010 9:48 AM
  • This the value 2009-03-28 00:00:00.000 and this value is exactly the same in both columns
    Friday, January 15, 2010 9:51 AM
  • Something like this?

    SELECT  
    CASE 
    	WHEN  DATEDIFF(D,dbo.VU_HRG2.CC_Start_Date_2, dbo.VU_HRG2.CC_Dis_Date_1) = 0 AND dbo.VU_HRG2.CCBDay1 IS NOT NULL THEN
    'TRUE'
    ELSE  
    'FALSE' --DATEADD(D, 1, ISNULL(dbo.VU_HRG2.CCBedDay1,0)) 
    END AS CCBDay1
    FROM         dbo.VU_HRG2
    

    every day is a school day
    • Proposed as answer by Arif Hasan Friday, January 15, 2010 10:13 AM
    • Marked as answer by MustafaH Friday, January 15, 2010 1:20 PM
    Friday, January 15, 2010 9:58 AM
  • Try converting the datetime colum to a varchar as given belwo

    Convert(VarChar(10), dbo.VU_HRG2.CC_Start_Date_2, 101) = Convert(VarChar(10), dbo.VU_HRG2.CC_Dis_Date_1, 101)
    Friday, January 15, 2010 9:59 AM
  • Thanks rich for your reply. I have to compare dates. If i will use datediff function then it will return me the numbers not date. numbers can be different. Why it is not comparing the date values? The data type of both date columns are DATETIME.

    Yes i can use the DATEADD function (Thanks for this)to add the numbers thats is true but first it should meet the condition requirement and should come in the first part instead of else part.

    Does it make sense? Please let me know if i m not explaining properly or u have any question.

    Friday, January 15, 2010 10:05 AM
  • Thanks Selva, no it is not working. what is 101? is it a format code?

    Friday, January 15, 2010 10:18 AM
  • (Cast And Convert)

    http://msdn.microsoft.com/en-us/library/ms187928.aspx
    Happy Coding, RDRaja
    Friday, January 15, 2010 10:27 AM
  • >>If i will use datediff function then it will return me the numbers not date. numbers can be different. Why it is not comparing the date values?

    you want to compare only date but you are comparing date and time. in your case date is same but what about time.

    by using datediff funcation and passing D (date) you compare only date not time and if date is same it will return  0 (zero) otherwise it will return some number.

    Arif
    • Marked as answer by MustafaH Friday, January 15, 2010 1:19 PM
    Friday, January 15, 2010 10:28 AM
  • You have to be careful with datetime. It is not safe to assume that the time part is 00:00:00.000.

    Can you post the results of the following queries?

    SELECT  VU_HRG2.CC_Start_Date_2,
    		VU_HRG2.CC_Dis_Date_1, 
            VU_HRG2.CCBDay1 
    FROM   dbo.VU_HRG2 
    GO
    
    EXEC sp_help VU_HRG2
    GO

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Friday, January 15, 2010 10:47 AM
  • Thanks SQLUSA for your reply. as requested these are the results.

    CC_Start_Date_2

    CC_Dis_Date_1

    CCBday1

    2009-08-12 00:00:00.000

    2009-08-12 00:00:00.000

    1

    2009-08-07 00:00:00.000

    2009-08-07 00:00:00.000

    NULL

    2009-03-28 00:00:00.000

    2009-03-28 00:00:00.000

    1

    2009-06-05 00:00:00.000

    2009-06-05 00:00:00.000

    1

    2009-09-25 00:00:00.000

    2009-09-25 00:00:00.000

    3

    2009-10-08 00:00:00.000

    2009-10-08 00:00:00.000

    2


    Friday, January 15, 2010 11:04 AM
  • What is @@version?

    After removing the "dbo."  prefix everywhere, your query should work.

    As everybody indicated above it is not best practices to use the equal operation (=) with datetime.
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Friday, January 15, 2010 11:25 AM
  • Thanks SQLUSA. I am using SQL Server 2008 64 bit. i have reomved dbo as you said. query is running but it is going in else part. the problem is same. As you have seen the dates are same. It should go to the else part in the second record because CCBdays is null. For other records it should go in the first part of the case statement. 

    So what is the best practice to compare the date?
    Friday, January 15, 2010 11:59 AM
  • Testing using the following data produces the correct results for me:

    create table VU_HRG2 (CC_Start_Date_2 datetime, CC_Dis_Date_1 DATETIME, CCBDAy1 INT, CCBDay2 INT)
    
    insert into VU_HRG2 SELECT '28 mar 2009', '28 mar 2009', 1, 1
    insert into VU_HRG2 SELECT '8 jul 2009', '9 jul 2009', NULL, 1
    insert into VU_HRG2 SELECT '6 may 2009', '6 may 2009', 1, 1
    
    SELECT  
    CASE WHEN  
    	(dbo.VU_HRG2.CC_Start_Date_2 = dbo.VU_HRG2.CC_Dis_Date_1) AND dbo.VU_HRG2.CCBDay1 is not NULL  THEN
    'TRUE'
    ELSE  
    'FALSE'
    END AS CCBDay1
    FROM         dbo.VU_HRG2
    

    every day is a school day
    • Marked as answer by MustafaH Friday, January 15, 2010 1:19 PM
    Friday, January 15, 2010 12:05 PM
  • You should do datediff (dd, Date1, Date2) = 0 .

    If you are not interested in the TIME part, you should use DATE data types in SQL Server 2008. That way, you can use the equal operator(=).

    Can you test the following?

    DECLARE @StartDate datetime, @DischargeDate datetime
    SET @StartDate = '2009-08-12 00:00:00.000'
    SET @DischargeDate = '2009-08-12 00:00:00.000'
    
    SELECT CASE
            WHEN @StartDate = @DischargeDate THEN 'True'
            ELSE 'False' END
    GO
    -- True

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by MustafaH Friday, January 15, 2010 1:19 PM
    Friday, January 15, 2010 12:05 PM
  • Thanks SQLUSA, yes this code is returning TRUE. I cannot change the datatype becuase there is alot of data which i dont  want to change. Thank you so much for your help i will try this now.

     

    Friday, January 15, 2010 12:22 PM
  • Thanks SQLUSA  and all for your help. Could you please tell me how can subtract and add a int value from one int column?

    Friday, January 15, 2010 2:02 PM
  • Try asking an unrelated question in a new thread.

    To subtract an integer from another integer, simply use

    set @NewInt = @OneInt -@AnotherInt

    And for the columns it would be the same

    select One - Another as NewResult ....

    Same goes for addition

    select One + Another as NewResult --providing that your integers are not big enough, otherwise you may want to cast to bigint.

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, January 15, 2010 2:39 PM