CODEWITHSUNDEEP
mysqldate
Marcx
Marcx

Reputation: 6826

MySql view/procedure to list all the week of a year by months

Like the title says I need to have a final result where on one line I have year- month and week...

Something like that:

------------------------
| Year | Month | Week  |  
| 2012 |   1   |   1   |  
| 2012 |   1   |   2   |  
| 2012 |   1   |   3   |  
| 2012 |   1   |   4   |  
| 2012 |   1   |   5   |  
| 2012 |   2   |   5   |

And so on...

Is it possible to generate a view like this (also using some support table)?

Upvotes: 1

Views: 1695

Answers (2)

tfont
tfont

Reputation: 11233

To add as an extension to this table!
If anyone is looking how to get the date range (first date and last date) of each week the following code will do so:

SELECT date_key AS first_date, MAX(date_key) AS last_date, calendarWeekNo AS week_no 
FROM dates WHERE calendarYear = 2013
GROUP BY `calendarWeekNo`
ORDER BY `calendarWeekNo` ASC

Just change the year to the selected year of your choice! The above example is 2013.

results:

first_date | last_date  | week_no
---------------------------------
2013-01-01 | 2013-01-06 | 1
2013-01-07 | 2013-01-13 | 2
2013-01-14 | 2013-01-20 | 3

etc, etc, etc...

Upvotes: 0

fancyPants
fancyPants

Reputation: 51868

You can do it with a stored procedure:

DELIMITER $$

CREATE PROCEDURE createDatesTable()
BEGIN

DECLARE FullDate date;

DROP TABLE IF EXISTS dates;
CREATE TABLE dates(
date_key date NOT NULL,
calendarYear int NOT NULL,
calendarQuarter tinyint NOT NULL,
calendarMonthNo int NOT NULL,
calendarWeekNo tinyint NOT NULL,
dayNumberOfMonth tinyint NOT NULL,
dayNumberOfWeek tinyint NOT NULL,
PRIMARY KEY (date_key));

SET FullDate  = '2012-01-01';

WHILE (FullDate <= '2012-12-31') DO 
BEGIN

INSERT INTO dates( 
    date_key,
    calendarYear,
    calendarQuarter,
    calendarMonthNo,
    calendarWeekNo,
    dayNumberOfMonth,
    dayNumberOfWeek
)VALUES( 
    FullDate,
    YEAR(FullDate),
    QUARTER(FullDate),
    MONTH(FullDate),
    WEEK(FullDate, 1), /*Have a look at the WEEK() function in the manual!!!*/
    DAYOFMONTH(FullDate),
    DAYOFWEEK(FullDate)
);

SET FullDate = DATE_ADD(Fulldate, INTERVAL 1 DAY);
END;
END WHILE;
END ;
$$
DELIMITER ;

Then do a call createDatesTable()

and you will have your table filled.

Important: ypercube's comment is right. You have to consider this. So have a look at the WEEK() function and its supported modes. Adjust the procedure accordingly.

Upvotes: 3

Related Questions