vigneshr35 — 2010-05-11T03:46:13-04:00 — #1
I have a table, does not contain a date field in it. It has day of week values 1 to 7. Considering that i retrieve these 7 rows from the table, I want an extra field, say 'test_date' created whose first row value will be a date I want to start with and then auto increment the date value for the next 6 rows. is there a way we can do this?
Thanks in Advance
r937 — 2010-05-11T06:22:05-04:00 — #2
assuming "dw" is the column from your table with the numbers 1 through 7...
SELECT '2010-05-09' + INTERVAL dw DAY AS running_date
vigneshr35 — 2010-05-11T08:29:07-04:00 — #3
Thanks, It is working absolutely fine. What if I would like to extend the requirement like this:
With the query you provided above, this is what we can get
1 - 2010-05-10
2 - 2010-05-11
3 - 2010-05-12
4 - 2010-05-13
5 - 2010-05-14
6 - 2010-05-15
7 - 2010-05-16
What if I want to get the below shown by still having 2010-05-09 as the date I have at hand, and along with that I have
a value from 1-7 from where I want the date to start incrementing(3 in this case)
3 - 2010-05-10
4 - 2010-05-11
5 - 2010-05-12
6 - 2010-05-13
7 - 2010-05-14
1 - 2010-05-15
2 - 2010-05-16
r937 — 2010-05-11T11:23:10-04:00 — #4
i'm surprised you even had to ask...
SELECT '2010-05-09' + INTERVAL dw+1 DAY ...
vigneshr35 — 2010-05-12T01:59:22-04:00 — #5
New to such queries, so could not understand much. But, the change you said just adds the date given. I had to start with the same date, but corresponding to a value between 1-7 and wrap over the date increment.
Understood the basic query suggested by you, made some changes to get what i wanted.
SELECT '2010-05-09' + INTERVAL dw-(3-1) DAY AS running_date,dw
FROM test_table where dw >= '3'
SELECT '2010-05-09' + INTERVAL dw+(8-3) DAY AS running_date,dw
FROM test_table where dw < '3' ;
Here 3 can be anything between 1 and 7, which we have at hand.
Thanks Again. Problem Solved:)