Extract the Data from the table with 10 min time range

user_15272845 0
Viewed 52

I need an SQL statement to select the data from the table with 10 min time range. Below is my table.

Product PurchasedTime ProductNo
ABC 9:22:58 123
ABC 9:31:40 123
ABC 9:39:40 123
DEF 9:59:41 258
DEF 10:02:38 258
GHI 13:20:10 963
GHI 13:50:25 745

I need output like the below table.

Product PurchasedTime ProductNo TimeRange
ABC 9:22:58 123 9:20 - 9:30
ABC 9:31:40 123 9:30 - 9:40
ABC 9:39:40 123 9:30 - 9:40
DEF 9:59:41 258 9:50 - 10:00
DEF 10:02:38 258 10:00 - 10:10
GHI 13:20:10 963 13:20 - 13:30
GHI 13:56:25 745 13:50 - 14:00
sql
1 Answers

Here goes your query to calculate time rage for each rows (in SQL Server):

select * ,(concat(datepart(hour,purchasedtime),':',(datepart(MINUTE, purchasedtime)/ 10)*10 ,' - ',datepart(hour,purchasedtime),':', ((datepart(MINUTE, PurchasedTime) / 10)+1)*10))TimeRange
from myproduct

Output:

enter image description here

user_86516010
Related