CODEWITHSUNDEEP
mysql
Deepak
Deepak

Reputation: 195

how to write a query based on its column

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.

Upvotes: 0

Views: 97

Answers (5)

user3032193
user3032193

Reputation: 1

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);

Upvotes: 0

OGHaza
OGHaza

Reputation: 4795

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

Upvotes: 1

MusicLovingIndianGirl
MusicLovingIndianGirl

Reputation: 5947

"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'

Upvotes: 0

RobertKing
RobertKing

Reputation: 1911

try

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

Upvotes: 1

MusicLovingIndianGirl
MusicLovingIndianGirl

Reputation: 5947

Use CASE..WHEN

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

Upvotes: 0

Related Questions