一条跨天消费记录分类统计的问题

在csdn，原来的问题是一个消费记录报表问题，感觉问题顶有意思，特别拿来分享一下。

如下：
需要做一个月报表，以日为基本单位，展示指定月中，每天的消费统计结果。
难点在于：这里的一“天”与自然概念中的一“天”不同，是从前一天的17：00以后到这一天的17：00以前的记录。
数据表结构如下：
CREATE TABLE [dbo].[T_ConsumeLog] (
[PKID] [int] IDENTITY (1, 1) NOT NULL , --流水ID
[CarWashFee] [float] NULL , --洗车费
[AccessoryFee] [float] NULL , --配件费
[ManHourFee] [float] NULL , --手工费
[ConsumeTime] [datetime] NULL , --消费时间
) ON [PRIMARY]
我目前的查询语句是：
SELECT CONVERT(VARCHAR,ConsumeTime,101) AS ConsumeTime , SUM(CarWashFee) AS CarWashFee,
SUM(AccessoryFee) AS AccessoryFee,
SUM(ManHourFee) AS ManHourFee
FROM T_ConsumeLog
GROUP BY CONVERT(VARCHAR,ConsumeTime,101)
这样查询出来的结果中，每天统计的是时间在当天0：00以后到第二天0：00以前的记录
例如，有三条记录：
PKID CarWashFee AccessoryFee ManHourFee ConsumeTime
------------------------------------------------------------------------
1 10.0 0 0 2007-1-17 16:00:00
2 10.0 0 0 2007-1-17 17:01:00
3 10.0 0 0 2007-1-18 10:00:00
------------------------------------------------------------------------
查询结果应该是：
ConsumeTime CarWashFee AccessoryFee ManHourFee
----------------------------------------------------------
01/17/2007 10.0 0.0 0.0
01/18/2007 20.0 0.0 0.0
----------------------------------------------------------
但查询出来的结果是：
ConsumeTime CarWashFee AccessoryFee ManHourFee
----------------------------------------------------------
01/17/2007 20.0 0.0 0.0
01/18/2007 10.0 0.0 0.0
----------------------------------------------------------
有一朋友回复中使用到一个自定义函数来实现。自己感觉这问题顶有意思，突然想到以前在一考勤系统中处理过有关跨天考勤数据分类统计的问题；试着不使用自定义函数来实现。最后在查询分析器中，测试通过了。方法如下：
只要到CASE语句，如下：
CREATE TABLE #T_ConsumeLog(PKID int IDENTITY (1, 1) NOT NULL,CarWashFee float null,ConsumeTime datetime null )
INSERT INTO #T_ConsumeLog
SELECT 10,'2007-1-17 16:00:00' UNION ALL
SELECT 10,'2007-1-17 17:01:00' UNION ALL
SELECT 10,'2007-1-18 10:00:00'
SELECT CASE WHEN CONVERT(nvarchar(16),ConsumeTime,121) BETWEEN
CONVERT(nvarchar(11),DATEADD(day,-1,ConsumeTime),121)+'17：01' AND CONVERT(nvarchar(11),ConsumeTime,121)+'17：00'
THEN CONVERT(nvarchar(10),ConsumeTime,121) ELSE CONVERT(nvarchar(10),DATEADD(day,1,ConsumeTime),121) END
AS ConsumeTime
,SUM(CarWashFee) AS CarWashFee
FROM #T_ConsumeLog
GROUP BY CASE WHEN CONVERT(nvarchar(16),ConsumeTime,121) BETWEEN
CONVERT(nvarchar(11),DATEADD(day,-1,ConsumeTime),121)+'17：01' AND CONVERT(nvarchar(11),ConsumeTime,121)+'17：00'
THEN CONVERT(nvarchar(10),ConsumeTime,121) ELSE CONVERT(nvarchar(10),DATEADD(day,1,ConsumeTime),121) END
DROP TABLE #T_ConsumeLog
/*
结果：
ConsumeTime CarWashFee
----------------------------------
2007-01-17 10.0
2007-01-18 20.0
*/

在我使用CASE方法后，天道酬勤大哥把他原来使用到的自定义函数去掉，也使用到CASE方法，感觉不错，特拿来与大家分享，方法如下：
CREATE TABLE [dbo].[T_ConsumeLog] (
[PKID] [int] IDENTITY (1, 1) NOT NULL ,--流水ID
[CarWashFee] [float] NULL ,--洗车费
[AccessoryFee] [float] NULL ,--配件费
[ManHourFee] [float] NULL ,--手工费
[ConsumeTime] [datetime] NULL ,--消费时间
) ON [PRIMARY]
insert T_ConsumeLog(CarWashFee, AccessoryFee, ManHourFee, ConsumeTime)
select 10.0, 0, 0, '2007-1-17 16:00:00'
union all select 10.0, 0, 0, '2007-1-17 17:01:00'
union all select 10.0, 0, 0, '2007-1-18 10:00:00'

SELECT CONVERT(VARCHAR,dateadd(hour, -17, ConsumeTime)+1,101) AS ConsumeTime , SUM(CarWashFee) AS CarWashFee,
SUM(AccessoryFee) AS AccessoryFee,
SUM(ManHourFee) AS ManHourFee
FROM T_ConsumeLog
GROUP BY CONVERT(VARCHAR,dateadd(hour, -17, ConsumeTime)+1,101)
--result
ConsumeTime CarWashFee AccessoryFee ManHourFee
------------------------------ ----------------------------------------------------- ----------------------------------------------------- -----------------------------------------------------
01/17/2007 10.0 0.0 0.0
01/18/2007 20.0 0.0 0.0
(2 row(s) affected)

原来问题：http://community.youkuaiyun.com/Expert/topic/5323/5323605.xml?temp=.925564

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7764484/viewspace-895762/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7764484/viewspace-895762/