DateTime Loop/Iteration Possibility

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

SQL Server 2005 Forums

Transact-SQL (2005)

DateTime Loop/Iteration Possibility

Author

Topic

jndyer0372
Starting Member

4 Posts

Posted - 2010-06-08 : 11:51:31

I have a table that uses datetime as the update date and I need to pull data for a month/week period at the end of the month or week that contains counts for the time periods, e.g. 5/1/2010 17:00:00 - 5/2/2010 16:59:59, 5/2/2010 17:00:00 - 5/3/2010 16:59:59, etc. for the month and same for week.

I have a query that pulls the counts based on date only but this does not correspond to actual shift times mentioned above.

Thanks in advance for any input.

Jennifer

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-08 : 13:24:50

Something like this?

declare @startMonth datetime
declare @endmonth datetime

set @startmonth = '5/1/2010'

set @endmonth = dateadd(day,datediff(day,0,@startmonth),'19000201 16:59:59')

select dateadd(day,datediff(day,0,@startmonth),'19000101 17:00:00'),@endMonth

Jim

N.B. - I am on MDY format

Everyday I learn something that somebody else already knew

jndyer0372
Starting Member

4 Posts

Posted - 2010-06-08 : 14:24:46

Thanks, this piece is helpful.

However this gives me the start date and end date for my query but how can I use this to iterate through all the datetimes of the month to show counts for each day period (consisting of only the actual shift time)?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-08 : 15:26:28

Without seeing your query and and knowing exactly what want, this is the best I can do. Remember, I don't know your data or your business needs beyond what you've described.


declare @startMonth datetime
declare @endmonth datetime

set @startmonth = '5/1/2010 17:00:00' 

set @endmonth = dateadd(day,datediff(day,0,@startmonth),'19000201 16:59:59')

select   dateadd(month,number,@startmonth)
        ,dateadd(second,-1,dateadd(month,number + 1,@startmonth))
from master..spt_values spt
where spt.[type] = 'P'

Everyday I learn something that somebody else already knew

jndyer0372
Starting Member

4 Posts

Posted - 2010-06-08 : 16:29:55

Absolutely true - I appreciate your help so let me give you some background.

I have a table containing CreateDate as datetime that represents the date and time a thread was created for processing. These can be created after the shift is over through to the next day before and during the shift, hence the 17:00:00 - 16:59:59 representation.

At the same time the team can be processing said threads but may not be able to get to all therefore leaving a variance which is quite possible especially if a thread is created close to shift end.

So when I try to run a query to get end of month results I would need to use timeframes in the CreateDate piece in order to accurately represent what was actually sent into the system for processing for that shift. My problem is that if I query the system for such a time period the getdate() will correspond to 0:00:00 and 11:59:59 which would not show me the true count of threads entered for processing on say 5/3/10 and then 5/4/10, etc if I query on 6/1/10 for May's data.

Thanks for your input.

jndyer0372
Starting Member

4 Posts

Posted - 2010-06-08 : 17:25:43

A little info on what I'm pulling from the db:

CreateDate ModifyDate ThreadCnt
5/3/2010 5/3/2010 98
5/4/2010 5/4/2010 153
.
.
.
where CreateDate will represent the date range of 5/2/2010 17:00:00 to 5/3/2010 16:59:59 and so on

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources