0

I have a table named TableA, as below;

+----+---------------+--------------+--------------+ | Id | Frequency | Inserted_Date| Eligibility | +----+---------------+--------------+--------------+ | 1 | Halfyearly | 23/8/2013 | | | 2 | Quaterly | 24/11/2013 | | +----+---------------+--------------+--------------+

Here Eligibility column is initially empty.I need to write a query where Eligibility is set to be yes /no based on the frequency for current month.

for 1st record, as of today Eligibility=No

for 2nd record, as of today Eligibility=Yes

+----+---------------+--------------+--------------+ | Id | Frequency |Inserted_Date | Eligibility| +----+---------------+--------------+--------------+ | 1 | Halfyearly | 23/8/2013 | No | | 2 | Quaterly | 24/11/2013 | Yes | +----+---------------+--------------+--------------+

I have no idea how to write a query for getting the above output and more over i need to change it for every month.

Please help me out.

Thank you all in advance for your response.

Improve this question
4
  • 1. Store dates using a DATE data type 2. (Usually) Don't store data that can be easily calculated on the fly. And shouldn't that be eligibilililility? ;-) Commented Nov 25, 2013 at 11:29
  • Maybe it's just me, but I can't tell how you're determining the value of Eligibilility. In fact, I can't tell what the table represents either. Commented Nov 25, 2013 at 11:38
  • @OGHaza im ll be doing this change every month. so the current month im checking whether this record is eligible or not. Commented Nov 25, 2013 at 11:42
  • Maybe if you add more example data it will be more clear - Ahh, I might understand, if Inserted date is 2013-01-01 and Frequency was Halfyearly, then Eligibility = 1 if current month is Jan 2013, Jul 2013, Jan 2014, Jul 2014 etc.. Commented Nov 25, 2013 at 11:49

5 Answers 5

Reset to default
1

The way I understand it, if Inserted_Date = Jan 2013 and Frequency = 'Halfyearly', then Eligibility = 1 when the current month is Jan 2013, Jul 2013, Jan 2014, Jul 2014 etc..

UPDATE TableA SET Eligibility = CASE WHEN (Frequency = 'Halfyearly' AND MONTH(Inserted_Date) % 6 = MONTH(NOW()) % 6) OR (Frequency = 'Quarterly' AND MONTH(Inserted_Date) % 3 = MONTH(NOW()) % 3) THEN 'Yes' ELSE 'No' END

If you have Annually as well you can just check 

MONTH(Inserted_Date) = MONTH(NOW())

See this working on SQLFiddle

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

try

UPDATE TableA SET ELIGIBILITY = CASE WHEN DATEPART(MM,GETDATE()) = DATEPART(MM,DATE) THEN 'YES' ELSE 'NO' END
Improve this answer

1 Comment

i using this query in a stored procedure, which is triggered every month.so Eligibility column will be changed correspondingly
0

Use CASE..WHEN

UPDATE tbl SET Eligibility = 'Your value' Frequency WHEN 'Half Yearly' THEN 'No' WHEN 'Quarterly' THEN 'Yes'
Improve this answer

1 Comment

based on Inserted_Date , i need to set the value for Eligibility.
0

"based on Inserted_Date , i need to set the value for Eligibility."

Then use

UPDATE tbl SET Eligibility = CASE WHEN InsertedDate='' THEN 'No' WHEN '' THEN 'Yes'
Improve this answer

2 Comments

in the first row, Inserted_Date='23/8/2013' , then Eligibility= yes for the month of august and february, for rest of the other months Eligibility =No.
Do you have column to store the month's name?
0

You can get the required output with below query: 

SELECT ID, Frequency, Date, (CASE WHEN TO_CHAR (SYSDATE, 'mm') = TO_CHAR (Date, 'mm') THEN YEligibilility ELSE NEligibilility END) AS Eligibilility FROM (SELECT ID, Frequency, Date, 'Yes' AS YEligibilility, 'No' AS NEligibilility From TableA);
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.