Sunday, March 25, 2012

Date problem

Hi,
Can anybody help me to find an easy solution to this problem?
I have a table

CREATE TABLE T1 (
Col1 VARCHAR(20)
, Col2 VARCHAR(20)
, Col3 VARCHAR(20)
, Col4 DATETIME
, Col5 INT )

INSERT INTO T1 VALUES ('A01','B01','C01',23-03-2006,4)

I want to pass a parameter to a stored proc such as Col1 ('A01'),and it will
check value of Col5, which is 4 here in our data.And then it will generate a resultset by adding 1+ to the month of Col4.

And I want to get a resultset like

A01 B01 C01 23-03-2006
A01 B01 C01 23-04-2006
A01 B01 C01 23-05-2006
A01 B01 C01 23-06-2006

It will also check if the date is 25-12-2006 the next date would be 25-01-2007 and also if 29-01-2006 the next date would be 28-02-2006.
I am trying to avoid Cursor.
Any solution would be really appreciated.
Thanks!!create an integers table like this --create table integers (i integer not null primary key)
insert into integers (i) values (0)
insert into integers (i) values (1)
insert into integers (i) values (2)
insert into integers (i) values (3)
insert into integers (i) values (4)
insert into integers (i) values (5)
insert into integers (i) values (6)
insert into integers (i) values (7)
insert into integers (i) values (8)
insert into integers (i) values (9) then in the stored proc, run this query --select Col1
, Col2
, Col3
, dateadd(mm,i,Col4) as Col4
from integers
cross
join T1
where i < Col5|||You are one of the smartest guy I ever seen.
Rudy ,thanks a ton.;)|||thanks for the kind words

but there are a half dozen guys in this very forum smarter than me ;)|||create an integers table like this --create table integers (i integer not null primary key)
insert into integers (i) values (0)
insert into integers (i) values (1)
insert into integers (i) values (2)
insert into integers (i) values (3)
insert into integers (i) values (4)
insert into integers (i) values (5)
insert into integers (i) values (6)
insert into integers (i) values (7)
insert into integers (i) values (8)
insert into integers (i) values (9) Just an FYI - if you want a BIG integers table (and one day you will :) ) this is a nice function to create one:
http://sqljunkies.com/WebLog/amachanic/articles/NumbersTable.aspx|||Just an FYI - if you want a BIG integers table (and one day you will :) ) this is a nice function to create one:
http://sqljunkies.com/WebLog/amachanic/articles/NumbersTable.aspx

Thanks for the link Pootie;)

No comments:

Post a Comment