TravyLee，你昨天帮小弟我写的SQL有点小疑点，帮忙再看看，多谢

TravyLee，你昨天帮我写的SQL有点小问题，帮忙再看看，谢谢！
create table test
(
id int,---编号
sl int,---数量
rq datetime ---日期
)

insert into test select 10, 101,'2002-1-1 6:00:00' ---注意，这条记录不应算在2002-01-01这一天里面，每一天的时间是从6:10开始的，到第二天的6:00结束

-------------------------------------------------------
insert into test select 10, 100,'2002-1-1 6:10:00'
insert into test select 10, 100,'2002-1-1 6:20:00'
insert into test select 10, 100,'2002-1-1 6:30:00'
insert into test select 10, 100,'2002-1-1 6:40:00'
insert into test select 10, 100,'2002-1-1 6:50:00'
insert into test select 10, 100,'2002-1-1 7:00:00'



insert into test select 10, 100,'2002-1-1 23:10:00'
insert into test select 10, 100,'2002-1-1 23:20:00'
insert into test select 10, 100,'2002-1-1 23:30:00'
insert into test select 10, 100,'2002-1-1 23:40:00'
insert into test select 10, 100,'2002-1-1 23:50:00'
insert into test select 10, 100,'2002-1-2 00:00:00'


insert into test select 10, 100,'2002-1-2 00:10:00'
insert into test select 10, 100,'2002-1-2 00:20:00'
insert into test select 10, 100,'2002-1-2 00:30:00'
insert into test select 10, 100,'2002-1-2 00:40:00'
insert into test select 10, 100,'2002-1-2 00:50:00'
insert into test select 10, 100,'2002-1-2 01:00:00'


insert into test select 10, 100,'2002-1-2 05:10:00'
insert into test select 10, 100,'2002-1-2 05:20:00'
insert into test select 10, 100,'2002-1-2 05:30:00'
insert into test select 10, 100,'2002-1-2 05:40:00'
insert into test select 10, 100,'2002-1-2 05:50:00'
insert into test select 10, 100,'2002-1-2 06:00:00'

----------------------------------------------------

insert into test select 10, 105,'2002-1-2 6:10:00'
insert into test select 10, 100,'2002-1-2 6:20:00'
insert into test select 10, 100,'2002-1-2 6:30:00'
insert into test select 10, 100,'2002-1-2 6:40:00'
insert into test select 10, 100,'2002-1-2 6:50:00'
insert into test select 10, 100,'2002-1-2 7:00:00'

insert into test select 10, 100,'2002-1-2 23:10:00'
insert into test select 10, 100,'2002-1-2 23:20:00'
insert into test select 10, 100,'2002-1-2 23:30:00'
insert into test select 10, 100,'2002-1-2 23:40:00'
insert into test select 10, 100,'2002-1-2 23:50:00'
insert into test select 10, 100,'2002-1-3 00:00:00'

insert into test select 10, 100,'2002-1-3 00:10:00'
insert into test select 10, 100,'2002-1-3 00:20:00'
insert into test select 10, 100,'2002-1-3 00:30:00'
insert into test select 10, 100,'2002-1-3 00:40:00'
insert into test select 10, 100,'2002-1-3 00:50:00'
insert into test select 10, 100,'2002-1-3 01:00:00'


insert into test select 10, 100,'2002-1-3 05:10:00'
insert into test select 10, 100,'2002-1-3 05:20:00'
insert into test select 10, 100,'2002-1-3 05:30:00'
insert into test select 10, 100,'2002-1-3 05:40:00'
insert into test select 10, 100,'2002-1-3 05:50:00'
insert into test select 10, 100,'2002-1-3 06:00:00'



select CONVERT(varchar(10),rq,120) as rq,SUM(sl) as sumsl from(
select case when 0<= DATEPART(HH,rq) and DATEPART(HH,rq)<6
then DATEADD(HH,-7,rq) else rq end as rq,sl from test)a
where CONVERT(varchar(10),rq,120) between '2002-01-01' and '2002-01-02'
group by CONVERT(varchar(10),rq,120)

/*
你的结果是这样：

2002-01-012401
2002-01-022405

正确的结果应该是：

2002-01-012400
2002-01-022405


*/


drop table test

[解决办法]

SQL code

select convert(varchar(10),dateadd(mi,-370,rq),120) as rq,sum(sl) as sl from testwhere convert(varchar(5),rq,108)  not between '06:01' and '06:09'and convert(varchar(10),dateadd(mi,-370,rq),120) between '2002-01-01' and '2002-01-02'group by convert(varchar(10),dateadd(mi,-370,rq),120)/*rq         sl---------- -----------2002-01-01 24002002-01-02 2405(2 行受影响)*/ 
 
[解决办法]
探讨

树人的是这样

2001-12-31 101
2002-01-01 2400
2002-01-02 2405

多了一个12-31

[解决办法]
真幽默，好选不选，选个最复杂并且是错的答案。
[解决办法]
这样?


SQL code
    SELECT         dt=CONVERT(VARCHAR(10),rq,120),        sl=SUM(CASE WHEN CONVERT(VARCHAR(5),rq,8)>='00:10' THEN sl ELSE 0 END)    FROM (select rq=DATEADD(hh,-6,rq),sl FROM test)t    GROUP BY CONVERT(VARCHAR(10),rq,120)