timestamp - Count of Occurrences from the transaction DateTime + 1 Hour in SQL

Question

I have the Dataset Like

Transaction Time      A     B    C      Expected Output
3/17/2020 14:42      India  1   10        2
3/17/2020 15:09      India  1   10        0
3/17/2020 15:48      India  1   10        4
3/17/2020 15:59      India  1   10        0
3/17/2020 16:13      India  1   10        0
3/17/2020 16:36      India  1   10        0
3/17/2020 17:02      India  1   10        1
3/17/2020 18:42      India  1   10        5
3/17/2020 18:55      India  1   10        0
3/17/2020 19:05      India  1   10        0
3/17/2020 19:24      India  1   10        0
3/17/2020 19:30      India  1   10        0
3/17/2020 20:01      India  1   10        1
3/17/2020 21:24      India  1   10        3
3/17/2020 21:26      India  1   10        0
3/17/2020 21:48      India  1   10        0

The Dataset is based on the transaction Datetime with some of the dimensions associated to it.

The first transaction occured on: 3/17/2020 14:42 , I want to know how many such transactions are available between the first transaction date and next 1 hour interval. So In the data we can see the first and second record satisfies this criteria so the last column "Expected Output" as 2 in the first row and 0 in the 2nd row.

again the counter will start from the third records : 3/17/2020 15:48 so for this record we can see there are total of 4 such transactions which falls between and current datetime + 1 hour interval so the 3 row is set as 4 and the row 4,5,6 is set as 0 and so on.

Requesting your help on the same.

question from:https://stackoverflow.com/questions/66049707/count-of-occurrences-from-the-transaction-datetime-1-hour-in-sql

Answer 1

Waitting for answers