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
TestTable
Running Total
Get amount of users that bought something soon after registration
Null as default value
sum vs count with multiple options in group by clause
sadasd
Stackoverflow 37635278
ss sqlpractice2014
sql
begginer1