sql - Date Aging Report + Cummulative -

-

i working on aging report based on last action date stored in table, given table below:

requestno   usercode    lastactiondate      actiontype req1        407     12/14/2012 9:47     saved req1        407     12/14/2012 9:48     submitted req1        407     12/14/2012 9:48     approved req1        203     12/17/2012 9:54     reviewed req1        242     12/18/2012 10:29    wf setup in dev. req1        203     12/18/2012 15:14    transport prod. req1        242     12/18/2012 15:16    completed req2        407     12/27/2012 10:36    submitted req2        456     12/27/2012 11:18    approved req2        407     12/27/2012 11:27    approved req2        203     12/27/2012 17:34    reviewed req2        242     12/28/2012 14:07    wf setup in dev. req2        203     12/28/2012 14:11    transport prod. req2        242     12/28/2012 21:27    completed req3        407     12/27/2012 11:32    submitted req3        456     12/27/2012 11:33    approved req3        407     12/27/2012 11:34    approved req3        203     12/27/2012 17:33    reviewed req3        242     12/27/2012 18:33    wf setup in dev. req3        203     12/28/2012 13:43    transport prod. req3        242     1/3/2013 14:56      completed

i need generate aging table following columns requestno, usercode, receiveddate, lastactiondate, actiontype, aging (difference lastactiondate , receiveddate) cummulative.just table below

requestno   usercode    receiveddate        lastactiondate      actiontype          aging   cummulative req1        407         12/14/2012 9:47     12/14/2012 9:47     saved               0       0 req1        407         12/14/2012 9:47     12/14/2012 9:48     submitted           0       0 req1        407         12/14/2012 9:48     12/14/2012 9:48     approved            0       0 req1        203         12/14/2012 9:48     12/17/2012 9:54     reviewed            3       3 req1        242         12/17/2012 9:54     12/18/2012 10:29    wf setup in dev.    1       4 req1        203         12/18/2012 10:29    12/18/2012 15:14    transport prod.  0       4 req1        242         12/18/2012 15:14    12/18/2012 15:16    completed           0       4 req2        407         12/27/2012 10:36    12/27/2012 10:36    submitted           0       0 req2        456         12/27/2012 10:36    12/27/2012 11:18    approved            0       0 req2        407         12/27/2012 11:18    12/27/2012 11:27    approved            0       0 req2        203         12/27/2012 11:27    12/27/2012 17:34    reviewed            0       0 req2        242         12/27/2012 17:34    12/28/2012 14:07    wf setup in dev.    1       1 req2        203         12/28/2012 14:07    12/28/2012 14:11    transport prod.  0       1 req2        242         12/28/2012 14:11    12/28/2012 21:27    completed           0       1 req3        407         12/27/2012 11:32    12/27/2012 11:32    submitted           0       0 req3        456         12/27/2012 11:32    12/27/2012 11:33    approved            0       0 req3        407         12/27/2012 11:33    12/27/2012 11:34    approved            0       0 req3        203         12/27/2012 11:34    12/27/2012 17:33    reviewed            0       0 req3        242         12/27/2012 17:33    12/27/2012 18:33    wf setup in dev.    0       0 req3        203         12/27/2012 18:33    12/28/2012 13:43    transport prod.  1       1 req3        242         12/28/2012 13:43    1/3/2013 14:56      completed           6       7

are looking this?

with cte (   select *       (     select t.*, row_number() on (partition requestno order lastactiondate) rnum       table1 t   ) q ) select c1.requestno, c1.usercode,         coalesce(c2.lastactiondate, c1.lastactiondate) receiveddate,        c1.lastactiondate, c1.actiontype,         datediff(day, coalesce(c2.lastactiondate, c1.lastactiondate), c1.lastactiondate) aging   cte c1 left join cte c2     on c1.requestno = c2.requestno    , c1.rnum = c2.rnum + 1  order requestno, lastactiondate

output:

 | requestno | usercode |                    receiveddate |                  lastactiondate |         actiontype | aging | |-----------|----------|---------------------------------|---------------------------------|--------------------|-------| |      req1 |      407 | december, 14 2012 09:47:00+0000 | december, 14 2012 09:47:00+0000 |              saved |     0 | |      req1 |      407 | december, 14 2012 09:47:00+0000 | december, 14 2012 09:48:00+0000 |          submitted |     0 | |      req1 |      407 | december, 14 2012 09:48:00+0000 | december, 14 2012 09:48:00+0000 |           approved |     0 | |      req1 |      203 | december, 14 2012 09:48:00+0000 | december, 17 2012 09:54:00+0000 |           reviewed |     3 | |      req1 |      242 | december, 17 2012 09:54:00+0000 | december, 18 2012 10:29:00+0000 |   wf setup in dev. |     1 | |      req1 |      203 | december, 18 2012 10:29:00+0000 | december, 18 2012 15:14:00+0000 | transport prod. |     0 | |      req1 |      242 | december, 18 2012 15:14:00+0000 | december, 18 2012 15:16:00+0000 |          completed |     0 | |      req2 |      407 | december, 27 2012 10:36:00+0000 | december, 27 2012 10:36:00+0000 |          submitted |     0 | |      req2 |      456 | december, 27 2012 10:36:00+0000 | december, 27 2012 11:18:00+0000 |           approved |     0 | |      req2 |      407 | december, 27 2012 11:18:00+0000 | december, 27 2012 11:27:00+0000 |           approved |     0 | |      req2 |      203 | december, 27 2012 11:27:00+0000 | december, 27 2012 17:34:00+0000 |           reviewed |     0 | |      req2 |      242 | december, 27 2012 17:34:00+0000 | december, 28 2012 14:07:00+0000 |   wf setup in dev. |     1 | |      req2 |      203 | december, 28 2012 14:07:00+0000 | december, 28 2012 14:11:00+0000 | transport prod. |     0 | |      req2 |      242 | december, 28 2012 14:11:00+0000 | december, 28 2012 21:27:00+0000 |          completed |     0 | |      req3 |      407 | december, 27 2012 11:32:00+0000 | december, 27 2012 11:32:00+0000 |          submitted |     0 | |      req3 |      456 | december, 27 2012 11:32:00+0000 | december, 27 2012 11:33:00+0000 |           approved |     0 | |      req3 |      407 | december, 27 2012 11:33:00+0000 | december, 27 2012 11:34:00+0000 |           approved |     0 | |      req3 |      203 | december, 27 2012 11:34:00+0000 | december, 27 2012 17:33:00+0000 |           reviewed |     0 | |      req3 |      242 | december, 27 2012 17:33:00+0000 | december, 27 2012 18:33:00+0000 |   wf setup in dev. |     0 | |      req3 |      203 | december, 27 2012 18:33:00+0000 | december, 28 2012 13:43:00+0000 | transport prod. |     1 | |      req3 |      242 | december, 28 2012 13:43:00+0000 |  january, 03 2013 14:56:00+0000 |          completed |     6 |

here sqlfiddle demo


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