CODEWITHSUNDEEP
mysql
TolMera
TolMera

Reputation: 416

MYSQL - Period Table to Normal "Year-Month-00"

I'm currently trying to turn the following info into a usable date field.

I have the following:

Date      |Period|Product

0000-00-00|   31 |Skies

0000-00-00|  17  |Shoes

0000-00-00|   4  |Etc...

The period is the number of monthis since the April Financial Year.

        Apri|May|June|July|Augu|Sept|Octo|Nove|Dece|Janu|Febu|Marc
2012   |1   |2  |3   |4   |5   |6   |7   |8   |9   |10  |11  |12    (Ends March 2013)

2011   |13  |14 |15  |16  |17  |18  |19  |20  |21  |22  |23  |24    (Ends March 2012)

2010   |25  |26 |27  |28  |29  |30  |31  |32  |33  |34  |35  |36    (Ends March 2011)

What im looking for as a result is

Date      |Period|Product

2010-10-00|   31 |Skies

2011-08-00|  17  |Shoes

2012-07-00|   4  |Etc...

Ive tried DateDiff but since the Period sheet im working from is backwards (and the worst thing I have ever seen) DateDiff doesn't do what I need.

The reason it's not doing what i need is that the month after period 24 (2011-03) is Period 1 (2011-04) So its not sequential. Its just down right stupid. Same applies to period 36 (2010-03) next period is 25 (2010-04)

Anyone who can solve this earns my instant respect. Good gods this has been painful to work out.

Working in MYSQL, but happy to take on any solution. Thanks

(Thanks to the person who edited the stuff for me, I could not find how to give the text a decent layout.)

Upvotes: 0

Views: 263

Answers (1)

Harald Brinkhof
Harald Brinkhof

Reputation: 4455

I'm not sure If I understand your question correctly but you can simply add months to a date with mysql's DATE_ADD function (and manipulate them using other date/time functions)

So april 2012 with 4 added to it gives you august, which is what you're looking for, right?

SELECT DATE_ADD(CONCAT(2012,'-04-01'),INTERVAL 4 MONTH),
MONTHNAME(DATE_ADD(CONCAT(2012,'-04-01'),INTERVAL 4 MONTH));


+--------------------------------------------------+-------------------------------------------------------------+
| DATE_ADD(CONCAT(2012,'-04-01'),INTERVAL 4 MONTH) | MONTHNAME(DATE_ADD(CONCAT(2012,'-04-01'),INTERVAL 4 MONTH)) |
+--------------------------------------------------+-------------------------------------------------------------+
| 2012-08-01                                       | August                                                      |
  +--------------------------------------------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

edit: I get the impression that what you want to calculate might be rather the data given from the current year april - the months in your table, that would be done with DATE_SUB. As you can see you can just use the YEAR() and NOW() functions to get the current year.

SELECT DATE_SUB(CONCAT(YEAR(NOW()),'-04-01'),INTERVAL 4 MONTH),
MONTHNAME(DATE_SUB(CONCAT(YEAR(NOW()),'-04-01'),INTERVAL 4 MONTH));


+---------------------------------------------------------+--------------------------------------------------------------------+
| DATE_SUB(CONCAT(YEAR(NOW()),'-04-01'),INTERVAL 4 MONTH) | MONTHNAME(DATE_SUB(CONCAT(YEAR(NOW()),'-04-01'),INTERVAL 4 MONTH)) |
+---------------------------------------------------------+--------------------------------------------------------------------+
| 2011-12-01                                              | December                                                           |
+---------------------------------------------------------+--------------------------------------------------------------------+
1 row in set (0.02 sec)

Calculating the months between 2 dates is done with PERIOD_DIFF, it needs a specific format though and months with only 1 digit will need 0 prepended.

SELECT 
PERIOD_DIFF(CONCAT(YEAR(NOW()),'04'),    -- year + '04' for YYYYMM
            CONCAT(YEAR('2011-08-01'),   -- year YYYY
                   IF(MONTH('2011-08-01') < 10, -- got 2 digits for month?
                       CONCAT('0',MONTH('2011-08-01')), -- no, prepend '0' 
                       MONTH('2011-08-01')) -- yes just return month
             )
) AS difference;

+------------+
| difference |
+------------+
|          8 |
+------------+

Finally found after I let my brain rest, it was one of those forest - trees things. ;)

SET @period=15;  

SELECT DATE_ADD(  -- use date_add so we can go back and forwards in time
  CONCAT(YEAR(NOW()) - FLOOR(IF(@period < 10,0,IF(@period < 12,-1,@period/12 - IF(@period%12 = 0,2,0))) ) ,'-03-01'), -- start from march this year so +1  equals april, subtract 1 year per 12 months
INTERVAL
 IF(@period%12 < 10,@period%12,@period%12 - 12) -- ignore the full years, if rest < 10 add (APR - DEC) else subtract (rest - 12 to get negative number)
MONTH) 
AS result;

this will always return the correct date in 1 swoop, I guarantee. :)

Upvotes: 1

Related Questions