Microsoft计算待办事项

Question

我想计算一下过去一个月每周的积压量。日期格式为(MM/DD/YY)

| mutation | issued_date | queryno_i | status |
-----------------------------------------------
  01/05/14   12/31/13       321         OPEN
  01/02/14   08/01/13       323         CLOSED
  01/01/14   06/06/13       123         OPEN
  01/01/14   01/01/14       1240        CLOSED
  01/02/14   01/01/14       1233        OPEN
  01/03/14   01/03/14       200         CLOSED
  01/05/14   01/04/14       300         OPEN
  01/06/14   01/05/14       231         OPEN
  01/07/14   01/06/14       232         CLOSED
  01/09/14   01/10/14       332         OPEN
  01/11/14   01/11/14       224         CLOSED
  01/15/14   01/14/14       225         CLOSED
  01/16/14   01/15/14       223         OPEN

我希望我的结果集看起来像这样：

WeekNum | Opened | Closed | Total Open
--------------------------------------
   1        4        3         4    <= (2-4)+ data in week 2 so (2-4)+(1-2)+7
   2        4        2         6    <= (1-2)+7           
   3        2        1         7    <= total count                   

我的代码在下面，但我不知道如何查询最后一部分。我甚至不知道这是否可能。

WITH 
issued_queries AS
(
     SELECT DATEPART(wk, issued_date) AS 'week_number'
           ,COUNT(queryno_i) AS 'opened'
     FROM t.tech_query
     WHERE DATEADD(D,-12,issued_date) > GETDATE()-40
     GROUP BY DATEPART(wk, issued_date)
),
closed_queries AS
(
    SELECT DATEPART(wk, mutation) AS 'week_number'
          ,COUNT(queryno_i) AS 'closed'
    FROM t.tech_query
    WHERE status=3 AND DATEADD(D,-12,issued_date) > GETDATE()-40
    GROUP BY DATEPART(wk, mutation)
),
total as
(
    SELECT COUNT(*) AS 'total'
    FROM t.tech_query
    WHERE status!='3'
)

SELECT issued_queries.week_number
     , issued_queries.opened
     , closed_queries.closed
FROM issued_queries JOIN closed_queries
  ON (issued_queries.week_number = closed_queries.week_number)
  ORDER BY week_number

Answer 1

在过去的一个月里每周的积压。我认为这意味着过去4周，因为这似乎是你正在做的事情。假设“突变”代表更新记录的日期(可能设置为关闭)。

因此，首先，我生成一个日期列表，这样即使没有新的/封闭的记录，也会有第X周的答案。

declare @SundayJustGone datetime

-- We need to get rid of the time component, done through convert.
set @SundayJustGone = convert(date, dateadd(d, 1-DATEPART(dw, getdate()), getdate()))
-- If earlier than sql 2008, can get rid of time component through: set @SundayJustGone = SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @SundayJustGone))

;with 
Last4Weeks as
(
-- Get the sunday of the week just gone.
select @SundayJustGone as SundayDate -- Sunday just gone

union all

select dateadd(d, -7, SundayDate) -- Get the previous Sunday
from Last4Weeks
where dateadd(d, -7, SundayDate) > dateadd(Wk, -4, @SundayJustGone) -- where the new date is not more than 4 weeks old
)
select A.SundayDate, 
    DATEPART(wk, DateAdd(d, -1, A.SundayDate)) as Week_Number, -- SQL considers Sunday the first day of the week, so we need to move it back 1 day to get the right week
    (select count(*) 
        from t.tech_query 
        where issued_date between DateAdd(d, -6, A.SundayDate) and A.SundayDate -- Was issued this week. (between monday - sunday)
        ) as Opened,
    (select count(*) 
        from t.tech_query 
        where status = 3 -- where it is closed
        and mutation between DateAdd(d, -6, A.SundayDate) and A.SundayDate -- and the mutation was this week. (between monday - sunday)
        ) as Closed,
    (select count(*) 
        from t.tech_query 
        where (status != 3 or datediff(d, mutation, A.SundayDate) < 0 ) -- Is still open, or was closed after this week.
        and datediff(d, issued_date, A.SundayDate) >= 0 -- and it was issued on or before the sunday.
    ) as TotalOpen
from Last4Weeks as A

希望这能帮上忙。

结果和你的不一样，因为我认为星期一是一周的第一天。若要将周初改为周日，需要考虑周末的问题，因此，将set @SundayJustGone = convert(date, dateadd(d, 1-DATEPART(dw, getdate()), getdate()))更改为set @SundayJustGone = convert(date, dateadd(d, -DATEPART(dw, getdate()), getdate())) (删除1)