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