SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Add a row for every day that passed?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

edyl
Starting Member

32 Posts

Posted - 11/16/2012 :  09:34:36  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 11/16/2012 :  10:26:31  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 11/16/2012 :  10:32:01  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
;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.

Edited by - nigelrivett on 11/16/2012 10:32:50
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 11/16/2012 :  18:26:31  Show Profile  Reply with Quote
Nigel,
Take a look at the following article and see why you might want to avoid rCTEs that count even for such small counts.
http://www.sqlservercentral.com/articles/T-SQL/74118/



--Jeff Moden
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000