Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite
1

How to display the time period of the most widely in use in ms sql server

I have purchase data, such as:

hours   the number of purchases 
10:00       2 
12:00       4 
13:00       5 
14:00       1

I want to show the period of time with most buyers. So the results would be with the most purchases hour period between 12:00 to 13:00.

Here is my query :

select  h.hours, day(d.dates)
from hours h, dates d
group by h.hours, day(d.dates)
having count(h.hours) >= all (select count(h2.hours) 
from hours h2, dates d2
group by h2.hours, day(d2.dates))

but the result is: 13:00

Can anyone help me?

share|improve this question
What is the DDL for the hours and dates tables? Why is 13:00 not the answer you're looking for? – Nick Chammas Jan 17 '12 at 1:44
1  
because i want to display period a clock that has the largest purchases (two of largest purchases numbers). in this case there are 12:00 --> 4 and 13:00 --> 5, so I want the result 12:00 - 13:00 – rujak Jan 17 '12 at 2:03
1  
I assume that every entry in your hours table represents the 1-hour period starting at that time. So, 12:00 represents the period starting at that time and ending just before it turns 13:00; 13:00 represents 13:00-14:00; and so on. So if you're looking for the one hour period with the most purchases then your answer is indeed 13:00 (which represents 13:00-14:00) since 5 purchases were made during that hour. – Nick Chammas Jan 17 '12 at 2:09
What, precisely, is the business logic here? – jcolebrand♦ Jan 17 '12 at 3:13
Failure to continue to communicate on poor questions means we can't help either. Closed because this is no good. If the OP will comment below his question, we will help him. – jcolebrand♦ Jan 18 '12 at 14:33

closed as off topic by Nick Chammas, jcolebrand♦ Jan 18 '12 at 14:33

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined in the FAQ. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about closed questions here.

Browse other questions tagged or ask your own question.