组由时间间隔在SQL

问题描述：

我需要显示时间06:00之间的数据 - 在同一时间组由时间间隔在SQL

这是样本查询I M使用06:00 - 18:00和18:00。我无法检索第二组数据。怎么做？

像 1， 2，进来06:00 - 18点范围

和4， 5，进来18点 - 06:00范围

SELECT  COUNT(cp.comm_pend_id) AS comm_pend_id, cp.UserID, um.Username, CONVERT(varchar, cp.submitted_date, 101) AS Date, SUM(cp.Earning) 
         AS Earning, SUM(cp.total_commission) AS total_commission 
FROM   dbo.comm_pending AS cp INNER JOIN 
         dbo.user_master AS um ON cp.UserID = um.UserID 
GROUP BY cp.UserID, CONVERT(varchar, cp.submitted_date, 101), um.Username, cp.PaidStatus, CONVERT(varchar(10), cp.submitted_date, 108) 
HAVING  (cp.PaidStatus = 'unpaid') AND (CONVERT(varchar(10), cp.submitted_date, 108) BETWEEN '06:00:00' AND '18:00:00') 
ORDER BY cp.UserID

答

由于18:00大于06:00，所以无法检索第二组，因此在同一天他们之间没有任何关系。对于第二组，您应该像这样过滤：

((CONVERT(varchar(10), cp.submitted_date, 108) < '06:00:00' 
OR 
(CONVERT(varchar(10), cp.submitted_date, 108) > '18:00:00')

我想至06:00之间显示两个组数据 - 18:00至18:00 - 06:00 – Jaj 2011-04-06 16:07:46

如果你表现出你期望作为查询的输出在视觉上会是什么帮助。 – 2011-04-06 16:15:32

答

CASE声明可能会有所帮助。例如：

create table #times (num int, dt datetime) 

insert into #times values (1,'2011-4-6 0:00:00') 
insert into #times values (2,'2011-4-6 4:00:00') 
insert into #times values (3,'2011-4-6 8:00:00') 
insert into #times values (4,'2011-4-6 12:00:00') 
insert into #times values (5,'2011-4-6 16:00:00') 
insert into #times values (6,'2011-4-6 20:00:00') 

select 
    sum(case when (datepart(hh,dt) between 6 and 17) then num else 0 end) as daySum, 
    sum(case when (datepart(hh,dt) not between 6 and 17) then num else 0 end) as nightSum 
from #times

组由时间间隔在SQL

相关推荐