April 6, 2015 at 2:16 am
I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as 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 table B stores the item process time. The structure is as 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
A 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 to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.
Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.
The result expected is
Table A
Name StartDate EndDate
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
Table B
Item ProcessStartDate ProcessEndDate
A 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
Table A
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
Table B
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
Any one has any idea how I can do this? Please Help!
Thank you very much.
April 6, 2015 at 3:11 am
Quick question, can you post the DDL (create table) code and the sample data as an insert statement? The problem is elementary but unfortunately I don't have time to prepare the DDL/Sample from your post.
😎
April 6, 2015 at 10:36 am
These queries give what I think is your required output"
Table and data setup
declare @a table
(
Name char(10)
,StartDate datetime
,EndDate datetime
)
declare @b-2 table
(
Item char
,ProcessStartDate datetime
,ProcessEndDate datetime
)
insert @a (Name, StartDate, EndDate) values
('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')
insert @b-2 (Item, ProcessStartDate, ProcessEndDate)values
('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')
,('A', '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')
Query 1
select a.name, a.startdate, a.EndDate, b.item, b.processstartdate, b.processenddate
from @a a
cross apply
(select * from @b-2 b
where b.ProcessStartDate >= a.StartDate
and b.ProcessEndDate <= a.EndDate
) b
Query 2
select a.name, a.startdate, a.EndDate, b.item, b.processstartdate, b.processenddate
from @a a
left join @b-2 b
on b.ProcessStartDate >= a.StartDate and b.ProcessEndDate <= a.EndDate
Your verbal description did not match your data. There is no 2015-04-01 17:30 hours in your data.
And please follow the advice given earlier about posting dll and insert statements. It makes it much easier and quicker to come up with a solution. (Check the link in my signature for examples on how to do this.)
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 6, 2015 at 7:41 pm
Sorry for not ask question in correct way. Below is the script for creating a table and inserting the sample data.
Resource Assign table
GO
/****** Object: Table [dbo].[ResourceAssign] Script Date: 04/07/2015 09:05:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ResourceAssign](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ResourceID] [nvarchar](50) NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[Inactive] [int] NULL,
CONSTRAINT [PK_ResourceAssign] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [ResourceAssign]
([ResourceID]
,[StartDate]
,[EndDate]
,[Inactive])
VALUES
('I0109', '2015-03-30 08:30:00.000','2015-03-30 16:30:00.000', 0),
('I0104','2015-03-30 08:30:00.000','2015-03-30 16:30:00.000', 0),
('I0108','2015-03-30 08:30:00.000','2015-03-30 16:30:00.000', 0),
('I0109','2015-03-30 16:30:00.000','2015-03-31 00:30:00.000', 0),
('I0107','2015-03-30 16:30:00.000','2015-03-31 00:30:00.000', 0),
('I0108','2015-03-30 16:30:00.000','2015-03-31 00:30:00.000', 0),
('I0109','2015-03-31 08:30:00.000','2015-03-31 16:30:00.000', 0),
('I0108','2015-03-31 16:30:00.000','2015-04-01 00:30:00.000', 0),
('I0107','2015-03-31 16:30:00.000','2015-04-01 00:30:00.000', 0),
('I0109','2015-04-01 00:30:00.000','2015-04-01 08:30:00.000', 0),
('I0104','2015-04-01 08:30:00.000','2015-04-01 16:30:00.000', 0),
('I0107','2015-04-01 08:30:00.000','2015-04-01 16:30:00.000', 0),
('I0108','2015-04-01 16:30:00.000','2015-04-02 00:30:00.000', 0),
('I0104','2015-04-02 00:30:00.000','2015-04-02 08:30:00.000', 0),
('I0107','2015-04-02 08:30:00.000','2015-04-02 16:30:00.000', 0),
('I0109','2015-04-02 08:30:00.000','2015-04-02 16:30:00.000', 0),
('I0104','2015-04-03 00:30:00.000','2015-04-03 08:30:00.000', 0),
('I0107','2015-04-03 00:30:00.000','2015-04-03 08:30:00.000', 0),
('I0108','2015-04-03 08:30:00.000','2015-04-03 16:30:00.000', 0),
('I0109','2015-04-03 16:30:00.000','2015-04-04 00:30:00.000', 0)
GO
Item Quantity Process table
CREATE TABLE [dbo].[QuantityProcess](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Length1] [decimal](18, 2) NOT NULL,
[Length2] [decimal](18, 2) NOT NULL,
[LengthMeterSqr] [decimal](18, 4) NOT NULL,
[TotalLength] [decimal](18, 2) NOT NULL,
[StartProcessTime] [datetime] NULL,
[EndProcessTime] [datetime] NULL,
[DurationUse] [nchar](10) NULL,
CONSTRAINT [PK_QuantityProcess] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [inno_Edging].[dbo].[QuantityProcess]
([Length1]
,[Length2]
,[LengthMeterSqr]
,[TotalLength]
,[StartProcessTime]
,[EndProcessTime]
,[DurationUse])
VALUES
(223.00,219.00,0.0488,884.00,'2015-04-01 16:30:19.000','2015-04-01 16:30:52.000',33),
(224.00,294.00,0.0659,1036.00, '2015-04-01 16:31:08.000','2015-04-01 16:32:06.000',58),
(226.00,224.00,0.0506,900.00,'2015-04-01 16:35:36.000','2015-04-01 16:36:05.000',29),
(223.00,221.00,0.0493,888.00,'2015-04-01 16:32:16.000','2015-04-01 16:34:21.000',125),
(228.00,228.00,0.0520,912.00,'2015-04-01 18:35:36.000','2015-04-01 18:37:36.000',120),
(223.00,219.00,0.0488,884.00,'2015-04-01 18:38:36.000','2015-04-01 18:40:36.000',120),
(228.00,228.00,0.0520,912.00,'2015-04-02 08:58:49.000','2015-04-02 08:59:35.000',46),
(230.00,229.00,0.0527,918.00,'2015-04-02 08:59:43.000','2015-04-02 09:04:09.000',266),
(226.00,227.00,0.0513,906.00,'2015-04-02 09:04:19.000','2015-04-02 09:05:37.000',78),
(223.00,219.00,0.0488,884.00,'2015-04-02 00:10:36.000','2015-04-02 00:11:36.000',60) ,
(223.00,219.00,0.0488,884.00,'2015-04-02 00:13:36.000','2015-04-02 00:15:36.000',120),
(223.00,223.00,0.0497,892.00,'2015-04-02 09:57:39.000','2015-04-02 09:57:46.000',7),
(233.00,233.00,0.0543,932.00,'2015-04-02 12:59:27.000','2015-04-02 13:00:18.000',51),
(229.00,232.00,0.0531,922.00,'2015-04-02 13:02:02.000','2015-04-02 13:02:55.000',53),
(216.00,217.00,0.0469,866.00,'2015-04-03 11:15:08.000','2015-04-03 11:15:45.000',37),
(326.00,220.00,0.0717,1092.00,'2015-04-03 11:15:53.000','2015-04-03 11:16:13.000',20),
(258.00,329.00,0.0849,1174.00,'2015-04-03 11:16:25.000','2015-04-03 11:16:40.000',15),
(233.00,214.00,0.0499,894.00,'2015-04-03 12:15:20.000','2015-04-03 12:15:30.000',10),
(321.00,229.00,0.0735,1100.00,'2015-04-06 15:09:20.000','2015-04-06 15:09:27.000',7)
In my system, user allow to select a date, the start time and end time to get the result. User can select start date 2015-04-01 16:31:00 and end date 2015-04-01 19:30:00 from the system to find out what item is process in this time range. With the same time range, user need to know who is assign to process this item. The resource(employee) is assign base on the shift(8 hour per shift). So, if user select start date 2015-04-01 16:31:00 and end date 2015-04-01 19:30:00 to query the item process, they also need to know whose in that period in charge to process the item with the time range select. How I can get to know the time select is fall in which shift?
The result expected are
Quantity Process table
(224.00,294.00,0.0659,1036.00, '2015-04-01 16:31:08.000','2015-04-01 16:32:06.000',58),
(226.00,224.00,0.0506,900.00,'2015-04-01 16:35:36.000','2015-04-01 16:36:05.000',29),
(223.00,221.00,0.0493,888.00,'2015-04-01 16:32:16.000','2015-04-01 16:34:21.000',125),
(228.00,228.00,0.0520,912.00,'2015-04-01 18:35:36.000','2015-04-01 18:37:36.000',120),
(223.00,219.00,0.0488,884.00,'2015-04-01 18:38:36.000','2015-04-01 18:40:36.000',120),
Resource Assign table
('I0108','2015-04-01 16:30:00.000','2015-04-02 00:30:00.000', 0)
If user select date range is start date 2015-04-01 00:00:01 and end date 2015-04-01 23:59:59, the result expected as below. Even only 16:30:19 - 18:40:36 has item process, but there is few shift or employee is fall in the time range. It also need to been query out as a result for Resource assign table.
(223.00,219.00,0.0488,884.00,'2015-04-01 16:30:19.000','2015-04-01 16:30:52.000',33),
(224.00,294.00,0.0659,1036.00, '2015-04-01 16:31:08.000','2015-04-01 16:32:06.000',58),
(226.00,224.00,0.0506,900.00,'2015-04-01 16:35:36.000','2015-04-01 16:36:05.000',29),
(223.00,221.00,0.0493,888.00,'2015-04-01 16:32:16.000','2015-04-01 16:34:21.000',125),
(228.00,228.00,0.0520,912.00,'2015-04-01 18:35:36.000','2015-04-01 18:37:36.000',120),
(223.00,219.00,0.0488,884.00,'2015-04-01 18:38:36.000','2015-04-01 18:40:36.000',120),
('I0109','2015-04-01 00:30:00.000','2015-04-01 08:30:00.000', 0),
('I0104','2015-04-01 08:30:00.000','2015-04-01 16:30:00.000', 0),
('I0107','2015-04-01 08:30:00.000','2015-04-01 16:30:00.000', 0),
('I0108','2015-04-01 16:30:00.000','2015-04-02 00:30:00.000', 0),
Any idea how I can get this result?
April 6, 2015 at 10:41 pm
The posting of the dll and data was very helpful!!
I was able to modify my earlier post to get what I think is what you are after.
declare @start_date datetime = '2015-04-01 16:30:00'
, @end_date datetime = '2015-04-01 19:30:00'
select distinct a.ResourceID, a.StartDate,
b.Length1, b.Length2, b.lengthMeterSqr, b.totalLength, b.startprocesstime, b.endprocesstime, b.durationUse
from ResourceAssign a
cross apply
(select * from QuantityProcess b
where b.StartProcessTime >= @start_date
and b.EndProcessTime <= @end_date
) b
where CAST(a.startdate as date) = CAST(@start_date as date)
and CAST(a.startdate as time) >= CAST(@start_date as time)
order by a.resourceid
This isn't exactly the formatted output that you gave in your example. But it will give the same information. Someone with a higher pay-grade than me might be able to tease it out.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 7, 2015 at 3:30 am
If I change the startdate to 2015-04-01 16:31:00, it not able to select the record. But it has item process at that time range. Just the ResourceAssign table not able to select out. How I can fix this?
April 7, 2015 at 8:06 pm
derickloo (4/7/2015)
If I change the startdate to 2015-04-01 16:31:00, it not able to select the record. But it has item process at that time range. Just the ResourceAssign table not able to select out. How I can fix this?
Change the outer where clause to:
where b.StartProcessTime between a.StartDate and a.EndDate
or (CAST(a.startdate as date) = CAST(@start_date as date)
and CAST(a.startdate as time) >= CAST(@start_date as time))
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 8, 2015 at 7:19 pm
Thanks LinksUp. It's work.
April 10, 2015 at 10:03 pm
derickloo (4/8/2015)
Thanks LinksUp. It's work.
Glad it worked for you. 🙂
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply