Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Demo
CREATE TABLE Yourtable ([Order_strUserId] varchar(12), [Order_dtmInitiated] datetime, [Order_dtmLastUpdated] datetime) ; INSERT INTO Yourtable ([Order_strUserId], [Order_dtmInitiated], [Order_dtmLastUpdated]) VALUES ('s2ir1f8vqx', '2016-12-13 15:06:17', '2016-12-13 15:06:59'), ('4839m6lnjchn', '2016-12-13 15:09:42', '2016-12-13 15:12:21'), ('y7k3u6q1wjn', '2016-12-13 15:11:40', '2016-12-13 15:12:01'), ('sdc74a0ahid', '2016-12-13 15:14:06', '2016-12-13 15:14:28'), ('2pgj2ixpta9', '2016-12-13 15:17:41', '2016-12-13 15:17:42'), ('qlfv4vmxdb', '2016-12-13 15:18:17', '2016-12-13 15:18:47'), ('y9jd24i59x5v', '2016-12-13 15:19:30', '2016-12-13 15:19:30'), ('9vb2d6u90hn', '2016-12-13 15:22:09', '2016-12-13 15:22:09') ; ;WITH cte AS (SELECT Min(Cast(Dateadd(minute, Datediff(minute, 0, Order_dtmInitiated), 0) AS SMALLDATETIME)) AS st_date, Max(Cast(Dateadd(minute, Datediff(minute, 0, Order_dtmLastUpdated), 0) AS SMALLDATETIME)) ed_date FROM Yourtable UNION ALL SELECT Dateadd(minute, 1, st_date), ed_date FROM cte WHERE st_date < ed_date) SELECT st_date, Count(1) FROM cte a LEFT JOIN Yourtable b ON a.st_date >= Cast(Dateadd(minute, Datediff(minute, 0, Order_dtmInitiated), 0) AS SMALLDATETIME) AND a.st_date <= Cast(Dateadd(minute, Datediff(minute, 0, Order_dtmLastUpdated), 0) AS SMALLDATETIME) GROUP BY st_date OPTION (maxrecursion 0)
run
|
edit
|
history
|
help
0
student
Window Functions - ROW_NUMBER()
sql jueves
New
arp
generate-data-combinations-for-a-column-dynamically-based-on-another-column-sql
test 2018.05.26
db6
Cine latino
gfhrey