topic Counting datetimes in App Development

Counting datetimes

Anonymous — Tue, 10 Oct 2017 18:20:01 GMT

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.

Re: Counting datetimes

sunny_talwar — Tue, 10 Oct 2017 18:26:19 GMT

May be like this

Count(DateTime)

and

Count(DISTINCT Floor(DateTime))

Re: Counting datetimes

Anonymous — Tue, 10 Oct 2017 20:13:11 GMT

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.

Re: Counting datetimes

sunny_talwar — Tue, 10 Oct 2017 20:59:12 GMT

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.