寻找我的问题的解决方案。在我的示例中,表仅包含 2 列。列变量版本显示我们应用程序的版本号。列日期登录时间显示客户上次登录到应用程序的时间。
我的第一个想法是创建一个最大日期,按日期排序并按varVersion分组。在用户开始使用旧应用程序之前,这似乎工作得很好。
在我的示例中,您可以看到用户使用版本 2.1.3,而不是移回 1.1.8。使用最大时间,看起来他正在使用此版本6天。当我查看数据时,他只使用这个版本5分钟(错误(。
麦克斯:
varVersion | dateLoginTime
2.1.4 | 2018-03-13 11:31:26.893
1.1.8 | 2018-03-07 16:40:21.060
2.1.3 | 2018-02-28 12:26:52.760
2.1.2 | 2018-02-15 12:35:42.707
1.1.6 | 2018-01-23 15:01:46.410
我希望创建新字段并查看应用程序从 to 使用的时间。但未能获得正确的结果。尝试最小/最大/超过,但仍然错误。
最小/最大结果:
varVersion |FROM |TO
2.1.4 |2018-02-28 22:45:48.687 |2018-03-13 11:31:26.893
2.1.3 |2018-02-26 12:16:41.907 |2018-02-28 12:26:52.760
2.1.2 |2018-02-14 19:56:11.837 |2018-02-15 12:35:42.707
1.1.8 |2018-01-24 12:19:06.933 |2018-03-07 16:40:21.060
1.1.6 |2018-01-08 16:54:46.780 |2018-01-23 15:01:46.410
预期成果
version |FROM |TO
2.1.4 |2018-03-07 16:45:10.207 |2018-03-13 11:31:26.893
1.1.8 |2018-03-07 16:40:21.060 |2018-03-07 16:45:10.207
2.1.4 |2018-02-28 22:45:48.687 |2018-03-07 16:40:21.060
2.1.3 |2018-02-26 12:16:41.907 |2018-02-28 22:45:48.687
2.1.2 |2018-02-14 19:56:11.837 |2018-02-26 12:16:41.907
1.1.8 |2018-01-24 12:19:06.933 |2018-02-14 19:56:11.837
1.1.6 |2018-01-08 16:54:46.780 |2018-01-24 12:19:06.933
有人有一些想法吗?
提前致谢
切赫
数据:
--POPULATE DATA FOR TEST
drop table #temp
create table #temp
(varVersion VARCHAR(100),
dateLoginTime DATETIME)
INSERT INTO #temp (varVersion, dateLoginTime)
values
('2.1.4','2018-03-13 11:31:26.893'),
('2.1.4','2018-03-12 11:22:12.650'),
('2.1.4','2018-03-08 08:40:18.133'),
('2.1.4','2018-03-07 16:45:10.207'),
('1.1.8','2018-03-07 16:40:21.060'),
('2.1.4','2018-03-07 12:28:08.823'),
('2.1.4','2018-03-02 12:21:58.583'),
('2.1.4','2018-03-01 12:20:17.163'),
('2.1.4','2018-02-28 22:49:42.320'),
('2.1.4','2018-02-28 22:45:48.687'),
('2.1.3','2018-02-28 12:26:52.760'),
('2.1.3','2018-02-27 12:21:50.887'),
('2.1.3','2018-02-26 12:16:41.907'),
('2.1.2','2018-02-15 12:35:42.707'),
('2.1.2','2018-02-14 19:56:11.837'),
('1.1.8','2018-02-14 12:39:50.603'),
('1.1.8','2018-02-02 12:34:08.393'),
('1.1.8','2018-01-25 12:18:19.790'),
('1.1.8','2018-01-24 12:19:06.933'),
('1.1.6','2018-01-23 15:01:46.410'),
('1.1.6','2018-01-22 12:12:18.510'),
('1.1.6','2018-01-08 16:54:46.780')
--ORIGINAL STATEMENT
SELECT DISTINCT TOP 10
varVersion ,
MAX(dateLoginTime) dateLoginTime--, MAX(dateLoginTime)--, MAX(login_time)
FROM #temp
GROUP BY varVersion
ORDER BY 2 DESC
--NEW STATEMENT
SELECT DISTINCT TOP 10
varVersion ,
MIN(dateLoginTime) 'FROM', MAX(dateLoginTime) 'TO'
FROM #temp
GROUP BY varVersion
ORDER BY 2 DESC
select * from #temp
这应该有效。 使用滞后开始,然后只需在上面搜索。
declare @T table (ver VARCHAR(10), dt DATETIME);
INSERT INTO @T (ver, dt)
values
('2.1.4','2018-03-13 11:31:26.893'),
('2.1.4','2018-03-12 11:22:12.650'),
('2.1.4','2018-03-08 08:40:18.133'),
('2.1.4','2018-03-07 16:45:10.207'),
('1.1.8','2018-03-07 16:40:21.060'),
('2.1.4','2018-03-07 12:28:08.823'),
('2.1.4','2018-03-02 12:21:58.583'),
('2.1.4','2018-03-01 12:20:17.163'),
('2.1.4','2018-02-28 22:49:42.320'),
('2.1.4','2018-02-28 22:45:48.687'),
('2.1.3','2018-02-28 12:26:52.760'),
('2.1.3','2018-02-27 12:21:50.887'),
('2.1.3','2018-02-26 12:16:41.907'),
('2.1.2','2018-02-15 12:35:42.707'),
('2.1.2','2018-02-14 19:56:11.837'),
('1.1.8','2018-02-14 12:39:50.603'),
('1.1.8','2018-02-02 12:34:08.393'),
('1.1.8','2018-01-25 12:18:19.790'),
('1.1.8','2018-01-24 12:19:06.933'),
('1.1.6','2018-01-23 15:01:46.410'),
('1.1.6','2018-01-22 12:12:18.510'),
('1.1.6','2018-01-08 16:54:46.780');
select tt.ver, tt.dt as frm
, isnull((select min(td.dt) from @T td where td.ver <> tt.ver and td.dt > tt.dt), (select max(dt) from @T)) as too
from ( select t.ver, t.dt, lag(t.ver) over (order by t.dt asc) as lagVer
from @T t
) tt
where tt.ver <> tt.lagVer or tt.lagVer is null
order
by tt.dt desc;
ver frm too
---------- ----------------------- -----------------------
2.1.4 2018-03-07 16:45:10.207 2018-03-13 11:31:26.893
1.1.8 2018-03-07 16:40:21.060 2018-03-07 16:45:10.207
2.1.4 2018-02-28 22:45:48.687 2018-03-07 16:40:21.060
2.1.3 2018-02-26 12:16:41.907 2018-02-28 22:45:48.687
2.1.2 2018-02-14 19:56:11.837 2018-02-26 12:16:41.907
1.1.8 2018-01-24 12:19:06.933 2018-02-14 19:56:11.837
1.1.6 2018-01-08 16:54:46.780 2018-01-24 12:19:06.933