CODEWITHSUNDEEP
mysqlsqlvb.nettype-conversion
daniel11
daniel11

Reputation: 2041

How do I add a column with an auto-incremental name to a MySql table?

I'm using VB.net to write a MYSQL application and one feature I need to add is the ability to add a column for each week that my employees worked. For example I want the first column to be called "W1" and the next one to be "W2" and so on, right up to "W52". so my question is how would I add a column to my table and have it's name add 1 to its current value? if there wasn't any letters in the name it would be easy but I need it to have the "W" in the title as well to avoid confusion. to add the column I have this:

ALTER TABLE manager ADD W1 MEDIUMINT;

i just need the part that adds an INTEGER to a VARCHAR datatype if possible... Maybe there should be some sort of data type conversion involved?

UPDATE: what i want to display in my vb.net form is a datagrid view that looks exactly like this:

Sample from my spread sheet

Just to explain further, the black bar at the very top are the date stamps for each week, I would like to have this included in the datagridview if possible but it is not required. Under each week column the employees will be entering the percents they worked (example: 20%, or 0.20 is one day of work) I know this is an odd way of doing things but it's mandatory... and I was wondering if it were possible to automate the creation of these columns rather than me having to manually enter them.

Upvotes: 1

Views: 604

Answers (1)

Kevin Burton
Kevin Burton

Reputation: 11934

1 possible solution would be to create 2 new tables

tbl_week: weekid, weekno, start_date, end_date

tbl_weeks_worked: userid, weekid, worked

and pivot the data in the database. (alternativly you could do this in VB.NET)

if you want the header, you will need to union 2 pivoted queries

e.g: 

SELECT '' as userid,
      MAX(CASE WHEN w.weekno = 1 THEN w.start_date END)) AS 'W1', 
      MAX(CASE WHEN w.weekno = 2 THEN w.start_date END)) AS 'W2', 
      MAX(CASE WHEN w.weekno = 3 THEN w.start_date END)) AS 'W3', 
      ........ etc 
FROM tbl_week w
WHERE w.start_date >= start AND w.end_date <= enddate 

UNION ALL

SELECT  
  userid,  
  SUM(CASE WHEN w.weekno = 1 AND ww.weekid IS NOT NULL THEN worked ELSE 0 END)) AS 'W1', 
  SUM(CASE WHEN w.weekno = 2 AND ww.weekid IS NOT NULL THEN worked ELSE 0 END)) AS 'W2', 
  SUM(CASE WHEN w.weekno = 3 AND ww.weekid IS NOT NULL THEN worked ELSE 0 END)) AS 'W3', 
  ........ etc 
FROM tbl_week w
LEFT JOIN tbl_weeks_worked ww ON ww.weekid = w.weekid
WHERE w.start_date >= start AND w.end_date <= enddate 
GROUP BY userid;

Note: (this will only work for a single year view)

Upvotes: 1

Related Questions