SQL: Generate event sequence numbers within a specified time interval
A certain table in the MS SQL database has three fields: account, date and time of string type.
Account_Number |
FuelPurchase_Date |
Fuel_TOD |
19 |
2024-04-03 |
07:02:02 AM |
19 |
2024-04-03 |
07:02:41 AM |
19 |
2024-04-03 |
02:58:49 PM |
19 |
2024-04-03 |
07:58:49 PM |
19 |
2024-04-05 |
02:58:49 PM |
19 |
2024-04-05 |
02:59:31 PM |
19 |
2024-04-17 |
11:56:13 PM |
20 |
2024-04-17 |
11:59:13 PM |
19 |
2024-04-18 |
12:15:13 AM |
19 |
2024-04-18 |
02:56:13 PM |
20 |
2024-04-18 |
07:41:55 AM |
20 |
2024-04-18 |
07:41:55 PM |
20 |
2024-04-18 |
07:56:55 PM |
19 |
2024-04-19 |
07:41:55 AM |
19 |
2024-04-19 |
07:42:20 AM |
19 |
2024-04-19 |
08:41:20 AM |
Now we need to add a sequence number column Seq for the group. When a new event occurs for an account within one hour, Seq+1; If a new event occurs after one hour, reset Seq to 1.
Account_Number |
FuelPurchase_Date |
Fuel_TOD |
Seq |
19 |
2024-04-03 |
07:02:02 AM |
1 |
19 |
2024-04-03 |
07:02:41 AM |
2 |
19 |
2024-04-03 |
02:58:49 PM |
1 |
19 |
2024-04-03 |
07:58:49 PM |
1 |
19 |
2024-04-05 |
02:58:49 PM |
1 |
19 |
2024-04-05 |
02:59:31 PM |
2 |
19 |
2024-04-17 |
11:56:13 PM |
1 |
19 |
2024-04-18 |
12:15:13 AM |
2 |
19 |
2024-04-18 |
02:56:13 PM |
1 |
19 |
2024-04-19 |
07:41:55 AM |
1 |
19 |
2024-04-19 |
07:42:20 AM |
2 |
19 |
2024-04-19 |
08:41:20 AM |
3 |
20 |
2024-04-17 |
11:59:13 PM |
1 |
20 |
2024-04-18 |
07:41:55 AM |
1 |
20 |
2024-04-18 |
07:41:55 PM |
1 |
20 |
2024-04-18 |
07:56:55 PM |
2 |
SPL code:
A |
|
1 |
=mssql.query("select *,cast(FuelPurchase_Date as datetime) + cast(Fuel_TOD as datetime) as DT from tb order by Account_Number,DT”) |
2 |
=A1.new(Account_Number,FuelPurchase_Date,Fuel_TOD, if(Account_Number==Account_Number[-1] && interval@s(Fuel_DT[-1],Fuel_DT)<3600,Seq[-1]+1,1):Seq) |
A1: Use JDBC to query the database, spell out the calculated column DT of date and time type, and sort it by account and DT.
A2: Create a new two-dimensional table and add a new calculated column Seq. When the account number of the current record is the same as the previous record and the time interval is within one hour, Seq+1; Otherwise, reset Seq to 1. [-1] represents the previous record of relative position.
Problem source：https://stackoverflow.com/questions/78380050/creating-sequence-numbers-with-hourly-reset
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
