我需要确定一周和周末的销售数量,但周末是在周五下午6点到周一上午9点之间计算的。
例如,如果我有以下数据:
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我将得到以下内容:
Weekday: 4
Weekend: 5使用以下命令可以很容易地获得每天的销售额:
select count(*) as total, dateadd(DAY,0, datediff(day,0, created) as created
from sales
group by dateadd(DAY,0, datediff(day,0, created))但是我不知道如何把时间和时间结合起来来获取我需要的信息。
发布于 2017-02-14 22:03:45
这只是一个复杂的case语句。您可以使用outer apply并使用聚合的值来完成此操作:
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;这是有意在工作日使用datename()。星期几受国际化设置的影响。所有说英语的国家都有相同的星期名称,但当地的惯例可能会影响一周的开始日期。我还认为,对于非英语设置,代码很清楚为什么它不能工作--在某种程度上,与"1“相比是不能工作的。
发布于 2017-02-14 22:03:45
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发布于 2017-02-14 22:00:17
试试这样的东西:
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解释: DW =7代表周六,1代表周日,6代表周五,2代表周一我加1作为cnt是为了澄清它是无用的,除非你在子查询中添加distinct。你可以在一个查询中做到这一点,但你必须重复两次大的“case when end”语句。
https://stackoverflow.com/questions/42227777
复制相似问题