Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>If you want to count for the last 7 days and the last 30 days, you could use something like this:</p> <pre><code>-- Count of Participants in the last 7 days SELECT COUNT(DISTINCT Q.UserName) FROM dbo.UserQuiz Q WHERE DateTimeComplete &gt;= DATEADD(dd, -7, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) -- Count quizzes taken in the last 7 days SELECT COUNT(Q.QuizID) FROM dbo.UserQuiz Q WHERE Q.DateTimeComplete &gt;= DATEADD(dd, -7, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) -- Count quizzes taken in the last 30 days SELECT COUNT(Q.QuizID) FROM dbo.UserQuiz Q WHERE Q.DateTimeComplete &gt;= DATEADD(dd, -30, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) </code></pre> <p>Note that if you want to count for calendar weeks and calendar months then you need to do a bit more work to filter the proper starting and ending date filters.</p> <hr> <p><strong>EDIT:</strong> Fixed the query syntax (1st answer posted without running it in SSMS) also I didn't notice at first that the UserQuiz.UserQuizID is nullable. This column is therefore not what I would have expected it to be. </p> <hr> <p><strong>EDIT #2:</strong> As per OP's request, these three statistics can also be retrieved in a single query using sub-selects. Sub-selects are necessary because of differences in either the filter criteria or the aggregations involved in counting each statistic (or both).</p> <pre><code>-- Combine three stats into one query using sub-selects... SELECT (SELECT COUNT(DISTINCT Q.UserName) FROM dbo.UserQuiz Q WHERE DateTimeComplete &gt;= DATEADD(dd, -7, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))) as ParticipantsLast7Days, (SELECT COUNT(Q.QuizID) FROM dbo.UserQuiz Q WHERE Q.DateTimeComplete &gt;= DATEADD(dd, -7, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))) as QuizzesLast7Days, (SELECT COUNT(Q.QuizID) FROM dbo.UserQuiz Q WHERE Q.DateTimeComplete &gt;= DATEADD(dd, -30, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))) as QuizzesLast30Days </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POHow to come up with statistics that shows the total number of quizzes, total number of participants?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USJoel Brown
    1. USJoel Brown
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow to come up with statistics that shows the total number of quizzes, total number of participants?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks for your help. I really appreciate it. However, I tried your queries, but no one of them worked with me. Sorry, but don't get me wrong. I am still new to the Web Development. For instance, I tried your first query in SQLServer Management Studio and I got the following error: Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'SELECT'. Msg 102, Level 15, State 1, Line 3 Incorrect syntax near ')'.
      singulars
      1. PO
      1. USMohammed Al-Ali
    2. CO@MohammedAl-Ali - Sorry, my initial answer was done without the benefit of a test in SSMS. I have fixed the errors. The syntax is now correct.
      singulars
      1. PO
      1. USJoel Brown
    3. COIt is OK. I could be able to fix the syntax. Now, I am just worrying about the result, because I got 0 as a result of all queries. I don't know why. I am working on figuring out the problem. I have data in the database, but still I got zeros. Could you please help me with this issue?
      singulars
      1. PO
      1. USMohammed Al-Ali
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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