sql server 2008 - SQL Query to calculate median and group by -


i have following table. 

declare @tbl_result table (        id varchar(10),     createdate datetime,     people_code_id varchar(10),      conversion_date datetime,     campus varchar(20),     days_took int    );

this table has records january 01,2013 date of leads received , converted.

i needed find median time took convert leads arrived in last 10 weeks , group them campus able using sql query below

with    cte_result           ( select   *                    @tbl_result                   createdate > dateadd(week, -10, getdate())              )     select  campus ,             avg(days_took) mediantime        ( select    campus ,                         days_took ,                         row_number() on ( partition campus order days_took asc ) agerank ,                         count(*) on ( partition campus ) campuscount                    cte_result             ) x       x.agerank in ( x.campuscount / 2 + 1, ( x.campuscount + 1 ) / 2 )     group x.campus

i need plot trend on graph i.e. find records previous 10 weeks buckets , plot median on line chart - each line 1 campus. (grouped campus)

is cursor option? find leads of first 10 week starting jan 01, above sql query median, push temp table , find next 10 weeks , on.

or there better can do?

without trying optimise query, if need produce same result across multiple 10-week periods, can expand current (10 week ago today) ranges many ranges required, threading periodenddate throughout query shown below.

sql fiddle

ms sql server 2012 schema setup:

query 1:

declare @tbl_result table (        id varchar(10),     createdate datetime,     people_code_id varchar(10),      conversion_date datetime,     campus varchar(20),     days_took int    );  -- fill table dummy data 2013-01-01 insert @tbl_result (createdate, campus, days_took) select dateadd(d, a.number, '20130101'), 'campus' + right(b.number, 10),        abs(cast(newid() binary(6)) % 130) + 1 master..spt_values join master..spt_values b on b.type='p' , b.number < 50 -- 50 campuses a.type='p'   , dateadd(d, a.number, '20130101') <= getdate();  -- first cte used create required number of 10-week periods n(number) (   select 0   union   select number+1 n   number <= datediff(week, '20130101', getdate()) ), -- , below here it's query periodenddate threaded through cte_result (   select   dateadd(week, -number, getdate()) periodenddate,            t.*       @tbl_result t   cross    join     n            -- see range built dynamically here      createdate > dateadd(week, -number-10, getdate())     ,    createdate < dateadd(week, -number, getdate()) +1 ) select  periodenddate, campus ,         avg(days_took) mediantime    (          select periodenddate,   campus ,          days_took ,          row_number() on ( partition periodenddate, campus order days_took asc ) agerank ,          count(*) on ( partition periodenddate, campus ) campuscount               cte_result         ) x   x.agerank in ( x.campuscount / 2 + 1, ( x.campuscount + 1 ) / 2 ) group x.periodenddate, x.campus order x.periodenddate, x.campus;

Comments

Post a Comment

Popular posts from this blog

node.js - Bad Request - node js ajax post -

i'm using express 3.0 , node v0.11.0. have button submits form , i'm using ajax post data json object node server. client code is: $('#contact').submit(function(e) { console.log('submit called'); var formdata = json.stringify($('form').serializeobject()); console.log(formdata); $.ajax({ url: "http://localhost:3000/savedata/", type: "post", datatype: 'json', data: {objectdata: formdata}, contenttype: "application/json", complete: function() { console.log('process complete'); }, success: function(data) { console.log('process sucess'); }, error: function() { console.log('process error'); }, }); return false; }); then on server: var express = require('express'); var app = express(); app.set('views', __dirname + '/views'); app.set('view engine', 'jade'); app.use(express.bodyparser());...
Read more

Why does Ruby on Rails generate add a blank line to the end of a file? -

running rails generate controller foo home results in: class foocontroller < application controller def home end end (there's nothing on line in actual file, blank line) is there purpose blank line? it's common use newline indicate end of line, last line. many editors (like vi, use) add in newline silently. consensus text files (especially in unix world) should end in newline, , there have been problems historically if wasn't present. why should text files end newline? the tool used count lines in file "wc" counts newlines in file, without trailing newline show 3 instead of 4. it improves readability of templates used in generator. consider: https://github.com/rails/rails/blob/master/railties/lib/rails/generators/rails/controller/templates/controller.rb to remove trailing newline, template have last line of: end<% end -%> instead of: end <% end -%> that seems less readable me. the discussion below refers ...
Read more

keyboard - Smiles and long press feature in Android -

i've created android keyboard. how add features inserting smilies, , using long keypresses display symbols? to create long-press behavior allows select several options pop-up menu, follow guide (specifically pop-up menu section): http://developer.android.com/guide/topics/ui/menus.html smileys (also known emoticons) combination of keys, understood programs , parsed same way. have do, when clicks smiley face on keyboard, simulate pressing of key combination matching emoticon. example, when clicks smiley, should simulate key presses ':' , ')' :). here's list of emoticons: http://en.wikipedia.org/wiki/list_of_emoticons
Read more