StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>A simple way to group the records by date in Server 2008+ above is cast the <code>datetime</code> column to a <code>date</code>. Then use simply use the "CommentDate" column as the chart item and "NumOfComments" for the value:<br><br></p> <pre><code>SELECT CONVERT(DATE, tbComment.CreateDateTime) AS CommentDate , COUNT(*) NumOfComments FROM tbPerson INNER JOIN tbComment ON tbPerson.PersonID = tbComment.PersonID GROUP BY CONVERT(DATE, tbComment.CreateDateTime); </code></pre> <p><br> You can make the chart labels a little more user friendly by formatting the date within the SQL (see <a href="http://msdn.microsoft.com/en-us/library/ms187928.aspx" rel="nofollow">cast and convert</a>). Just be sure to order the results by the <code>date</code> value, and not the formatted <em>string</em>. Otherwise, the chart labels may not appear in proper chronological order (it is a common mistake):</p> <pre><code>--- format as: Mon dd, yyyy SELECT CONVERT(VARCHAR, tbComment.CreateDateTime, 107) AS CommentDateString , COUNT(*) NumOfComments FROM tbPerson INNER JOIN tbComment ON tbPerson.PersonID = tbComment.PersonID GROUP BY CONVERT(VARCHAR, tbComment.CreateDateTime, 107) ORDER BY CONVERT(DATE, CONVERT(VARCHAR, tbComment.CreateDateTime, 107), 107) </code></pre> <p><a href="http://sqlfiddle.com/#!6/77973/13" rel="nofollow"><strong>SQLFiddle</strong></a></p> <p>As an aside, if HTML charts are an option, you might look into Scott's suggestion. Generally <code>CFChart</code> is fine for <em>simple</em> cases, but once you start to customize the appearance (which invariably happens), you are usually better off using the underlying tools directly.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload