June 23, 2011 at 8:19 pm
Ok, I know my Subject line isn't great, but I'm not sure exactly what I am looking for.
I'll give a simplified version of what I am looking to do.
Say I have a table called MyData that has a Name and a Date like
Susan 1-1-2009
Bobby 9-5-2008
And I want to calculate additional records based on that table, so that in another table or another view or something I see the Name and the Date incrementing by a year but not past the present date. So today, on 6-23-2011 it'd look like
Susan 1-1-2009
Susan 1-1-2010
Susan 1-1-2011
Bobby 9-5-2008
Bobby 9-5-2009
Bobby 9-5-2010
I know I could have a table where I insert each line as a new record, but I am looking for something less space consuming and more dynamic. So that, without having to do anything, if I open it on 11-8-2011 I'll see
Susan 1-1-2009
Susan 1-1-2010
Susan 1-1-2011
Bobby 9-5-2008
Bobby 9-5-2009
Bobby 9-5-2010
Bobby 9-5-2011
It seems to me like there must be a good way to do this in SQL, but I'm still new enough to it that I could really use some guidance.
Can anyone point me in the right direction here?
June 24, 2011 at 2:03 am
You must use DATEADD (datepart , number , date ) function to increment your Date.
See below what i had done.
select getdate()
Select Dateadd(yy,1,getdate())
Create table DateData(EName varchar(20), TDate Datetime)
insert into DateData values('Prashant',getdate())
insert into DateData values('Sagar',getdate()-1)
insert into DateData values('Vaibhav',getdate()-2)
Select EName,Tdate,DateAdd(yy,1,Tdate) as IncrementDate from DateData
June 24, 2011 at 2:30 am
use madworks
go
declare @table table
(
name varchar(10),
ddatetime datetime
)
insert into @table
select 'Bobby', GETDATE()-365
union
select 'Mary' , GETDATE() - 512
;
select * from @table
;
With CTE as (
select name , ddatetime from @table
union All
Select name,DATEADD(yy,1,ddatetime) from cte c
where DATEDIFF(yy,ddatetime,getdate()) >0
)
select * from cte
order by name
June 24, 2011 at 10:37 am
Jayanth_Kurup:
It looks like common table expressions may work for me.
I've been playing around with it, but could use a little more help if you don't mind.
So, I'm basing this off of a table named TEST that has
name ddatetime
Bobby 1/1/2009
Mary 6/30/2008
And I run this code:
;
With CTE as (
select name , ddatetime from TEST
union All
Select name, DATEADD(yy,1,ddatetime) from cte c
where DATEDIFF(yy,ddatetime,getdate()) >0
)
select * from cte
order by name
And the results are
Bobby 2009-01-01 00:00:00.000
Bobby 2010-01-01 00:00:00.000
Bobby 2011-01-01 00:00:00.000
Mary 2008-06-30 00:00:00.000
Mary 2009-06-30 00:00:00.000
Mary 2010-06-30 00:00:00.000
Mary 2011-06-30 00:00:00.000
This is close to what I am looking for, but I don't want ddatetime to go past the current date, so I don't want that last line
Mary 2011-06-30 00:00:00.000
because we haven't gotten to 6/30/2011 yet.
June 24, 2011 at 12:50 pm
June 24, 2011 at 1:04 pm
I've been trying to play with that, but I am unsuccessful.
I want to add this, right?
(DATEDIFF(dd,ddatetime,getdate()) > 0)
Where do I add that? We're not talking about the outside select statement, right?
It should be somewhere in the CTE definition, correct?
June 24, 2011 at 6:28 pm
Nevermind that last post. I got what I need.
Thanks for your help!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply