<div>
If you're in SQL 2008 try 
 
&nbsp;
<pre><code class="code-10 nolinks" id="code-8170176">select 
 datename(dw,T.MY_DATETIME) as DayOfTheWeek,
 cast(T.MY_DATETIME as date) as myDate,
 datepart(hh,T.ACTLOG_DATETIME) as HourInterval
 count(T.USER_IDENTIFIER) as UserCount

from log_hist T

Where
 T.MY_DATETIME&gt;convert(datetime,'10/22/2009') AND T.MY_DATETIME&lt;convert(datetime, '11/30/2009')
group by
 cast(T.MY_DATETIME as date),
 datename(dw,T.MY_DATETIME), 
 datepart(hh,T.ACTLOG_DATETIME)
</code></pre>
 
If in SQL 2005 
 
&nbsp;
<pre><code class="code-10 nolinks" id="code-8170178">select 
 datename(dw,T.MY_DATETIME) as DayOfTheWeek,
 dateadd(d, dateadiff(d, 0, T.MY_DATETIME), 0) as myDate,
 datepart(hh,T.ACTLOG_DATETIME) as HourInterval
 count(T.USER_IDENTIFIER) as UserCount

from log_hist T

Where
 T.MY_DATETIME&gt;convert(datetime,'10/22/2009') AND T.MY_DATETIME&lt;convert(datetime, '11/30/2009')
group by
 dateadd(d, dateadiff(d, 0, T.MY_DATETIME), 0),
 datename(dw,T.MY_DATETIME), 
 datepart(hh,T.ACTLOG_DATETIME)
</code></pre>
</div>

<div>
Both queries seem &quot;close&quot; but the myDate value has lost the valid time and is showing 12:00. 

</div>

<div>
because you need to get rid of the time portion so that the grouping can be done by date only 
 
if you don't want the 12:00 to appear do 
 
...convert(varchar, dateadd(d, dateadiff(d, 0, T.MY_DATETIME), 0), 103) as myDate, .... 
 
..... 
group by convert(varchar, dateadd(d, dateadiff(d, 0, T.MY_DATETIME), 0), 103) .... 

</div>

<div>
Honestly i would like to show the date and time as is in the database field. 

</div>

<div>
Maybe this is what you're looking for then 
 

<pre><code class="code-10 nolinks" id="code-8170237">select 
 datename(dw,T.MY_DATETIME) as DayOfTheWeek,
 T.MY_DATETIME,
 datepart(hh,T.ACTLOG_DATETIME) as HourInterval,
 count(T.USER_IDENTIFIER) over (partition by cast(T.MY_DATETIME as date)) as UserCount

from log_hist T

Where
 T.MY_DATETIME&gt;convert(datetime,'10/22/2009') AND T.MY_DATETIME&lt;convert(datetime, '11/30/2009')
</code></pre>
</div>

<div>
sorry like this 

<pre><code class="code-10 nolinks" id="code-8170239">select 
 datename(dw,T.MY_DATETIME) as DayOfTheWeek,
 T.MY_DATETIME,
 datepart(hh,T.ACTLOG_DATETIME) as HourInterval,
 count(T.USER_IDENTIFIER) over (partition by dateadd(hh, datediff(hh, 0, T.MY_DATETIME as date), 0)) as UserCount

from log_hist T

Where
 T.MY_DATETIME&gt;convert(datetime,'10/22/2009') AND T.MY_DATETIME&lt;convert(datetime, '11/30/2009')
</code></pre>
</div>

<div>
and btw, you don't need the convert there in your where clause 

<pre><code class="code-10 nolinks" id="code-8170241">select 
 datename(dw,T.MY_DATETIME) as DayOfTheWeek,
 T.MY_DATETIME,
 datepart(hh,T.ACTLOG_DATETIME) as HourInterval,
 count(T.USER_IDENTIFIER) over (partition by dateadd(hh, datediff(hh, 0, T.MY_DATETIME as date), 0)) as UserCount

from log_hist T

Where
 T.MY_DATETIME&gt; '10/22/2009' AND T.MY_DATETIME &lt; '11/30/2009'
</code></pre>
</div>

<div>
Get's error: 
Incorrect syntax near the keyword 'as'. 
pointing to count(...) line. 
 
also, &nbsp;the user_identifier counts need to be unique within the &quot;hour&quot; interval being reported on. 
can you possible modify the three flavors of queries with this uniqueness and let me know about the error listed here? 
 
Thanks much. &nbsp;It really is close (I have a few questions though about the dateadd stuff but would rather look at the final solution first). 
 
Again, THANKS! 

</div>

<div>
oops, yeah line 5 above should be 
 
count(T.USER_IDENTIFIER) over (partition by &nbsp; dateadd(hh, datediff(hh, 0, T.MY_DATETIME), 0)) as UserCount 
 
try it and let me know 
 
I'm just using dateadd ... and datediff to group all the dates to the hour.
</div>

<div>
It now runs, but the resultset is off. &nbsp;There is no grouping though so ... 

</div>

<div>
&gt;&gt;There is no grouping though so ...&lt;&lt; 
Yes, because you cannot have both. If you want the grouping you need to decide which date to show, which in my opinion it makes sense my comment <a href="http:#a35779704" rel="ugc">http:#a35779704</a>&nbsp; with the adjustment in <a href="http:#a35780266" rel="ugc">http:#a35780266</a>. Remember that the hour is shown with the HoursInterval column. So, I'm not sure why you're saying that this is not what you expect. 
 
So basically if your data looks like this: 
 
DayOfWeek &nbsp;| &nbsp;Date &nbsp;| &nbsp; 
Monday | 03/05/2009 &nbsp;1:01 
Monday | 03/05/2009 &nbsp;1:21 
Monday | 03/05/2009 &nbsp;1:45 
Monday | 03/05/2009 &nbsp;2:03 
Monday | 03/05/2009 &nbsp;3:15 
Monday | 03/05/2009 &nbsp;4:02 
 
 
and you want 
 
DayOfWeek &nbsp;| &nbsp;Date &nbsp;| &nbsp;HourInterval &nbsp;| &nbsp;UserCount 
Monday | 03/05/2009 | &nbsp;1 &nbsp;| &nbsp;3 
Monday | 03/05/2009 | &nbsp;2 &nbsp;| &nbsp;1 
Monday | 03/05/2009 | &nbsp;3 &nbsp;| &nbsp;1 
Monday | 03/05/2009 | &nbsp;4 &nbsp;| &nbsp;1 
 
you definitively need to drop the time portion in the &quot;date&quot; column. otherwise there's nothing to group and you will end up with the same output as your input. 

</div>

<div>
Please note that when I say drop the time portion, I mean it for display purpose only, not from your table. The original data remains the same, it's just the select query adjusting it so you get the correct count. Here's the query again. Try it and let me know. 
 

<pre><code class="code-10 nolinks" id="code-8170307">select 
 datename(dw,T.MY_DATETIME) as DayOfTheWeek,
 convert(varchar, dateadd(d, dateadiff(d, 0, T.MY_DATETIME), 0), 103) as myDate,
 datepart(hh,T.ACTLOG_DATETIME) as HourInterval
 count(T.USER_IDENTIFIER) as UserCount

from log_hist T

Where
 T.MY_DATETIME&gt;convert(datetime,'10/22/2009') AND T.MY_DATETIME&lt;convert(datetime, '11/30/2009')
group by
 convert(varchar, dateadd(d, dateadiff(d, 0, T.MY_DATETIME), 0), 103),
 datename(dw,T.MY_DATETIME), 
 datepart(hh,T.ACTLOG_DATETIME)
</code></pre>
</div>

<div>
one more thing, I guess in your example t.My_Datetime and t.ACTLOG_DATETIME are the same column, right?
</div>

<div>
ok, try my last comment <a href="http:#a35781088" rel="ugc">http:#a35781088</a>&nbsp;and let me know.
</div>

<div>
It runs now. &nbsp;I am not sure if the count of users is unique per hour though. 
 
The MyDate field : &nbsp; convert(varchar, dateadd(d, datediff(d, 0, T.ACTLOG_DATETIME), 0), 103) as myDate, gives me the date in the format dd/mm/yyyy. &nbsp;Can you tell me the value to get the format mm/dd/yyyy?
</div>

<div>
Ok. sorry, the format I needed for the dates is &quot;101&quot;. &nbsp; &nbsp;But, &nbsp;I do think that the user id values per hour are more than once. &nbsp;The last piece of this is to only have one occurrence per hour per user in the usercount hour buckets. 

</div>

<div>
The users must repeat in this query as there are some rows with counts higher than the total number of users. 

</div>

<div>
I'm not following you, Could you post some sample data (input) and the expected result?
</div>

<div>
The table is an activity table for a web application. &nbsp;Every time a user goes to a page the access is logged with user id, &nbsp;datetime of access (multiple inserts per user per hour). &nbsp;I am trying to generate a resultset that represents how many users accessed the web site per hour. &nbsp;So, &nbsp;since all the user's accesses are written to this activity table, &nbsp;the user id will appear several times in a one hour window in this logging table. 
 
The resultset should answer the question: &quot;How many users access this web site per hour hour (not how many times per user which is what we have)&quot; &nbsp;for each hour of the day for a date range. 
 
 
It's kind of like we want the hourly bucket to contain the results of a select &quot;distinct&quot; user_identifier count. 

</div>

<div>
So, &nbsp;if user1 access the web site 5 times at 9:00pm today and user34 access the web site 12 times within the same 9:00PM &quot;bucket&quot; the output would be (assuming just these two accessed the site): 
 
DayOfWeek &nbsp;| &nbsp;Date &nbsp;| &nbsp;HourInterval &nbsp;| &nbsp;UserCount 
Tuesday | 05/17/2011 | &nbsp;21 &nbsp;| &nbsp;2 
 
 

</div>

<div>
Just right. &nbsp;I bow to the master. &nbsp;If you're ever in NYC... 
A minor note, &nbsp;you have a typo: &nbsp;the function datediff you typed as dateadiff (an extra a).
</div>

<div>
An extremely knowledgeable and patient SQL master.
</div>

<div>
<div class="content wysiwyg-content">
I have a table with a date and a unique user id. &nbsp;I would like to generate a resultset that gives me the count of user table inserts per hour. &nbsp;Here is desired output: 
DayOfWeek &nbsp;| &nbsp;Date &nbsp;| &nbsp;HourInterval &nbsp;| &nbsp;UserCount 
Monday | 03/05/2009 | &nbsp;1 &nbsp;| &nbsp;12 
Monday | 03/05/2009 | &nbsp;2 &nbsp;| &nbsp;32 
Monday | 03/05/2009 | &nbsp;3 &nbsp;| &nbsp;19 
Monday | 03/05/2009 | &nbsp;4 &nbsp;| &nbsp;72 
Monday | 03/05/2009 | &nbsp;5 &nbsp;| &nbsp;4 
Monday | 03/05/2009 | &nbsp;6 &nbsp;| &nbsp;11 
... 
 
 
 

<pre><code class="code-10 nolinks" id="code-8170161">select 
 datename(dw,T.MY_DATETIME) as DayOfTheWeek,
 T.MY_DATETIME,
 datepart(hh,T.ACTLOG_DATETIME) as HourInterval
 count(T.USER_IDENTIFIER) as UserCount

from log_hist T

Where
 T.MY_DATETIME&gt;convert(datetime,'10/22/2009') AND T.MY_DATETIME&lt;convert(datetime, '11/30/2009')
group by
 T.MY_DATETIME,
 datename(dw,T.MY_DATETIME), 
 datepart(hh,T.ACTLOG_DATETIME)
</code></pre>
</div>
</div>

I have a table with a date and a unique user id.  I would like to generate a resultset that gives me the count of user table inserts per hour.  Here is desired output:
DayOfWeek  |  Date  |  HourInterval  |  UserCount
Monday | 03/05/2009 |  1  |  12
Monday | 03/05/2009 |  2  |  32
Monday | 03/05/2009 |  3  |  19
Monday | 03/05/2009 |  4  |  72
Monday | 03/05/2009 |  5  |  4
Monday | 03/05/2009 |  6  |  11
...




t-sql aggregate problem

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Microsoft SQL Server 2005

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.