November 4, 2015 at 3:21 am
'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
November 4, 2015 at 3:33 am
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'.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 4, 2015 at 6:21 am
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
November 4, 2015 at 7:54 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply