sql 如何实现分日期段合计？
有如下表：
Day Qty
2012-2-111
2012-2-210
2012-2-32
2012-2-45
2012-2-146
2012-2-154
2012-2-168
2012-2-268
2012-3-161
2012-3-171
2012-3-185
2012-3-196
．．．．．．
请问在SQL2000里面，将上面表的数据，按照半个月来做一个统计，就比如需要出现如下效果：
Qty
３８ －－２月份前１５天之内的加总
１６ －－ ２月１５日之后的加总
１３ －－３月前１５天的加总
．．．．．．
请教代码，谢谢了！
[解决办法]
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (Day datetime,Qty int)
insert into [TB]
select '2012-2-1',11 union all
select '2012-2-2',10 union all
select '2012-2-3',2 union all
select '2012-2-4',5 union all
select '2012-2-14',6 union all
select '2012-2-15',4 union all
select '2012-2-16',8 union all
select '2012-2-26',8 union all
select '2012-3-16',1 union all
select '2012-3-17',1 union all
select '2012-3-18',5 union all
select '2012-3-19',6
select * from [TB]
SELECT SUBSTRING(CONVERT(VARCHAR(10), DAY, 120), 6, 2) + '月'
+ CASE WHEN SUBSTRING(CONVERT(VARCHAR(10), DAY, 120), 9, 2) > '15'
THEN '15日后'
ELSE '15日前'
END AS '日期' ,
SUM(qty) AS qty
FROM dbo.TB
GROUP BY SUBSTRING(CONVERT(VARCHAR(10), DAY, 120), 6, 2) + '月'
+ CASE WHEN SUBSTRING(CONVERT(VARCHAR(10), DAY, 120), 9, 2) > '15'
THEN '15日后'
ELSE '15日前'
END
/*
日期qty
02月15日后16
02月15日前38
03月15日后13*/
select Day,sum(Qty) from (
select Day=(case when 这里先转换一下格式为时间，在截取“日”<15 then),Qty from 表
)a
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (Day datetime,Qty int)
insert into [TB]
select '2012-2-1',11 union all
select '2012-2-2',10 union all
select '2012-2-3',2 union all
select '2012-2-4',5 union all
select '2012-2-14',6 union all
select '2012-2-15',4 union all
select '2012-2-16',8 union all
select '2012-2-26',8 union all
select '2012-3-16',1 union all
select '2012-3-17',1 union all
select '2012-3-18',5 union all
select '2012-3-19',6
with cte
as
(
select Day=case when datename(DAY,TB.Day)>15 then str(DATENAME(MONTH,TB.Day),2)+'月15日前'
else str(DATENAME(MONTH,TB.Day),2)+'月15日后' end
,tb.Qty
from [TB]
)
select cte.Day,SUM(cte.Qty)Qty
from
cte