[Resolved] Convert MySQL query record values

Joseph_Sliker

Hi again;

I am digging through the MySQL docs right now, but am having a little trouble formulating my seach.

Here's the deal:

I'm doing a select query on a table that has a date field.

I would like the result that is returned from the query to tag dates for each record from a date with a descriptive string.

For example, if the date falls between 9/1/03 and 10/31/03, I'd like a field value/string to be returned as "Fall", if the date falls between 11/1/03 and 3/1/04, return the string "Winter", etc.

This specific query syntax seems to work to limit one specific date range:

"SELECT TESTDATE, SCORE FROM DATAORF WHERE TESTDATE > '2003-08-31' AND TESTDATE < '2003-11-01' ORDER BY TESTDATE"

I suspect I may have to figure out how to do subqueries in order to get specific date ranges in separate WHERE clauses, but is there a way to return the specific descriptive string values? This seems maddeningly familiar, but I just can't remember how to do this sort of thing.

Thanks

nemonoman

http://www.mysql.com/doc/en/CASE_Statement.html

Joseph_Sliker

Well, MicroSoft Access IS good for something.

I know how to to construct the sort of query i needed in their graphical query layout system...so I did. When I looked at the resulting SQL formula it showed me the missing piece...pretty simple as it turns out:

SELECT "FALL" AS TAG, TESTDATE, SCORE FROM DATAORF WHERE TESTDATE > '2003-08-31' AND TESTDATE < '2003-11-01' ORDER BY TESTDATE

Now I just have to learn about stringing together three of these in a query/subquery arrangement.

If anyone beats me to that part, let me know.

Thanks!

Joseph_Sliker

Thanks nemonoman for the suggestion...CASE is a new concept to me. I will give it a try from that angle too.

nemonoman

SELECT CASE
WHEN somecolumn<5 THEN "Less than 5"
WHEN somecolumn>5 AND somecolumn<10 THEN "More than 5, less than 10"
ELSE "More than 10"
END
FROM sometable
WHERE someothercolumn=99

In my version of MySQL END CASE fails, but just END works

Joseph_Sliker

This does the job for me...I may not even bother with the subquery route.

SELECT SCORE, TESTDATE,
CASE
WHEN TESTDATE > '2003-08-31' AND TESTDATE < '2003-11-01' THEN "FALL"
WHEN TESTDATE > '2003-10-31' AND TESTDATE < '2004-02-28' THEN "WINTER"
WHEN TESTDATE > '2004-02-27' AND TESTDATE < '2004-06-30' THEN "SPRING"
END
AS TESTPHASE
FROM MYTABLE
WHERE TESTDATE > '2003-08-31' AND TESTDATE < '2004-06-30' ORDER BY TESTDATE

Thanks for the HUGE help...I believe I will have lots of work for this new concept in the future.🙂

nemonoman

You can do tricks, too:

SELECT count(SCORE), CASE
WHEN TESTDATE > '2003-08-31' AND TESTDATE < '2003-11-01' THEN "FALL"
WHEN TESTDATE > '2003-10-31' AND TESTDATE < '2004-02-28' THEN "WINTER"
WHEN TESTDATE > '2004-02-27' AND TESTDATE < '2004-06-30' THEN "SPRING"
END
AS TESTPHASE
FROM MYTABLE
GROUP BY 2

Should produce how many scores for each season
Group by 2 means Group by returned column 2