| John Thomas 2002-11-02, 8:23 pm |
| Select CEILING ( datepart(hh,timefield) * 60 + datepart
(mi,timefield) / 20), count(*)
From Table
Group BY CEILING ( datepart(hh,timefield) * 60 + datepart
(mi,timefield) / 20)
This is not tested as I dont have SQL Server now to check
it.
But you would have to group records to get the o/p
----
John
>-----Original Message-----
>Need help please, with getting a query to count the # of
times each
>record falls within a specific time range.
>
>I have table where 1 column contains a "TIME" field, with
data stored
>as: HH:MM. This represents the number of hours and
minutes it took a
>specific task to be accomplished.
>
>I need to write a query, that tells me how many of those
times fell
>within various time ranges. I don't need any group-bys,
and just need
>to count all the records in the table. The breakouts I
need
>are: Between 1 - 20 minutes, 21-40 minutes, 41-60
minutes, 61-80
>minutes, 81-100 minutes, and over 100 minutes. So, the
query should
>return something like this:
>
>Time Range # of Records % of Total
>1-20 Mins 342 33%
>21-40 Mins 155 28%
>41-60 Mins 244 24%
>61-80 Mins 155 15%
>
>I know how to calculate a "Percent of Total." And, I
know how to run a
>query, where the values would be displayed horizontally,
i.e.,
>1-20 Mins 21-40 Min 41-60 Min 61-80 Mins, etc...
> 342 155 244 155
>
>But, need the report to look like the first example.
Could someone give
>me an idea, of how to write the SQL, and format the
output, to get a
>report as shown in the first example? Thanks very much
for any/all
>help, advice.
>
>Gary
>
>.
>
|