May 12, 2010 at 2:48 am
I am needing to get parameters for default ranges as wells as user selected ranges from 2 fields in my report - by hours and minutes (Maximum of 360 Minutes). The default ranges I need to get are as follows:
1st Range - 0 -1Hrs
2nd Range - 1 - 3Hrs
3rd Range - > 6
Does anyone know how I would be able to get these ranges?
May 12, 2010 at 2:54 am
You need to be more specific about the requirements. It is unclear if you want it according to the hours or according to clock (i.e. 2:30 – 2:45 is in the second range because it was between the hours 2:00 and 3:00 or in the first group because the amount of time in this time frame is less then 1 hour). Another thing that you should notice is that you don’t have clear borders for your range. The highest border in your second range is 3 and the lower border in your last range is 6. How are you going to handle values that will be between those borders?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 12, 2010 at 3:01 am
Thank you for responding - I actually missed one of the ranges - which is 3 -6hrs. The ranges can be set in minutes or hours. Additional Ranges can be added or deleted. I am looking at the time in hours and/or minutes (to determine the amount of time between two datetime fields)
May 12, 2010 at 3:25 am
If you are looking for the length of time that something took, then you can use the datediff function to calculate the period of time. According to it you can decide which range should be used. Here is a small demo to show how to do it:
declare @t table (StartTime datetime, EndTime datetime)
insert into @t (StartTime, EndTime)
select '01:59:00', '02:01:00'
union select '02:40:00', '05:39:00'
union select '01:59:00', '07:01:00'
select StartTime, EndTime,
case when datediff(mi,StartTime, EndTime) <= 60 then 1
when datediff(mi,StartTime, EndTime) > 60 and datediff(mi,StartTime, EndTime) < =180 then 2
when datediff(mi,StartTime, EndTime) > 180 and datediff(mi,StartTime, EndTime) < =360 then 3
else 4
end as RangeNum
from @t
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 12, 2010 at 3:33 am
Thank you so much - I will try this. 🙂
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply