SQL Server 计算一个值在多个日期范围之间出现的次数并与前几周进行比较 [英] SQL Server to count how many times a value appears between multiple date ranges and compare to previous weeks
问题描述
我第一次在
SQL Server 计算一个值在多个日期范围之间出现的次数
并且不确定我是否应该发布新问题或修改原始帖子.虽然它建立在原始帖子的基础上，但我觉得它足以呈现新帖子 - 请告知这是否正确.
and wasn't sure if I should post a new question or modify the original post. Though it's building on the original post I felt it was different enough to render a new post - please advise if this was the correct thing to do.
我没有得到一些行之有效的解决方案，但不幸的是，有人警告我，我需要根据附加条件进行一些修改，但无法解决.
I wasn't given a few solutions that worked well but unfortunately a caveat was thrown at me and I need to make some modifications based on an additional criteria and not able to solve it.
首先是一些示例数据和预期输出
First off here again are some sample data and the expected output
| Time_Stamp | Emp_ID | Balance | Hours |
|-----------------|---------|---------|-------|
| 7/16/2017 19:40 | 3140340 | 2250 | 37.5 |
| 7/16/2017 19:40 | 2000950 | 4050 | 67.5 |
| 7/16/2017 19:40 | 3118410 | 400 | 6.7 |
| 7/16/2017 19:40 | 311840 | 11700 | 195 |
| 7/23/2017 21:19 | 3140340 | 2250 | 37.5 |
| 7/23/2017 21:19 | 2000950 | 4050 | 67.5 |
| 7/23/2017 21:19 | 3118410 | 800 | 13.3 |
| 7/23/2017 21:19 | 3124160 | 450 | 7.5 |
| 7/23/2017 21:19 | 311840 | 400 | 6.7 |
| 7/30/2017 7:00 | 3140340 | 2250 | 37.5 |
| 7/30/2017 7:00 | 2000950 | 400 | 6.7 |
| 7/30/2017 7:00 | 3118410 | 1200 | 20 |
| 7/30/2017 7:00 | 311840 | 700 | 11.7 |
| 8/6/2017 12:00 | 3140340 | 444 | 7.4 |
| 8/6/2017 12:00 | 3118410 | 444 | 7.4 |
| 8/6/2017 12:00 | 3124160 | 90 | 1.5 |
| 8/6/2017 12:00 | 311840 | 325 | 5.4 |
| 8/13/2017 12:00 | 3140340 | 900 | 15 |
| 8/13/2017 12:00 | 3118410 | 1350 | 22.5 |
| 8/13/2017 12:00 | 3124160 | 90 | 1.5 |
| 8/13/2017 12:00 | 311840 | 1700 | 28.3 |
预期输出如下
| | 16-Jul | 23-Jul | 30-Jul | 6-Aug | 13-Aug |
|---------|--------|--------|--------|-------|--------|
| emp_id | wk1 | wk2 | wk3 | wk4 | wk5 |
| 3140340 | 1 | 2 | 3 | 0 | 1 |
| 2000950 | 1 | 2 | 0 | 0 | 0 |
| 3118410 | 0 | 1 | 2 | 0 | 1 |
| 311840 | 1 | 0 | 1 | 0 | 1 |
| 3124160 | 0 | 1 | 0 | 1 | 2 |
一个重要的注意事项 - 不幸的是，余额(分钟)的数据类型是 varchar，我需要通过除以 60 将其转换为小时.为此我使用了以下内容
An important note - unfortunately the data type for balance (minutes) is in varchar and I need to convert it to hours by dividing by 60. I used the following for that
ROUND(CONVERT(varchar(50),CONVERT(float,([BALANCE]/convert(float,60.0)))),2) AS [Hours]
话虽如此，我现在需要完成的是，如果每周的余额少于 7.5 小时，那么只需输入 0(零).
With that being said what I now need to accomplish is if the balance of each week is less than 7.5 hours then just put a 0 (zero).
如果 >= 7.5 小时，则计数为 1.如果连续周 >= 7.5 小时 - 例如连续 2 周，则第一周计数为 1，第二周计数为 2.如果第三周低于 7.5 小时，然后是 0.
If it is >= 7.5 hours then a count of 1. If consecutive weeks are >= 7.5 hours - for example 2 consecutive weeks then the first week will have a count of 1 and the 2nd week a count of 2. IF the 3rd week is below 7.5 hours then 0.
如前所述，可以在以下位置找到原始问题和解决方案SQL Server 计算一个值在多个日期范围之间出现的次数
As stated earlier the original question along with the solutions can be found at SQL Server to count how many times a value appears between multiple date ranges
对于原始问题非常有效的两个解决方案是
Two of the solutions that worked really well for the original question were
;WITH
weekcounts AS (
SELECT Time_Stamp, Emp_ID, DATEPART(week, Time_Stamp) AS int_week FROM sampleData
)
,counts AS (
SELECT Emp_ID, int_week, 1 AS int_count
FROM weekcounts
UNION ALL
SELECT weekcounts.Emp_ID, weekcounts.int_week, 1 AS int_count
FROM weekcounts
INNER JOIN counts
ON weekcounts.Emp_ID = counts.Emp_ID
AND (weekcounts.int_week - 1) = counts.int_week
)
,countsagg AS (
SELECT Emp_ID, int_week, SUM(int_count) AS int_count
FROM counts
GROUP BY Emp_ID, int_week
)
SELECT * FROM countsagg
PIVOT (MAX(int_count) FOR int_week IN ([29],[30],[31],[32],[33])) piv
和
; with wk_nbrs as
(
--recursive CTE that generates the week numbers.
-- 7/23 thru 7/29 is Week 1
select cast('2017-07-23' as date) as wk_bgn
, cast('2017-07-29' as date) as wk_end
, 1 as wk_nbr
union all
select dateadd(d,7,fw.wk_bgn) as wk_bgn
, dateadd(d,7,fw.wk_end) as wk_end
, fw.wk_nbr + 1 as wk_nbr
from wk_nbrs as fw
where 1=1
and fw.wk_nbr < 100
)
, emp_wk_cnt as
(
--Getting the running total count of emp_id by week
select a.emp_id
, b.wk_nbr
, count(*) over (partition by a.emp_id order by b.wk_nbr asc) as emp_wk_cnt
from @emp_ts as a
inner join wk_nbrs as b on cast(a.time_stamp as date) between b.wk_bgn and b.wk_end
group by a.emp_id
, b.wk_nbr
)
--pivoting the results out to final expected output
select post.emp_id
, post.wk2
, post.wk3
, post.wk4
from (
select a.emp_id
, 'wk' + cast(a.wk_nbr as varchar(10)) as wk_nbr
, a.emp_wk_cnt
from emp_wk_cnt as a
) as pre
pivot (sum(pre.emp_wk_cnt)
for pre.wk_nbr in
([wk2],[wk3],[wk4])
) post
order by post.emp_id
我们将不胜感激.
提前致谢.
推荐答案
我想这可能会给你你想要的:
I think this might give you what you are looking for:
;WITH
weekcounts AS (
SELECT Time_Stamp, Emp_ID, [Balance], ROUND(CONVERT(FLOAT,[Balance]) / 60.0,2) AS [Hours], DATEPART(week, Time_Stamp) AS int_week FROM newSampleData
)
,counts AS (
SELECT Emp_ID, int_week, 1 AS int_count
FROM weekcounts
WHERE ([Hours] >= 7.5)
UNION ALL
SELECT weekcounts.Emp_ID, weekcounts.int_week, 1 AS int_count
FROM weekcounts
INNER JOIN counts
ON weekcounts.Emp_ID = counts.Emp_ID
AND (weekcounts.int_week - 1) = counts.int_week
AND ([Hours] >= 7.5)
)
,countsagg AS (
SELECT Emp_ID, int_week, SUM(int_count) AS int_count
FROM counts
GROUP BY Emp_ID, int_week
)
SELECT Emp_ID,
ISNULL([29],0) AS [week 29],
ISNULL([30],0) AS [week 30],
ISNULL([31],0) AS [week 31],
ISNULL([32],0) AS [week 32],
ISNULL([33],0) AS [week 33]
FROM countsagg
PIVOT (MAX(int_count) FOR int_week IN ([29],[30],[31],[32],[33])) piv
此外，如果 Balance 是 varchar，您将进行比提供的代码中所需的更多转换.这将用更少的代码给出几个小时的结果:
Also if Balance is a varchar you are doing more converts than needed in your code provided. This will give the result for hours with less code:
ROUND(CONVERT(FLOAT,[Balance]) / 60.0,2)
这篇关于SQL Server 计算一个值在多个日期范围之间出现的次数并与前几周进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
