sql - 4SQL - Query to select date range -


i have 2 tables, 1 table stores resources assign work period. structure below

name      startdate                    enddate  tan       2015-04-01 08:30:00.000     2015-04-01 16:30:00.000  max       2015-04-01 08:30:00.000     2015-04-01 16:30:00.000  alan      2015-04-01 16:30:00.000     2015-04-02 00:30:00.000

the second table b stores item process time. structure below

 item    processstartdate            processenddate  v       2015-04-01 09:30:10.000     2015-04-01 09:34:45.000  q       2015-04-01 10:39:01.000     2015-04-01 10:41:11.000  w       2015-04-01 11:44:00.000     2015-04-01 11:46:25.000        2015-04-01 16:40:10.000     2015-04-01 16:42:45.000  b       2015-04-01 16:43:01.000     2015-04-01 16:45:11.000  c       2015-04-01 16:47:00.000     2015-04-01 16:49:25.000

i need select item process in 2015-04-01 16:40:00 , 2015-04-01 17:30:59. beside need know how many resource assigned process item in period of time. know start date 2015-04-01 16:00:00 , end date 2015-04-01 17:30:59. how can select data both tables. there no need join, seperate selections.

another item process time in 2015-04-01 10:00:00 , 2015-04-04 11:50:59.

the result expected is

   name      startdate                    enddate    alan      2015-04-01 16:30:00.000     2015-04-02 00:30:00.000     item    processstartdate            processenddate          2015-04-01 16:30:10.000     2015-04-01 16:32:45.000    b       2015-04-01 16:33:01.000     2015-04-01 16:35:11.000    c       2015-04-01 16:37:00.000     2015-04-02 16:39:25.000

scenario 2 expected result

   name      startdate                    enddate    tan       2015-04-01 08:30:00.000     2015-04-01 16:30:00.000    max       2015-04-01 08:30:00.000     2015-04-01 16:30:00.000      item    processstartdate            processenddate          q       2015-04-01 10:39:01.000     2015-04-01 10:41:11.000     w       2015-04-01 11:44:00.000     2015-04-01 11:46:25.000

i has modified bit requirement. please help.

this should work:

select * tableb processstartdate>'2015-04-01 16:00:00'        ,        processenddate<'2015-04-01 17:30:59'

to result tablea have 2 option depending on need do.

  • they not connected, same thing. (change tablea)
  • they connected, need use key between them , select proper part tablea correspond processdates.

Comments

Post a Comment

Popular posts from this blog

javascript - AngularJS custom datepicker directive -

i make custom datepicker directive custom template. but have no idea how start building it... how include date data directive? i appreciate guide or give me advice work on more precisely. it might you! follow below steps html code sample: <label>birth date</label> <input type="text" ng-model="birthdate" date-options="dateoptions" custom-datepicker/> <hr/> <pre>birthdate = {{birthdate}}</pre> <script type="text/ng-template" id="custom-datepicker.html"> <div class="enhanced-datepicker"> <div class="proxied-field-wrap"> <input type="text" ui-date-format="yy-mm-dd" ng-model="ngmodel" ui-date="dateoptions"/> </div> <label> <button class="btn" type="button"><i class="icon-calendar"></i>
Read more

javascript - jQuery date picker - Disable dates after the selection from the first date picker -

<!doctype html> <html lang="en"> <head> <meta charset="utf-8"> <title>jquery ui datepicker - default functionality</title> <link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css"> <script src="//code.jquery.com/jquery-1.10.2.js"></script> <script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script> <link rel="stylesheet" href="/resources/demos/style.css"> <script> $(function () { $("#datepicker1").datepicker(); }); </script> <script> $(function () { $("#datepicker2").datepicker(); }); </script> </head> <body> <p>date 1: <input type="text" id="datepicker1"></p> <p>date 2: <input type="t
Read more