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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-01-14 : 07:56:40
|
| Karthik Kannan writes "Hi,I have the following date;Name Time In------------------abc 07.30bcd 10.45def 14.25fgh 07.16Now the report that is required is of the following format;---------------------------------Entry Time: 07:00 to 07:301. abc 07.302. fgh 07.16Entry Time: 10:31 to 11:001. bcd 10:45Entry Time: 14.00 to 14.301. def 14.25---------------------------------As you can see, the report has to be in half hour splits.I found a possible way to do it which is;1. Write a User Defined Function (UDF).2. Have hardcode values like Between 00:00 to 00:30 = 1 Between 00:31 to 01:00 = 2 .....etc3. Thus, when we pass the time to the UDF, it would return the time slot.4. All records will have this field added in SQL and it would allow sorting and filtering the data according to the time slot.What I would like to check if there is a better way of doing it instead of hardcoding all the values.Thanks." |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2004-01-14 : 10:27:25
|
| Well, there are 48 half-hour increments in 24 hours ... or more generally, 1440/{duration in minutes} increments in 24 hours. The label you display for the time span is (midnight + increment*duration + 1) to (midnight+(increment+1)*duration). So your UDF doesn't need to hardcode anything.JonathanGaming will never be the same |
 |
|
|
|
|
|
Time Split Report