按日和小时分组的t-SQL

I need to determine the number of sales for the week and weekend but the weekend is to be counted as between 6pm Friday and 9am Monday.

我需要确定一周和周末的销售数量，但周末应计算在周五下午6点到周一上午9点之间。

For instance, if I have the below data:

例如，如果我有以下数据：

2017-02-09 14:00
2017-02-09 19:00
2017-02-10 17:15
2017-02-10 18:22
2017-02-11 11:00
2017-02-11 16:00
2017-02-12 19:30
2017-02-13 08:00
2017-02-14 14:00

I would get the following:

我会得到以下内容：

Weekday: 4
Weekend: 5

Its fairly easy to get the sales per day using something like:

使用以下内容可以轻松获得每日销售额：

select count(*) as total, dateadd(DAY,0, datediff(day,0, created) as created
from sales
group by dateadd(DAY,0, datediff(day,0, created))

But I cant think how to combine the hours and days to get the information I require.

但我想不出如何结合时间和日期来获取我需要的信息。

4 个解决方案

#1

This is just a complicated case statement. You can do this using outer apply and use the value for aggregation:

这只是一个复杂的案例陈述。您可以使用外部应用执行此操作并使用聚合值：

select weekpart, count(*)
from sales s outer apply
     (values (case when datename(dw, created) in ('Tuesday', 'Wednesday', 'Thursday') then 'Weekday'
                   when datename(dw, created) = 'Monday' and 
                        datepart(hh, created) >= 9
                   then 'Weekday'
                   when datename(dw, created) = 'Friday' and 
                        datepart(hh, created) < 18
                   then 'Weekday'
                   else 'Weekend'
               end)
      ) v(weekpart)
group by v.weekpart;

This intentionally uses datename() for the weekdays. The day-of-week is affected by internationalization settings. All English-speaking countries have the same names for the week days, but local conventions may affect the date when the week starts. I also figure that for a non-English setting, the code is pretty clear as to why it won't work -- in a way that comparing to "1" is not.

这故意使用datename（）作为工作日。周日受国际化设置的影响。所有英语国家/地区的工作日都具有相同的名称，但当地约定可能会影响本周开始的日期。我还认为，对于非英语设置，代码非常清楚为什么它不起作用 - 以与“1”相比不是。

#2

with CTE as
(
select created,
  case when 
   (datepart(dw,[created]) = 6 and  datepart(hh,created) >= 18) or
   (datepart(dw,created)=7 or datepart(dw,created)=1) or 
   (datepart(dw,[created]) = 2 and  datepart(hh,created) <= 9)
   then 'Weekend'
  else 'Weekday' end as weekType
 from Table1)
 select weekType, count(1)
 from CTE
 group by weekType

Check it here: http://sqlfiddle.com/#!6/4fbb0/8

请在此处查看：http：//sqlfiddle.com/#！6 / 4fbb0 / 8

#3

Try something like that :

试试这样的事情：

SELECT week_or_weekend, count(*)
FROM
(
    SELECT 1 as cnt, CASE WHEN 
    DATEPART(DW, created) in (7,1)
    OR
    (DATEPART(DW, created) 6 AND DATEPART(hour, created) >= 18)
    OR
    (DATEPART(DW, created) 2 AND DATEPART(hour, created) < 9)
    THEN 'WEEKEND'
    ELSE 'WEEK'
    END week_or_weekend 
    FROM sales
) q
GROUP BY week_or_weekend

Explanation: DW = 7 stands for Saturday, 1 for Sunday, 6 for Friday, 2 for Monday I add 1 as cnt for clarification by it's useless unless you add a distinct in the sub-query. You could do it in 1 query but you would have to repeat twice the big 'case when end' statement.

说明：DW = 7代表星期六，1代表星期日，6代表星期五，2代表星期一我添加1作为cnt澄清它没用，除非你在子查询中添加一个不同的。您可以在1个查询中执行此操作，但是您必须重复两次大结果'结束时'语句。

#4

This will get you started:

这将让你开始：

with CTE as
(
select dateadd(hh, datepart(hh,created), dateadd(dd,0, datediff(dd,0,created))) as created_dayhour, 
       someothercolumn, 
       datepart(dw,created) as create_day, 
       datepart(hh, created) as create_hour
from sales
)
select created_dayhour, count(someothercolumn)
from CTE
group by created_dayhour

#1