sql - Get Time difference between group of records -

-

i have table following structure

id         activitytime             status 19        2013-08-23 14:52            1 19        2013-08-23 14:50            1 19        2013-08-23 14:45            2 19        2013-08-23 14:35            2 19        2013-08-23 14:32            1 19        2013-08-23 14:30            1 19        2013-08-23 14:25            2 19        2013-08-23 14:22            2 53        2013-08-23 14:59            1 53        2013-08-23 14:56            1 53        2013-08-23 14:57            1 53        2013-08-23 14:52            2 53        2013-08-23 14:50            2 53        2013-08-23 14:49            2 53        2013-08-23 14:18            2 53        2013-08-23 14:30            1

i want calculate total time difference each id against status 2. example id 19 stayed on status 2 14:35 14:50 (15 minutes), 14:22 14:30 (8 minutes). total id 19 stayed on status 2 23 minutes. (in both cases considered minimum time status 2 status 1) the problem is how include difference between status different in next row.

for example exclude first record in table status 1 , pick second row. same case status 2. pick minimum time, calculate difference , add them multiple groups of status against each channel.

i have tried using cursor can't seem make work. pretty confident can achieve in c# after getting data , looping through it. wondering if there direct way of getting information without loop. created sql fiddle data, can't seem make work. coming c# background basic knowledge of sql. glad if can me out.

cte (common table expressions) can used specialized temp table. allows (in case) dynamically create row number can later create self join on.

i think looking this:

--create temp table select 19 id,'2013-08-23 14:52' activitytime,1 status #temp union select 19,'2013-08-23 14:50',1 union select 19,'2013-08-23 14:45',2 union select 19,'2013-08-23 14:35',2 union select 19,'2013-08-23 14:32',1 union select 19,'2013-08-23 14:30',1 union select 19,'2013-08-23 14:25',2 union select 19,'2013-08-23 14:22',2 union select 53,'2013-08-23 14:59',1 union select 53,'2013-08-23 14:56',1 union select 53,'2013-08-23 14:57',1 union select 53,'2013-08-23 14:52',2 union select 53,'2013-08-23 14:50',2 union select 53,'2013-08-23 14:49',2 union select 53,'2013-08-23 14:18',2 union select 53,'2013-08-23 14:30',1  --build cte table ;with cte ( select      *,     row_number() on (order  id, activitytime) rownum      #temp )  --query cte table, self joining row 1 row 2 compare. select a.id, sum(datediff(minute,b.activitytime,a.activitytime)) totaltime   cte  left outer join cte b     on a.rownum = b.rownum + 1 , a.id = b.id b.status = 2 group a.id

Comments

Post a Comment

Popular posts from this blog

java - Run a .jar on Heroku -

-
i trying run .jar file on heroku, , can't find instructions anywhere on how this. have read wants me compile uploading heroku. possible, , if is, how this? if helps, trying run minecraft craftbukkit server, , don't have pom.xml file yet. you didn't provide details i'll try give , people reaches question way it. first of all, create heroku account , create heroku application (assumed have done that). second, need generate application jar , upload heroku. so, open terminal , install heroku cli (assuming unix terminal): $ sudo npm install -g heroku-cli you need have npm , nodejs installed, can check link: https://www.digitalocean.com/community/tutorials/how-to-install-node-js-on-ubuntu-16-04 once done that, can use npm update node version acceptable heroku-cli: $ sudo npm install -g n $ sudo n 7.10.0 and run: $ sudo npm install -g heroku-cli in order deploy jars, need install deploy plugin: $ sudo heroku plugins:install heroku-cli-deploy ...
Read more

java - Jtable duplicate Rows -

-
Image
when add new rows in jtable duplicate rows many times this method update jtable public final void tableaucomande(){ ((defaulttablemodel)tabcommande.getmodel()).getdatavector().clear(); session s=hibernateutil.getsessionfactory().opensession(); criteria cr1= s.createcriteria(commande.class); list <commande> lcs=cr1.list() ; criteria cr2= s.createcriteria(lignecommande.class); list <lignecommande> lck=cr2.list() ; if(lcs.size()!=0 && lck.size()!=0) { (commande lc:lcs) { for(lignecommande lig:lck){ ((defaulttablemodel)tabcommande.getmodel()).addrow(new object[]{ lc.getid_cmd(),lc.getnumcmd(),lc.getdate_cmd(),lc.getclient().getnomprenom(), lig.getproduit().getlib_prd(),lig.getquantite(),lig.getproduit().getid_prd() }); } } // tabcommande.setrowselectioninterval(tabcommande.getrowcount()-1,tabcommande.getrowcount()-1); } } this code add add data rows displaye...
Read more

validation - How to pass paramaters like unix into windows batch file -

-
i need pass various parameters .cmd file unix format file.cmd -configuration=value -source=value -flag but, try this: startlocal @echo off cls setlocal set cmdline=%* set configuration= set source= set badargs= set validation= goto main :splitargs echo splitargs(%*) if "%*" neq "" ( /f "tokens=1,2,* delims== " %%i in ("%*") call :assignkeyvalue %%i %%j & call :splitargs %%k ) goto :eof :assignkeyvalue echo assignkeyvalue(%1, %2) if /i %1==-configuration ( set configuration=%2 ) else if /i %1==-source ( set source=%2 ) else ( rem append unrecognised [key,value] badargs echo unknown key %1 set badargs=%badargs%[%1, %2] ) goto :eof :validate echo validating set validation=fail if defined configuration ( echo -configuration ok if defined source ( echo -source ok if not defined badargs ( set validation=success ) ) ) goto :eof :main cl...
Read more