sql - Merge date ranges -

-

oracle sql newbie here , first time poster.

i thought simple until realized can't figure out how split return assignments.

here assignments table: asgn

id  st_dt       end_dt      pos    locn  status  wage_cd --  ----------  ----------  -----  ----  ------  -------   12-31-2006  08-16-2009  clerk  lax   3       a   08-17-2009  10-04-2009  clerk  lax   0       z   10-05-2009  06-30-2010  opr    nyc   3       a   07-01-2010  12-31-2010  opr    nyc   3       b   01-01-2011  06-30-2012  opr    nyc   3       c   07-01-2012  04-09-2013  opr    nyc   3       d   04-10-2013  06-30-2013  clerk  lax   3       a   07-01-2013  08-10-2014  clerk  lax   3       b   07-01-2013  08-10-2014  clerk  lax   3       c b   04-10-2013  05-31-2013  sup    lax   3       b   06-01-2013  06-30-2014  sup    lax   0       z b   07-01-2013  08-10-2014  sup    lax   3       b b   08-11-2014  08-11-2014  clerk  nyc   3       b   08-12-2014  01-11-2015  sup    lax   3       b   01-12-2015  02-10-2016  sup    lax   3       b b   02-11-2016  08-12-2016  oper   sfo   3       b   02-11-2016  08-12-2016  oper   sfo   3       b

i've tried below unexpected results.

select * (    select id          ,min(st_dt) st_dt          ,max(end_dt) end_dt          ,pos          ,locn          ,status    asgn    group id, locn, pos, status ) subqry order id, st_dt

unexpected results, makes sense. return assignments not separated combined previous assignments.

id  st_dt       end_dt      pos    locn  status --  ----------  ----------  -----  ----  ------   12-31-2006  08-10-2014  clerk  lax   3   08-17-2009  10-04-2009  clerk  lax   0   10-05-2009  04-09-2010  opr    nyc   3 b   04-10-2013  02-10-2015  sup    lax   3 b   06-01-2013  06-30-2014  sup    lax   0 b   08-11-2014  08-11-2014  clerk  nyc   3 b   02-11-2016  08-12-2016  oper   sfo   3

and results see adjacent dates combined per id, position, location, , status:

id  st_dt       end_dt      pos    locn  status --  ----------  ----------  -----  ----  ------   12-31-2006  08-16-2009  clerk  lax   3   08-17-2009  10-04-2009  clerk  lax   0   10-05-2009  04-09-2010  opr    nyc   3   04-10-2013  08-10-2014  clerk  lax   3 b   04-10-2013  05-31-2013  sup    lax   3 b   06-01-2013  06-30-2014  sup    lax   0 b   07-01-2013  08-10-2014  sup    lax   3 b   08-11-2014  08-11-2014  clerk  nyc   3 b   08-12-2014  02-10-2015  sup    lax   3 b   02-11-2016  08-12-2016  oper   sfo   3

i asked more season oracle sql programmer , said i'd have plsql, i'm thinking there has way make work via sql.

test setup script:

create table asgn (id varchar2(10) ,st_dt date ,end_dt date ,pos varchar2(10) ,locn varchar2(10) ,status number ,wage_cd varchar2(10));  insert asgn values('a',to_date('12-31-2006','mm-dd-yyyy'),to_date('08-16-2009','mm-dd-yyyy'),'clerk','lax',3,'a'); insert asgn values('a',to_date('08-17-2009','mm-dd-yyyy'),to_date('10-04-2009','mm-dd-yyyy'),'clerk','lax',0,'z'); insert asgn values('a',to_date('10-05-2009','mm-dd-yyyy'),to_date('06-30-2010','mm-dd-yyyy'),'opr','nyc',3,'a'); insert asgn values('a',to_date('07-01-2010','mm-dd-yyyy'),to_date('12-31-2010','mm-dd-yyyy'),'opr','nyc',3,'b'); insert asgn values('a',to_date('01-01-2011','mm-dd-yyyy'),to_date('06-30-2012','mm-dd-yyyy'),'opr','nyc',3,'c'); insert asgn values('a',to_date('07-01-2012','mm-dd-yyyy'),to_date('04-09-2013','mm-dd-yyyy'),'opr','nyc',3,'d'); insert asgn values('a',to_date('04-10-2013','mm-dd-yyyy'),to_date('06-30-2013','mm-dd-yyyy'),'clerk','lax',3,'a'); insert asgn values('a',to_date('07-01-2013','mm-dd-yyyy'),to_date('08-10-2014','mm-dd-yyyy'),'clerk','lax',3,'b'); insert asgn values('a',to_date('07-01-2013','mm-dd-yyyy'),to_date('08-10-2014','mm-dd-yyyy'),'clerk','lax',3,'c'); insert asgn values('b',to_date('04-10-2013','mm-dd-yyyy'),to_date('05-31-2013','mm-dd-yyyy'),'sup','lax',3,'a'); insert asgn values('b',to_date('06-01-2013','mm-dd-yyyy'),to_date('06-30-2014','mm-dd-yyyy'),'sup','lax',0,'z'); insert asgn values('b',to_date('07-01-2013','mm-dd-yyyy'),to_date('08-10-2014','mm-dd-yyyy'),'sup','lax',3,'b'); insert asgn values('b',to_date('08-11-2014','mm-dd-yyyy'),to_date('08-11-2014','mm-dd-yyyy'),'clerk','nyc',3,'a'); insert asgn values('b',to_date('08-12-2014','mm-dd-yyyy'),to_date('01-11-2015','mm-dd-yyyy'),'sup','lax',3,'a'); insert asgn values('b',to_date('01-12-2015','mm-dd-yyyy'),to_date('02-10-2016','mm-dd-yyyy'),'sup','lax',3,'b'); insert asgn values('b',to_date('02-11-2016','mm-dd-yyyy'),to_date('08-12-2016','mm-dd-yyyy'),'oper','sfo',3,'a'); insert asgn values('b',to_date('02-11-2016','mm-dd-yyyy'),to_date('08-12-2016','mm-dd-yyyy'),'oper','sfo',3,'b');

employee has 2 rows 07-01-2013 through 08-10-2014; assumed mistake , deleted 1 of rows.

other that, application of "tabibitosan method" solving "gaps , islands" problems date ranges. trick in creating "groups" (gp) adjacent intervals.

with      prep ( id, st_dt, end_dt, gp, pos, locn, status ) (        select id, st_dt, end_dt,                end_dt - sum( end_dt - st_dt + 1 ) on (partition id, pos, locn, status                                                         order st_dt),               pos, locn, status          asgn      ) select id, min(st_dt) st_dt, max(end_dt) end_dt, pos, locn, status   prep group id, gp, pos, locn, status order id, st_dt ;    id         st_dt      end_dt     pos        locn           status ---------- ---------- ---------- ---------- ---------- ----------          12-31-2006 08-16-2009 clerk      lax                 3          08-17-2009 10-04-2009 clerk      lax                 0          10-05-2009 04-09-2013 opr        nyc                 3          04-10-2013 08-10-2014 clerk      lax                 3 b          04-10-2013 05-31-2013 sup        lax                 3 b          06-01-2013 06-30-2014 sup        lax                 0 b          07-01-2013 08-10-2014 sup        lax                 3 b          08-11-2014 08-11-2014 clerk      nyc                 3 b          08-12-2014 02-10-2016 sup        lax                 3 b          02-11-2016 08-12-2016 oper       sfo                 3   10 rows selected

Comments

Post a Comment

Popular posts from this blog

mysql - Dreamhost PyCharm Django Python 3 Launching a Site -

-
i'm new python/django , created simple django website using pycharm. site works fine running on computer, i'm lost when comes taking site onto host (dreamhost). i'm able upload files correct directories fine, , believe made correct changes database on mysql. i'm not sure go there though. right now, page shows files rather site. direction appreciated. much! you should not run django application using python manage.py runserver on production. how run application on django depends on server (apache, nginx) want use for apache - https://docs.djangoproject.com/en/1.10/howto/deployment/wsgi/modwsgi/ for nginx - https://www.digitalocean.com/community/tutorials/how-to-serve-django-applications-with-uwsgi-and-nginx-on-ubuntu-14-04 the mysql database should, of course, exist , have correct credentials. or, if choose use sqlite, file should accessible, readable , writable www-user (may different depending on linux distributive)
Read more

java - Sending SMS with SMSLib and Web Services -

-
i have made rest web service send smss using hsdpa usb modem. using smslib in java send smss. every time web service invoked, create gateway start service, send message, stop service , remove gateway. takes 20 seconds each message. found starting service takes lot of time. part of code use send smss service.getinstance().addgateway(gateway); service.getinstance().startservice(); outboundmessage msg = new outboundmessage(phonenumber, message); if (service.getinstance().sendmessage(msg)) { result = "message sent successfully!!"; } else { result = "could not send message."; } service.getinstance().stopservice(); service.getinstance().removegateway(gateway);//remove gateway is there way can start service once if not started , use send message when ever web service invoked? why dont group messages , send in 1 go? service.getinstance().sendmessages(messa...
Read more

java - How to resolve The method toString() in the type Object is not applicable for the arguments (InputStream) -

-
i'm trying read json file local machine. @path("/") public class jsonparsing { file f = new file("file.json"); if (f.exists()){ inputstream = new fileinputstream("file.json"); string jsontxt = ioutils.tostring(is); system.out.println(jsontxt); jsonobject json = new jsonobject(jsontxt); string = json.getstring("1000"); system.out.println(a); } } but i'm getting error in tostring() method. possible read .txt file containing json object local machine? if possible, how done? firstly: whenever ask question- add imports well. secondly: yes possible read .txt file containing json object. steps: 1. add maven dependency in pom.xml - <dependency> <groupid>com.googlecode.json-simple</groupid> <artifactid>json-simple</artifactid> <version>1.1.1</version> </dependency> or add jar of dependency. 2.import...
Read more