sql中按时间间隔分组

发布于 2024-10-30 06:50:24 字数 807 浏览 0 评论 0原文

我需要同时显示 06:00 - 18:00 和 18:00 - 06:00 之间的数据，

这是示例查询 IM 使用。我无法检索第二组数据。怎么办？

喜欢 1、 2、 3 时间范围为 06:00 - 18:00

和 4， 5、 6 营业时间为 18:00 - 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

原文

I need to show data between time 06:00 - 18:00 and 18:00 - 06:00 at same time

this is sample Query I M Using. I can not retrieve second group data. How to do?

like
1,
2,
3
comes in 06:00 - 18:00 range

and 4,
5,
6
comes in 18:00 - 06:00 range

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

向日葵 2024-11-06 06:50:24

您无法检索第二组，因为 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')

You cannot retrieve the second group because 18:00 is greater than 06:00, so there's nothing between them in the same day. For the second group you should filter like this:

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

回复收藏 0 原文

说不完的你爱 2024-11-06 06:50:24

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

A CASE statement might be able to help. For example:

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

回复收藏 0 原文

~没有更多了~