Generate Dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Generate Dates

Author

Topic

Divya.birla
Starting Member

3 Posts

Posted - 2011-03-29 : 06:34:20

I have a table which contain Topic, Sequence number and no of turns.
User input the Single Start date and on the basis of which
series of start date and end date is calculated.

Let start date= 1 Jan ,2011
then

Topic | Sequence number |NoOfTurns | Start date | end date
......................................................................
Theorem | 1 | 5 | 1 jan,2011 | 6 jan,2011
circle | 2 | 4 | 7 jan,2011 | 11 jan,2011
triangle | 3 | 4 | 11 jan,2011 | 15 jan,2011

Start date is the End date of upper row and End date = Startdate + no of turns as days.

Please help me to get start date and end date sequence

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-29 : 07:20:37

In your example, in the second row, the start date is ONE PLUS the end date on the first row. But in your third row, the start date is the SAME as the end date on the previous row. Is that a typo, or is there a business rule that dictates whether to add one or not?

Assuming it was a typo, if you are using SQL 2005 or higher, you can do this using a recursive CTE like this:

declare @startDate datetime; set @startDate = '20110101';

with cte as
(
   select
      t.SeqNumber,
      t.NoOfTurns,
      @startDate as StartDate,
      dateadd(dd,t.NoOfTurns,@startDate) as EndDate
   from
      YourTable t
   where
      t.SeqNumber = 1
   union all
   select
      t.SeqNumber,
      t.NoOfTurns,
      dateadd(dd,0,c.EndDate) as StartDate, -- change the 0 to 1 for 1+ the previous end date.
      dateadd(dd,0+t.NoOfTurns,c.EndDate) as EndDate -- change the 0 to 1 for 1+ the previous end date.
   from
      YourTable t
      inner join cte c on t.SeqNumber = c.seqNumber + 1
)
select * from cte;

Divya.birla
Starting Member

3 Posts

Posted - 2011-03-29 : 07:57:08

thanx sunitabeck for reply

the start date is always ONE PLUS the end date of the previous row

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-29 : 08:49:12

Then, change these two lines


      dateadd(dd,0,c.EndDate) as StartDate, -- change the 0 to 1 for 1+ the previous end date.
      dateadd(dd,0+t.NoOfTurns,c.EndDate) as EndDate -- change the 0 to 1 for 1+ the previous end date.


      dateadd(dd,1,c.EndDate) as StartDate, -- change the 0 to 1 for 1+ the previous end date.
      dateadd(dd,1+t.NoOfTurns,c.EndDate) as EndDate -- change the 0 to 1 for 1+ the previous end date.

Divya.birla
Starting Member

3 Posts

Posted - 2011-03-29 : 08:55:34

thanx sunitabeck
You help me a lot...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources