StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Here's my shot at it. All of the components you need can be done in straight SQL.</p> <pre><code>select count(1) as total ,(fixedDuration / divisor) + adder as duration from ( select case/*(30s_increments_else_60s)*/when(callDuration<60)then(120)else(60)end as divisor ,case/*(increment_by_1_else_2)*/when(callDuration<30)then(1)else(2)end as adder ,(/*duration_capped@600*/callDuration+600-ABS(callDuration-600))/2 as fixedDuration ,callDuration from callmetatbl where programid = 1001 and callDuration > 0 ) as foo group by (fixedDuration / divisor) + adder </code></pre> <p>Here's the SQL I used for testing. <em>(I don't have my own personal callmetatbl ;)</em></p> <pre><code>select count(1) as total ,(fixedDuration / divisor) + adder as duration from ( select case/*(30s_increments_else_60s)*/when(callDuration<60)then(120)else(60)end as divisor ,case/*(increment_by_1_else_2)*/when(callDuration<30)then(1)else(2)end as adder ,(/*duration_capped@600*/callDuration+600-ABS(callDuration-600))/2 as fixedDuration ,callDuration from -- callmetatbl -- using test view below ( select 1001 as programid, 0 as callDuration union select 1001 as programid, 1 as callDuration union select 1001 as programid, 29 as callDuration union select 1001 as programid, 30 as callDuration union select 1001 as programid, 59 as callDuration union select 1001 as programid, 60 as callDuration union select 1001 as programid, 119 as callDuration union select 1001 as programid, 120 as callDuration union select 1001 as programid, 179 as callDuration union select 1001 as programid, 180 as callDuration union select 1001 as programid, 239 as callDuration union select 1001 as programid, 240 as callDuration union select 1001 as programid, 299 as callDuration union select 1001 as programid, 300 as callDuration union select 1001 as programid, 359 as callDuration union select 1001 as programid, 360 as callDuration union select 1001 as programid, 419 as callDuration union select 1001 as programid, 420 as callDuration union select 1001 as programid, 479 as callDuration union select 1001 as programid, 480 as callDuration union select 1001 as programid, 539 as callDuration union select 1001 as programid, 540 as callDuration union select 1001 as programid, 599 as callDuration union select 1001 as programid, 600 as callDuration union select 1001 as programid,1000 as callDuration ) as callmetatbl where programid = 1001 and callDuration > 0 ) as foo group by (fixedDuration / divisor) + adder </code></pre> <p>The SQL output is shown below, as 2 records counted for each duration (bucket) 1 through 12.</p> <pre><code>total duration 2 1 2 2 2 3 2 4 2 5 2 6 2 7 2 8 2 9 2 10 2 11 2 12 </code></pre> <p>Here are the results from the "foo" sub-query:</p> <pre><code>divisor adder fixedDuration callDuration 120 1 1 1 120 1 29 29 120 2 30 30 120 2 59 59 60 2 60 60 60 2 119 119 60 2 120 120 60 2 179 179 60 2 180 180 60 2 239 239 60 2 240 240 60 2 299 299 60 2 300 300 60 2 359 359 60 2 360 360 60 2 419 419 60 2 420 420 60 2 479 479 60 2 480 480 60 2 539 539 60 2 540 540 60 2 599 599 60 2 600 600 60 2 600 1000 </code></pre> <p>Cheers.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload