SQL about unavailbility period

Question

SQL about unavailbility period

Rico Yung 1

Table A_1:

gmt_create, unique_id, transaction_type

2021-11-21 01:23:34 10101 DEPOSIT
2021-11-21 01:23:34 10102 DEPOSIT
2021-11-21 01:23:35 10105 DEPOSIT
2021-11-21 01:23:35 10121 CLEARING
2021-11-21 01:23:35 10401 DEPOSIT
2021-11-21 01:23:35 11101 PAYOUT
2021-11-21 01:23:36 11701 DEPOSIT
................... ..... .......

How to write SQL to measure "Unavailability Period " from the table above?

Unavailability Period means a period of time commencing from the first minute where a defect, Incident or error of Partner’s System caused the actual Transaction volume processed by Partner’s System to be less than seventy percent (70%) of the Recent Average Transaction Volume continuously for ten (10) minutes or more, and ends on the first minute where the actual Transaction volume processed by Partner’s System exceeds seventy percent (70%) of the Recent Average Transaction Volume.

Recent Average Transaction Volume means the highest of the average number of Transactions per second

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2021-11-29T22:35:21.2+00:00

I am sorry, but I am not able to see any connection between the sample data you and your description. You talk about transaction volumes and unavailability, but I cannot recognize anything of that in the data.

Generally, when asking for query help, we recommend that you post the CREATE TABLE statement for your table(s) and your sample data as INSERT statements. This permits us to easily copy and paste into a query window to develop a tested solution. We also need to see the expected results given the sample, so that we can verify our solution.
EchoLiu-MSFT 14,626 Reputation points

2021-11-30T01:34:34.08+00:00

Hi @Rico Yung ,

Welcome to the microsoft TSQL Q&A forum!

Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)
along with your expected result? So that we’ll get a right direction and make some test.

Regards
Echo