代码之家 › 专栏 › 技术社区 › JK.

SQL Server-根据周数获取一周内的第一个日期?

bugtracker.net week-number tsql sql-server

4

JK. · 技术社区 · 16 年前

我有一个查询(在bug tracker.net中使用),它按状态计算每周的bug数。但是查询返回周数,我真正想要的是一周的第一个日期

select datepart(wk, DateAdd(day, 0, DateDiff(day, 0, bg_reported_date)))
       as [week], bg_status , st_name as [status], count(*) as [count] 
  from bugs inner join statuses on bg_status = st_id 
 group by datepart(wk, DateAdd(day, 0, DateDiff(day, 0, bg_reported_date))),
          bg_status, st_name
 order by [week], bg_status

得到周数的部分是

datepart(wk, DateAdd(day, 0, DateDiff(day, 0, bg_reported_date))) as [week]

它返回这个输出:

week        bg_status   status                                        count
----------- ----------- --------------------------------------------- ------
22          1           new                                           1
22          5           closed                                        32

但最好是说每星期的第一天,如2010年1月1日,然后2010年8月1日,等等

问题不是重复的 How do you get the "week start date" and "week end date" from week number in SQL Server? (回答说如何从日期而不是从周数开始一周)

不是的副本 Calculate date from week number (问题问C)

不是的副本 Get first date of week from provided date (问题要求使用javascript)

我进行了搜索,但找不到针对SQL Server的此问题的答案(如果重要,请参阅2010)

2 回复 | 直到 11 年前

1

5

Community Mohan Dere 9 年前

如果你想得对,答案是 SO 1267126 可以应用于您的问题。

每个错误都报告了组中的日期映射到同一周。因此,根据定义,每个错误日期也必须映射到一周的同一开始。因此,您对bug报告日期以及周数计算运行“从给定日期开始的一周的开始”计算,并按两个表达式(适度地可怕)分组,最后得到您所寻求的答案。

SELECT DATEPART(wk, DATEADD(day, 0, DATEDIFF(d, 0, bg_reported_date))) [week],
       DATEADD(dd, -(DATEPART(dw, bg_reported_date)-1), bg_reported_date)
       AS [weekstart], bg_status, st_name AS [status], COUNT(*) AS [count] 
  FROM bugs INNER JOIN statuses ON bg_status = st_id 
 GROUP BY DATEPART(wk, DATEADD(day, 0, DATEDIFF(day, 0, bg_reported_date))),
       DATEADD(dd, -(DATEPART(dw, bg_reported_date)-1), bg_reported_date),
       bg_status, st_name
 ORDER BY [week], bg_status

自从 bg_reported_date 是日期时间(请参见注释;它包含时间组件),在确定周开始之前必须将其强制转换为日期(但周数表达式不需要强制转换,周开始表达式的“星期几”部分也不需要强制转换):

SELECT DATEPART(wk, DATEADD(day, 0, DATEDIFF(d, 0, bg_reported_date))) [week],
       DATEADD(dd, -(DATEPART(dw, bg_reported_date)-1),
               CAST(bg_reported_date AS DATE)) AS [weekstart],
       bg_status, st_name AS [status], COUNT(*) AS [count] 
  FROM bugs INNER JOIN statuses ON bg_status = st_id 
 GROUP BY DATEPART(wk, DATEADD(day, 0, DATEDIFF(day, 0, bg_reported_date))),
       DATEADD(dd, -(DATEPART(dw, bg_reported_date)-1),
               CAST(bg_reported_date AS DATE),
       bg_status, st_name
 ORDER BY [week], bg_status

注意:未测试代码!

2

0

Community Mohan Dere 9 年前

我意识到这是一条非常古老的线索,但“一周内第一次约会,给定周数”正是我想要做的,我没有实际的工作日期,所以接受的答案对我来说不起作用。我想我会把我的解决方案张贴给子孙后代。请注意,我怀疑不同的文化设置可能会破坏此设置,因此请在使用前进行测试。

我的答案是从 this 一个。

假设你知道一周数和一年,你想知道那一周的开始和结束日期。以下是我的资料:

--These 2 "declared" variables would be passed in somehow
declare @WeekNumber int = DATEPART(wk, GETDATE())
declare @ForYear int = YEAR(GETDATE())-1

--Since we don't have a raw date to work with, I figured I could just start with 
--Jan 1 of that year.  I'll store that date in a cte here, but if you are doing this
--in a stored proc or function, it would make much more sense to use another @variable
;with x as
(
    --this method works in SQL 2008:
    SELECT CONVERT(DateTime, ('1/1/' + CONVERT(varchar, @ForYear))) as Jan1ForSelectedYear
    --If you are using 2014 or higher, you can use this instead:
    --DATETIME2FROMPARTS(@ForYear, 1, 1, 0,0,0,0,0)
)
--Now that we have a date to work with, we'll just add the number of weeks to that date
--That will bring us to the right week number of the given year.
--Once we have THAT date, we can get the beginning and ending of that week
--Sorry to make you scroll, but I think this is easier to see what is going on this way
SELECT  CONVERT(varchar(50), DateAdd(wk, (@WeekNumber - 1), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, x.Jan1ForSelectedYear) - 6, x.Jan1ForSelectedYear))), 101) as FirstDayOfWeekXForSelectedYear,
        CONVERT(varchar(50), DateAdd(wk, (@WeekNumber - 1), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, x.Jan1ForSelectedYear)    , x.Jan1ForSelectedYear))), 101) as LastDayOfWeekXForSelectedYear
FROM x