sql server - Microsoft SQL Calculating Backlog -

i calculate backlog every week in past month. date format in (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 

i want result set this:

weeknum | opened | closed | total open --------------------------------------    1        4        3         4    <= (2-4)+ data in week 2 (2-4)+(1-2)+7    2        4        2         6    <= (1-2)+7               3        2        1         7    <= total count                    

my code below not sure how query last part. not sure if possible or not.

with  issued_queries (      select datepart(wk, issued_date) 'week_number'            ,count(queryno_i) 'opened'      t.tech_query      dateadd(d,-12,issued_date) > getdate()-40      group datepart(wk, issued_date) ), closed_queries (     select datepart(wk, mutation) 'week_number'           ,count(queryno_i) 'closed'     t.tech_query     status=3 , dateadd(d,-12,issued_date) > getdate()-40     group datepart(wk, mutation) ), total (     select count(*) 'total'     t.tech_query     status!='3' )  select issued_queries.week_number      , issued_queries.opened      , closed_queries.closed issued_queries join closed_queries   on (issued_queries.week_number = closed_queries.week_number)   order week_number 

backlog every week in past month. i've taken mean last 4 weeks, appears doing. assuming "mutation" represents date record updated (maybe set closed).

so first, generate list of dates, way there answer week number x if there no new/closed records.

declare @sundayjustgone datetime  -- need rid of time component, done through convert. set @sundayjustgone = convert(date, dateadd(d, 1-datepart(dw, getdate()), getdate())) -- if earlier sql 2008, can rid of time component through: set @sundayjustgone = select dateadd(dd, 0, datediff(dd, 0, @sundayjustgone))  ;with  last4weeks ( -- sunday of week gone. select @sundayjustgone sundaydate -- sunday gone  union  select dateadd(d, -7, sundaydate) -- previous sunday last4weeks dateadd(d, -7, sundaydate) > dateadd(wk, -4, @sundayjustgone) -- new date not more 4 weeks old ) select a.sundaydate,      datepart(wk, dateadd(d, -1, a.sundaydate)) week_number, -- sql considers sunday first day of week, need move 1 day right week     (select count(*)          t.tech_query          issued_date between dateadd(d, -6, a.sundaydate) , a.sundaydate -- issued week. (between monday - sunday)         ) opened,     (select count(*)          t.tech_query          status = 3 -- closed         , mutation between dateadd(d, -6, a.sundaydate) , a.sundaydate -- , mutation week. (between monday - sunday)         ) closed,     (select count(*)          t.tech_query          (status != 3 or datediff(d, mutation, a.sundaydate) < 0 ) -- still open, or closed after week.         , datediff(d, issued_date, a.sundaydate) >= 0 -- , issued on or before sunday.     ) totalopen last4weeks 

hopefully helps.

the results different yours, assume monday first day of week. change start of week sunday, saturday needs considered end of week, so, change set @sundayjustgone = convert(date, dateadd(d, 1-datepart(dw, getdate()), getdate())) set @sundayjustgone = convert(date, dateadd(d, -datepart(dw, getdate()), getdate())) (1 removed)