Skip to main content
Database Administrators

Return to Question

Should be 5 not 6 occurrences!
Source Link
James Deadman
James Deadman
  • 121
  • 2

I am trying to think of a way of grouping some dates to make a count if they are more then 5 days apart.

For Example:

ID - DATE
22   15/05/2016 08:57
22   15/05/2016 10:50
22   15/05/2016 12:01
22   16/05/2016 01:12
22   22/05/2016 02:25

So for this I would currently get a count of 65 as there are 65 occurrences however I want to only get a count for 2 due to the fact that the first 4 have sequential dates and the final one is 5 days after the previous date so would need that counted as a new one.

Hopefully that makes sense!

I am trying to think of a way of grouping some dates to make a count if they are more then 5 days apart.

For Example:

ID - DATE
22   15/05/2016 08:57
22   15/05/2016 10:50
22   15/05/2016 12:01
22   16/05/2016 01:12
22   22/05/2016 02:25

So for this I would currently get a count of 6 as there are 6 occurrences however I want to only get a count for 2 due to the fact that the first 4 have sequential dates and the final one is 5 days after the previous date so would need that counted as a new one.

Hopefully that makes sense!

I am trying to think of a way of grouping some dates to make a count if they are more then 5 days apart.

For Example:

ID - DATE
22   15/05/2016 08:57
22   15/05/2016 10:50
22   15/05/2016 12:01
22   16/05/2016 01:12
22   22/05/2016 02:25

So for this I would currently get a count of 5 as there are 5 occurrences however I want to only get a count for 2 due to the fact that the first 4 have sequential dates and the final one is 5 days after the previous date so would need that counted as a new one.

Hopefully that makes sense!

Data better visible
Source Link
Marco
Marco
  • 3.7k
  • 5
  • 24
  • 31

I am trying to think of a way of grouping some dates to make a count if they are more then 5 days apart.

For Example:

ID - DATE
22 15/05/2016 08:57
22 15/05/2016 10:50
22 15/05/2016 12:01
22 16/05/2016 01:12
22 22/05/2016 02:25

ID - DATE
22   15/05/2016 08:57
22   15/05/2016 10:50
22   15/05/2016 12:01
22   16/05/2016 01:12
22   22/05/2016 02:25

So for this I would currently get a count of 6 as there are 6 occurrences however I want to only get a count for 2 due to the fact that the first 4 have sequential dates and the final one is 5 days after the previous date so would need that counted as a new one.

Hopefully that makes sense!

I am trying to think of a way of grouping some dates to make a count if they are more then 5 days apart.

For Example:

ID - DATE
22 15/05/2016 08:57
22 15/05/2016 10:50
22 15/05/2016 12:01
22 16/05/2016 01:12
22 22/05/2016 02:25

So for this I would currently get a count of 6 as there are 6 occurrences however I want to only get a count for 2 due to the fact that the first 4 have sequential dates and the final one is 5 days after the previous date so would need that counted as a new one.

Hopefully that makes sense!

I am trying to think of a way of grouping some dates to make a count if they are more then 5 days apart.

For Example:

ID - DATE
22   15/05/2016 08:57
22   15/05/2016 10:50
22   15/05/2016 12:01
22   16/05/2016 01:12
22   22/05/2016 02:25

So for this I would currently get a count of 6 as there are 6 occurrences however I want to only get a count for 2 due to the fact that the first 4 have sequential dates and the final one is 5 days after the previous date so would need that counted as a new one.

Hopefully that makes sense!

Source Link
James Deadman
James Deadman
  • 121
  • 2

Grouping date's as 1 count if difference is 5 days

I am trying to think of a way of grouping some dates to make a count if they are more then 5 days apart.

For Example:

ID - DATE
22 15/05/2016 08:57
22 15/05/2016 10:50
22 15/05/2016 12:01
22 16/05/2016 01:12
22 22/05/2016 02:25

So for this I would currently get a count of 6 as there are 6 occurrences however I want to only get a count for 2 due to the fact that the first 4 have sequential dates and the final one is 5 days after the previous date so would need that counted as a new one.

Hopefully that makes sense!