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.
| Author |
Topic |
|
mavericky
Posting Yak Master
117 Posts |
 Posted - 2011-09-22 : 20:03:53
|
| Hi All,I have 3 columns:Start DateEnd DateHourIntervalThese are some of the values of StartDate and EndDate. HourInterval is indicated beside it.2011-01-05 00:00:00 , 2011-01-05 00:15:00 – Interval 12011-01-05 00:15:00 , 2011-01-05 00:30:00 – Interval 22011-01-05 00:30:00 , 2011-01-05 00:45:00 – Interval 32011-01-05 00:45:00 , 2011-01-05 01:00:00 – Interval 4Can there be a specific formula in sql to calculate HourInterval values based on the above data?Thanks,Mavericky |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-09-22 : 20:20:48
|
select datediff(hour, StartDate, EndDate)
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
mavericky
Posting Yak Master
117 Posts |
Posted - 2011-09-23 : 09:41:48
|
| Thanks for the reply but this does not give correct answer. From the example above, there are 15 mins intervals when the two dates are considered. For the StartDate and EndDate, I want to retrieve value as 1 if it is between 0(min field in StartDate) and 15(min field in EndDate) mins, 2 if between 15 and 30 mins, 3 if between 30 and 45 mins, and 4 if between 45 and next hour. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-09-23 : 11:39:28
|
quote:
Originally posted by mavericky
Thanks for the reply but this does not give correct answer. From the example above, there are 15 mins intervals when the two dates are considered. For the StartDate and EndDate, I want to retrieve value as 1 if it is between 0(min field in StartDate) and 15(min field in EndDate) mins, 2 if between 15 and 30 mins, 3 if between 30 and 45 mins, and 4 if between 45 and next hour.
What is IT, in you statement above?Are you trying to join something to these intervals or what? May be a sample with expeced output would help.It's also nice to provide DDL, DML and expected output with your questions. Here is a like that can help you prepare that, so we can help you better:http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-09-24 : 00:27:59
|
[code]case when datediff(hour, StartDate, EndDate) between 0 and 15 then 1 when datediff(hour, StartDate, EndDate) between 16 and 30 then 2. . . . end[/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
ttruheyo
Starting Member
9 Posts |
Posted - 2011-09-26 : 14:52:19
|
| This is great I have a question that relates to this I have3 a column in one table that is a date field. This date field is the key to all the data I need to pull for instance I need to the count of column a when the datefield is Getdate()+5, 10, 30, 45 etc.. Right now I have the same statement with the where clause changed each time. Using the above scenario I assume I can do something. However, everything I have tried has failed. Ideas. I am looking for something like:select ColumnA, count(FinishDate >= getdate()+30) as 'Due 30+', count(FinishDate <= getdate()-1)as 'Past Due' from MyTableB group by ColumnANow I am well aware that this select statement is not valid but it is the best way I could think of to show what data I am loking for.NotTheDBA - But playing one. Tom |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|
|
Calculate value from dates