表结构如下ID EndTime
3 2008-07-02 23:30:00
8 2008-01-18 19:13:00
24 2008-03-26 18:47:00
34 2008-07-14 12:45:00
57 2007-12-19 22:21:00
60 2008-03-24 16:46:00
63 2008-04-20 20:04:00
86 2008-03-25 10:29:00
103 2008-03-24 08:23:00
126 2007-11-25 19:37:00
128 2008-04-22 11:02:00
133 2007-09-29 18:46:00
139 2008-03-25 21:12:00
143 2007-11-04 00:44:00
173 2008-04-16 19:02:00
175 2008-03-19 10:06:00
190 2008-03-22 09:57:00
198 2008-02-20 17:32:00
203 2008-03-31 20:42:00
238 2008-05-17 08:44:00
246 2008-06-22 21:08:00
248 2008-02-17 16:23:00
258 2008-03-21 12:13:00
261 2008-03-24 12:01:00
275 2008-03-18 21:36:00
1327 2008-02-02 16:34:00
1331 2008-04-04 12:59:00
1397 2008-07-05 12:54:00
1413 2008-06-15 23:39:00
1428 2008-04-10 13:59:00
1439 2008-05-06 20:45:00
1460 2008-04-06 14:31:00
1469 2008-05-07 20:17:00
1504 2008-04-07 22:14:00
1530 2008-02-06 16:14:00
1565 2008-04-08 10:11:00
1566 2008-04-08 13:32:00
1619 2008-06-17 21:46:00
1631 2008-06-05 12:03:00
1644 2007-12-05 13:55:00
1648 2008-04-29 14:00:00
1659 2008-05-02 23:23:00
1774 2008-08-26 17:21:00
1776 2008-01-05 11:01:00
1795 2008-06-18 16:04:00
1796 2007-10-15 19:22:00
2766 2008-04-24 00:51:00
2790 2008-03-07 20:56:00
2797 2008-05-07 23:34:00
2815 2008-05-07 13:54:00
2841 2007-09-22 22:50:00
2991 2008-06-20 16:38:00
3001 2008-02-03 14:45:00
3002 2008-04-28 13:23:00
3026 2008-04-09 13:05:00
3044 2008-07-06 23:55:00
3057 2008-04-23 12:53:00
3180 2008-10-03 22:28:00
4261 2008-09-03 00:00:00
4266 2008-05-22 16:07:00
4283 2008-03-03 22:29:00
4302 2008-03-04 09:05:00
4306 2007-11-18 11:55:00
4307 2008-05-16 15:23:00
4310 2008-01-03 20:52:00
4447 2008-05-25 13:41:00
4501 2008-05-26 00:20:00
4515 2008-08-04 11:00:00
4596 2008-05-24 19:01:00
4956 2008-05-31 13:50:00
4957 2008-06-05 16:46:00
--EndTime为用户到期时间。
--想计算每个月用户流失量,就是这个月“没到期的用户”减去“上个月没到期的用户”
--没到期用户就是指2008-06-05 16:46:00减去的当前时间大于0。
--显示结果如下
shuliang date
XXX 2008-09-01
XXX 2008-08-01
XXX 2008-07-01
XXX 2008-06-01
…………
3 2008-07-02 23:30:00
8 2008-01-18 19:13:00
24 2008-03-26 18:47:00
34 2008-07-14 12:45:00
57 2007-12-19 22:21:00
60 2008-03-24 16:46:00
63 2008-04-20 20:04:00
86 2008-03-25 10:29:00
103 2008-03-24 08:23:00
126 2007-11-25 19:37:00
128 2008-04-22 11:02:00
133 2007-09-29 18:46:00
139 2008-03-25 21:12:00
143 2007-11-04 00:44:00
173 2008-04-16 19:02:00
175 2008-03-19 10:06:00
190 2008-03-22 09:57:00
198 2008-02-20 17:32:00
203 2008-03-31 20:42:00
238 2008-05-17 08:44:00
246 2008-06-22 21:08:00
248 2008-02-17 16:23:00
258 2008-03-21 12:13:00
261 2008-03-24 12:01:00
275 2008-03-18 21:36:00
1327 2008-02-02 16:34:00
1331 2008-04-04 12:59:00
1397 2008-07-05 12:54:00
1413 2008-06-15 23:39:00
1428 2008-04-10 13:59:00
1439 2008-05-06 20:45:00
1460 2008-04-06 14:31:00
1469 2008-05-07 20:17:00
1504 2008-04-07 22:14:00
1530 2008-02-06 16:14:00
1565 2008-04-08 10:11:00
1566 2008-04-08 13:32:00
1619 2008-06-17 21:46:00
1631 2008-06-05 12:03:00
1644 2007-12-05 13:55:00
1648 2008-04-29 14:00:00
1659 2008-05-02 23:23:00
1774 2008-08-26 17:21:00
1776 2008-01-05 11:01:00
1795 2008-06-18 16:04:00
1796 2007-10-15 19:22:00
2766 2008-04-24 00:51:00
2790 2008-03-07 20:56:00
2797 2008-05-07 23:34:00
2815 2008-05-07 13:54:00
2841 2007-09-22 22:50:00
2991 2008-06-20 16:38:00
3001 2008-02-03 14:45:00
3002 2008-04-28 13:23:00
3026 2008-04-09 13:05:00
3044 2008-07-06 23:55:00
3057 2008-04-23 12:53:00
3180 2008-10-03 22:28:00
4261 2008-09-03 00:00:00
4266 2008-05-22 16:07:00
4283 2008-03-03 22:29:00
4302 2008-03-04 09:05:00
4306 2007-11-18 11:55:00
4307 2008-05-16 15:23:00
4310 2008-01-03 20:52:00
4447 2008-05-25 13:41:00
4501 2008-05-26 00:20:00
4515 2008-08-04 11:00:00
4596 2008-05-24 19:01:00
4956 2008-05-31 13:50:00
4957 2008-06-05 16:46:00
--EndTime为用户到期时间。
--想计算每个月用户流失量,就是这个月“没到期的用户”减去“上个月没到期的用户”
--没到期用户就是指2008-06-05 16:46:00减去的当前时间大于0。
--显示结果如下
shuliang date
XXX 2008-09-01
XXX 2008-08-01
XXX 2008-07-01
XXX 2008-06-01
…………
解决方案 »
- 安装sql server 2005路径
- 两表之间的数据汇总更新?
- 数据库中的密码字段,需要对其进行加密,但加密算法该写到什么地方???
- 鸟哥初问(+100):索引填充因子的问题
- 08升级r2,现有两个数据库,怎么解决?
- 删除xp_cmdshell,提示服务器: 消息 3701,级别 11,状态 5,过程 sp_dropextendedproc,行 18 无法 除去 过程 'xp_cmdshell',因为它在系统目录中
- 续雷。。。之前问错了
- 急啊!!!!!如何用SQl语句启动跟踪
- 难道我这个存储过程有问题吗
- 数据备份,请关注!
- oracle和mssql哪个好?如果要做数据管理员,考什么证书,学什么东西好?
- 我想请教个关于数据表某个字段因时间改变而变化的问题,详情请进!!!
动态sql语句循环30多次也可以。。麻烦高人给支招吧。。
cast(convert(varchar(7),EndTime,120)+'-01' as datetime) as date
from tab
group by cast(convert(varchar(7),EndTime,120)+'-01' as datetime)
加条件select count(*) as shuliang,
cast(convert(varchar(7),EndTime,120)+'-01' as datetime) as date
from tab
where EndTime>=dateadd(year,-3,convert(varchar(7),getdate*(,120)+'-01')
group by cast(convert(varchar(7),EndTime,120)+'-01' as datetime)
select endtime-datepart(dd,endtime) as mm,count(1) as con from tb where endtime>getdate())a
left join (
select endtime-datepart(dd,endtime) as mm,count(1) as con from tb where endtime>getdate()
)b on a.mm=dateadd(mm,-1,b.mm)
where a.mm between dateadd(yy,-3,getdate()) and getdate()
select count(*) as shuliang,
cast(convert(varchar(7),EndTime,120)+'-01' as datetime) as date
from tab
group by cast(convert(varchar(7),EndTime,120)+'-01' as datetime)
--这个是结果,不是我要的效果
3 2007-09-01 00:00:00.000
4 2007-10-01 00:00:00.000
45 2007-11-01 00:00:00.000
27 2007-12-01 00:00:00.000
11 2008-01-01 00:00:00.000
21 2008-02-01 00:00:00.000
25 2008-03-01 00:00:00.000
22 2008-04-01 00:00:00.000
18 2008-05-01 00:00:00.000
15 2008-06-01 00:00:00.000
14 2008-07-01 00:00:00.000
10 2008-08-01 00:00:00.000
13 2008-09-01 00:00:00.000
18 2008-10-01 00:00:00.000
2 2008-11-01 00:00:00.000
1 2008-12-01 00:00:00.000
1 2009-02-01 00:00:00.000
1 2009-06-01 00:00:00.000
1 2009-07-01 00:00:00.000
1 2009-09-01 00:00:00.000
1 2010-10-01 00:00:00.000
select count(*) as shuliang,
cast(convert(varchar(7),EndTime,120)+'-01' as datetime) as date
from tab
where EndTime>=dateadd(year,-3,convert(varchar(7),getdate*(,120)+'-01')
group by cast(convert(varchar(7),EndTime,120)+'-01' as datetime)--消息 156,级别 15,状态 1,第 5 行
--在关键字 'group' 附近有语法错误。
列 'endtime'在选择列表中无效,因为该列未包含在聚合函数中,并且没有 GROUP BY 子句。
消息 8118,级别 16,状态 1,第 1 行
列 'endtime'在选择列表中无效,因为该列未包含在聚合函数中,并且没有 GROUP BY 子句。
消息 8118,级别 16,状态 1,第 1 行
列 'endtime'在选择列表中无效,因为该列未包含在聚合函数中,并且没有 GROUP BY 子句。
消息 8118,级别 16,状态 1,第 1 行
列 'endtime'在选择列表中无效,因为该列未包含在聚合函数中,并且没有 GROUP BY 子句。
没到期用户就是指EndTime减去的当前时间大于0。 没到期时间跟所统计的月份没关系,只和当前时间有关系。。想计算每个月的用户流失量。
比如2008-9-01月到期用户(EndTime-getdate()>0)是10个剩余用户
比如2008-8-01月到期用户(EndTime-(getdate()-1月))是30个剩余用户
那么2008-9-01的流失用户就是30-10=20个。
select endtime-datepart(dd,endtime) as mm,count(1) as con from tb where endtime>getdate() group by endtime-datepart(dd,endtime))a
left join (
select endtime-datepart(dd,endtime) as mm,count(1) as con from tb where endtime>getdate() group by endtime-datepart(dd,endtime))b on a.mm=dateadd(mm,-1,b.mm)
where a.mm between dateadd(yy,-3,getdate()) and getdate()改改表名,看行不行
cast(convert(varchar(7),EndTime,120)+'-01' as datetime) as date
from tab
where EndTime>=dateadd(year,-3,convert(varchar(7),getdate(),120)+'-01')
group by cast(convert(varchar(7),EndTime,120)+'-01' as datetime)
cast(convert(varchar(7),EndTime,120)+'-01' as datetime) as date
from tab
where EndTime>=dateadd(year,-3,convert(varchar(7),getdate*(,120)+'-01')
and EndTime<convert(varchar(7),getdate*(,120)+'-01'
group by cast(convert(varchar(7),EndTime,120)+'-01' as datetime)
order by cast(convert(varchar(7),EndTime,120)+'-01' as datetime) desc
看这一句
下面这样,能行吗?想计算每个月的用户流失量。
比如2008-9-01月到期用户(EndTime-getdate()>0)是10个剩余用户
比如2008-8-01月到期用户(EndTime-(getdate()-1月))是30个剩余用户
那么2008-9-01的流失用户就是30-10=20个。 shuliang date
XXX 2008-09-01
XXX 2008-08-01
XXX 2008-07-01
XXX 2008-06-01
……
XXX 2006-07-01
XXX 2006-06-01
月份=cast(convert(varchar(7),EndTime,120)+'-01' as datetime),
流失人数=
(
(select count(*) from @t where EndTime-dateadd(mm,-1,getdate())>0)
-
(select count(*) from @t where EndTime-getdate()>0)
)
from @T
where EndTime between dateadd(year,-3,convert(varchar(7),getdate(),120)+'-01') and getdate() group by cast(convert(varchar(7),EndTime,120)+'-01' as datetime)