Add a row for every day that passed? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Add a row for every day that passed?

Author

Topic

edyl
Starting Member

35 Posts

Posted - 2012-11-16 : 09:34:36

Hello Everyone,

I am trying to add rows based on the Start Date/Time and Stop Date/Time into a table but not sure how to do it. Lets say, my original table has the data as follows:


CycleID...........CycleCode...........StartDate..........StartTime...........StopDate..........StopTime
1................0021.................11/01/2012.........11:21...............11/01/2012........13:11
2................0021.................11/02/2012.........09:15...............11/04/2012........08:00

As you can see the cycles may start and end on the same day (First row). But they can also go on for days (second row). In my new table, I am trying insert a row for each day that has passed but also adjust the Start/Stop time as follows:


CycleID...........CycleCode...........DateOfOvservation......StartDate..........StartTime...........StopDate..........StopTime
1................0021.................11/01/2012.............11/01/2012.........11:21...............11/01/2012........13:11
2................0021.................11/02/2012.............11/02/2012.........09:15...............11/04/2012........23:59
2................0021.................11/03/2012.............11/02/2012.........00:00...............11/04/2012........23:59
2................0021.................11/04/2012.............11/02/2012.........00:00...............11/04/2012........08:00

How can I achieve this? Any suggestions and recommendations greatly appreciated.

Thanks in Advance.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-11-16 : 10:26:31

A bit odd - are you sure you don't want the date of observtion to be fixed and the satrt and end dates to reflects the record dates?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-11-16 : 10:32:01

;with cte1 as
(
select dstrt = min(startdate), dend = maxIstopdate)+1 from tbl
)
, cte as (
select d = dstrt from cte1
union all
select d = d + 1 from cte where d < (select dend from cte1)
)
select CycleID, CycleCode, DateOfObservation = t.StartDate, StartDate = c.d, SartTime = case when c.d = t.StartDate then d.StrtTime else '00:0:00' end ,
StopDate = c.d, SopTime = case when c.d = t.StopDate then d.StopTime else '23:59:59' end
from tbl t
join cte c
on c.d between t.startdate and d.stopdate

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2012-11-16 : 18:26:31

Nigel,
Take a look at the following article and see why you might want to avoid rCTEs that count even for such small counts.
[url]http://www.sqlservercentral.com/articles/T-SQL/74118/[/url]

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources