sql server - SQL to calculate number of tasks open at a given time -


i have nice query calculates how many tasks have been opened , closed in given week works charm, i'm having difficulties extending can show me how many tasks we're still open @ end of week.

in mind, sql need count total number of issues have been opened beginning of time, week , same number of closed , subtract 2 each other limited knowledge of sql, i'm struggling how write falls outside of group clause.

this sql have:

select  isnull(a.[year],b.[year]) [year],         isnull(a.[week],b.[week]) [week],         isnull(a.opened,0) opened,         isnull(b.closed,0) closed, a.totresponse, a.totcompletion (  select  year(insert_time) [year],                 datepart(week,insert_time) [week],                 count(id) opened, sum(timer2) totresponse, sum(timer3) totcompletion         service_req         [insert_time] not null , sr_type=1         group year(insert_time), datepart(week,insert_time)) full join ( select  year(close_time) [year],                     datepart(week,close_time) [week],                     count(id) closed             service_req              [close_time] not null , sr_type=1             group year(close_time), datepart(week,close_time)) b     on a.[year] = b.[year] , a.[week] = b.[week] order [year], [week]

if there's me this, appreciated.

what make query easier cumulative sum, alas introduced in sql server 2012. in case, can emulate using row_number(), because want cumulative count.

so, idea calculate cumulative number of opens , closes , aggregation. max() of cumulative numbers number @ end of week. in thinking this, i've rewritten query using union all rather full outer join:

with oc (       select cast(insert_time date) thedate, 1 opened, null closed,              timer2 response, timer3 completion,              row_number() on (order insert_time) cumopened, null cumclosed       service_req       insert_time not null , sr_type = 1       union       select cast(close_time date), null opened, 1 closed,              null response, null completion,              null cumopened, row_number() on (order close_time) cumclosed       service_req       close_time not null , sr_type = 1      ) select year(thedate) [year],        datepart(week, thedate) [week],        coalesce(sum(opened), 0) opened, coalesce(sum(closed), 0) closed,        max(response) response, max(completion) completion,        coalesce(max(cumopened), 0) - coalesce(max(cumclosed), 0) stillopened oc group year(thedate), datepart(week, thedate) order year(thedate), datepart(week, thedate);

you rewrite query using same idea, subquery calculate cumulative numbers.


Comments

Post a Comment

Popular posts from this blog

android - Get AccessToken using signpost OAuth without opening a browser (Two legged Oauth) -

i using signpost oauth access data magento server. have read various tutorials on same , reach point open browser user can enter credentials. however, per requirement have automate part. hence, user should not browser page. have set-up done on server side ( magento ), hit url , returned call page. same through program in android. i have tried below, consumer = new commonshttpoauthconsumer(key, secret); provider = new commonshttpoauthprovider(oauth_init_url,access_token_url, authorize_url); try { provider.retrieverequesttoken(consumer, oauth.out_of_band); log.d("tokens" , consumer.gettoken() + " -- " + consumer.gettokensecret()); } and request tokens. dont know how bypass next step. tried directly accessing accesstoken (stupid of me) provider.retrieveaccesstoken(consumer, "mycallback://callback"); no luck, ends in oauth.signpost.exception.oauthnotauthorizedexception: authorization failed (server replied 401). ...
Read more

org.mockito.exceptions.misusing.InvalidUseOfMatchersException: mockito -

getting error org.mockito.exceptions.misusing.invaliduseofmatchersexception: invalid use of argument matchers! 1 matchers expected, 2 recorded. exception may occur if matchers combined raw values: //incorrect: somemethod(anyobject(), "raw string"); when using matchers, arguments have provided matchers. example: //correct: somemethod(anyobject(), eq("string matcher")); at when( getprogramservice .callservice(any(getprogramtype.class))) .thenreturn(jaxbresponse); please explain error , possible resolution the code posted looks fine; don't see problem it. code before or above causing exception. key here: invalid use of argument matchers! 1 matchers expected, 2 recorded. any , used above, doesn't return "special kind of null" or "special kind of getprogramtype"; don't exist. instead, returns null , side effect puts matcher on stack. whe...
Read more

google shop client API returns 400 bad request error while adding an item -

i'm working on solution add items in google shopping market java application. following tutorial real mess because of missing classes of laste release, after setting 1.5-beta maven managed make base example typecheck correctly classes. still can make simple task of adding 1 item. instead of posting whole code i'm posting part creates product feed. private product createproduct(string id) { product product = new product(); product.title = "red wool sweater"; product.content = new content( "text", "comfortable , soft, sweater keep warm on " + "cold winter nights. red , blue stripes."); product.appcontrol = new appcontrol(); product.appcontrol.addrequireddestination("productads"); product.externalid = id; product.lang = "it"; product.country = "it"; product.condition = "nuovo"; product.price = new price("eu...
Read more