Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am working on a project where employees submit documents. When they do so, I receive a datetime that includes the day and time they submitted their work. I need to count the number of datetime entries in my data to establish the number of documents submitted. Furthermore, I need to count the number of unique days in which documents were submitted by employees (they must submit at least one document for it to count as a day worked).
As an example, if I had the following dataset for an employee:
10/10/2017 1:00 PM
10/10/2017 12:57 PM
10/09/2017 1:07 PM
10/06/2017 11:34 AM
10/04/2017 9:02 AM
Then I need to know that there are 5 records for this employee and 4 unique days worked.
May be like this
Count(DateTime)
and
Count(DISTINCT Floor(DateTime))
Count(DateTime) seems to work.
When I try Count(DISTINCT Floor(DateTime)), the numbers seem very low. With this method, the first employee returns 7 for submitted documents. When I run this query in SQL, 50 hits are returned. When I manually validate the number, 50 is correct.
So you are saying that you expect 50 unique dates.... whereas it is showing only 7? May be check what those dates are using Concat
Concat(DISTINCT DayName(DateTime), ', ')
Once you have this, you might be able to troubleshoot why the other dates are not showing up.