none
Comparing two columns using a case statement

    Question

  •  I have a requirement in which i need to compare two columns using a case statement.

    i have two columns last used year and last used month

    Now i need to compare the two columns with a cutoff date ( Which is 6 months back date from today ). if the last use dyaer and month are within the cutoff date then flag should be 1.

    For Ex: if today is 01.01.2013 then cutoff would be 01.07.2012.

    Values for Last used year column and last used month are 2013 and 1 respectively.

    I have used the below code which is throwing an error

    Case When (( LastUsedYear >=  Year(CutoffDate) and LastusedMonth >= Month(CutoffDate) )

    Then 1 else 0 end as FlagValue

    For the above example the flag value should be one.

    Could anyone help me in this logic.?

    Thanks in Advance..!!


    Balaji - BI Developer

    Saturday, January 19, 2013 9:56 AM

Answers

  • If I get this correctly:

    Case When LastUsedYear > Year(CutoffDate) OR
              LastUsedYear = Year(CutOffDate) AND LastusedMonth >= Month(CutoffDate)
         Then 1
         else 0
    end as FlagValue


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, January 20, 2013 4:37 PM

All replies

  • Can you post the error? Thanks.

    The following parses OK in SSMS 2012 (1 parent removed):

    select 
    
    Case When ( LastUsedYear >=  Year(CutoffDate) and LastusedMonth >= Month(CutoffDate) )
     
    Then 1 else 0 end as FlagValue
    
    from table1


    Kalman Toth SQL 2008 GRAND SLAM
    Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Saturday, January 19, 2013 1:59 PM
  • It might make more sense for you to create a computed column to generate the last used date in the format yyyy-mm-dd 00:00:00.000

    and then use the datediff function instead . this way you can get around the quirks of datetime such as 30 of FEb  12.31.2010 being read as 31.12.2010 etc


    Jayanth Kurup - www.enabledbusiness.com

    Saturday, January 19, 2013 2:41 PM
  • I think you missing a bracket before the THEN keyword in the CASE statement.

    The following seems to work:

     

    DECLARE @MyValues TABLE
    (
    	LastUsedYear	SMALLINT,
    	LastUsedMonth	TINYINT
    )
    
    DECLARE @CutOffDate	DATE;
    
    SET @CutOffDate = '20120701';
    
    INSERT @MyValues
    VALUES (2013, 1)
    
    
    SELECT Case When (( LastUsedYear >=  Year(@CutOffDate) and LastusedMonth >= Month(@CutOffDate) ))
    	   Then 1 else 0 end as FlagValue
    FROM @MyValues
    

     
    Saturday, January 19, 2013 3:10 PM
  • Hi Balaji,

    You can store the last date & month as a date column, then do this to return the flag value

    case when last_month_date between dateadd(mm,-6,cast('01/01/2013' as datetime)) and cast('01/01/2013' as datetime) then 1 else 0 end as FlagValue


    Many Thanks & Best Regards, Hua Min

    Saturday, January 19, 2013 3:41 PM
  • Hi Kalman,

    It was not throwing any specific error but showing the wrong results when comparing two different columns like in the above example.

    It should show 1 bcoz the 2013 and month 1 is within the lat six months. But the results are showing 1.


    Balaji - BI Developer

    Sunday, January 20, 2013 4:28 PM
  • Hi All,

    Thank you so much for the quick replies. i have concatenated the two columns into a date column as suggested, taken 01 as default date and now comparing with  the cutoff date. 

    if i'm wrong please correct me.!!



    Balaji - BI Developer

    Sunday, January 20, 2013 4:34 PM
  • If I get this correctly:

    Case When LastUsedYear > Year(CutoffDate) OR
              LastUsedYear = Year(CutOffDate) AND LastusedMonth >= Month(CutoffDate)
         Then 1
         else 0
    end as FlagValue


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, January 20, 2013 4:37 PM
  • SQL has a temporal data type; why are you using strings? That is hoqw we wrote COBOL inthe 1960's. Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise.

    CREATE TABLE Something_Report_Periods
    (something_report_name CHAR(10) NOT NULL PRIMARY KEY
       CHECK (something_report_name LIKE <pattern>),
     something_report_start_date DATE NOT NULL,
     something_report_end_date DATE NOT NULL,
      CONSTRAINT date_ordering
        CHECK (something_report_start_date <= something_report_end_date),
    etc);

    These report periods can overlap or have gaps. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[0-3][0-9]-00'


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, January 20, 2013 4:52 PM
  • Hi All,

    Thank you so much for the quick replies. i have concatenated the two columns into a date column as suggested, taken 01 as default date and now comparing with  the cutoff date. 

    if i'm wrong please correct me.!!



    Balaji - BI Developer

    Hi,

    Did you try the similar way I've shown above?


    Many Thanks & Best Regards, Hua Min

    Monday, January 21, 2013 4:35 AM