CASE Statement

  • '2013-01-01'

    withdrawal_date

    resigned_date

    I want to check, if the member was active during '2013-01-01 and withdrawal_date. Now withdrawal_date can be anything really, it can be '2014-01-05' or '2010-05-02'....

    And I'm using resigned_date as a field to check with, If the member was active during that period( '2013-01-02 and withdrawal_date), it would mean they resigned after that period('2013-01-01 and withdrawal_date)

    I tried to do CASE statement but I can't get it right

    SELECT

    CASE WHEN ((b_dt_resigned <= '2013-01-01') and (b_dt_resigned >= withdrawal_date)) THEN 'Y' ELSE 'N' END [MetHealth_Prior_Ind]

    FROM table_name

    please help

  • hoseam (11/4/2015)


    '2013-01-01'

    withdrawal_date

    resigned_date

    I want to check, if the member was active during '2013-01-01 and withdrawal_date. Now withdrawal_date can be anything really, it can be '2014-01-05' or '2010-05-02'....

    And I'm using resigned_date as a field to check with, If the member was active during that period( '2013-01-02 and withdrawal_date), it would mean they resigned after that period('2013-01-01 and withdrawal_date)

    I tried to do CASE statement but I can't get it right

    SELECT

    ((b_dt_resigned BETWEEN '2013-01-01' AND withdrawal_date) AND (b_dt_resigned > '2013-01-01'))

    FROM table_name

    please help

    I think you might also need the date on which the 'member' became active?

    With that, you know that the period between date start and date resigned is the active period.

    And if this overlaps with the period from '20130102' (best practice is not to use hyphens in literal dates, by the way) to withdrawal date, the answer is 'true'.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • hoseam (11/4/2015)


    '2013-01-01'

    withdrawal_date

    resigned_date

    I want to check, if the member was active during '2013-01-01 and withdrawal_date. Now withdrawal_date can be anything really, it can be '2014-01-05' or '2010-05-02'....

    And I'm using resigned_date as a field to check with, If the member was active during that period( '2013-01-02 and withdrawal_date), it would mean they resigned after that period('2013-01-01 and withdrawal_date)

    I tried to do CASE statement but I can't get it right

    SELECT

    ((b_dt_resigned BETWEEN '2013-01-01' AND withdrawal_date) AND (b_dt_resigned > '2013-01-01'))

    FROM table_name

    please help

    Hello,

    sorry, but I am absolutely lost in the question. Tried to read it several times, but still no idea. What tells me, that a member was active? How should I understand "active during ..."? What is withdrawal_date and what is resigned_date? Why are the dates 20130101 and 20130102 mentioned explicitly, is it some constant for the comparison?

    You say that you tried CASE, but then post a code that does not contain any CASE, and uses previously not mentioned column "b_dt_resigned" ... ?

    And what is the desired result? Just a Yes/No? Or some date range, number of days...?

    Please try to explain the question better... and add table definition and some sample data + desired output for this.

    Thanks, Vladan

  • As some others suggest: your question and setup is not clear at all.

    Could you please provide DDL for your tables and some test data with example of required output. It would help a lot to get right answers here...

    Link at the bottom of my signature.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply