Distinct Count of Dates In 1 Hour Range

[Anonymous]

Hello,

I'm having some issues getting a Distinct Count of dates with some dates that differ by a few minutes each way.

I have 2 tables:

Status - StatusID

Results - ResultID, StatusID, CreatedOn

One status has many results.

I might have a status with ID: 1 and 5 results containing StatusID 5.

I was doing a DISTINCTCOUNT on CreatedOn to get all of the results grouped by date. However, the dates can differ by a few minutes each way, so I might have the following:

6/15/2018 6:45:02 PM

6/15/2018 6:43:02 PM

6/15/2018 6:48:05 PM

6/15/2018 6:41:02 PM

A DistinctCount on these rows would bring back 4. However, what I want is a filter to count records that are within a 1 hour range (30 minutes each side) as one. So this would bring back a result of 1 instead of 4.

I tried through the Data model to round the hour up/down but then there would be issues of times close to the hour. I have a feeling I might need to do COUNTROWS with some condition but I'm struggling to see how to compare values to the previous one?

Any help would be greatly appreciated.

Thanks,

Chris

[Greg_Deckler]

Soooo...how exactly do you want the grouping to occur? Do you want everything in HOUR 6 to be grouped together or does hour 6 need to get everything from 5:30 - 6:30?