Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 using multiple records from a criteria table

Author  Topic 

chippyles
Yak Posting Veteran

68 Posts
Posted - 2006-07-06 : 11:16:14
I have two tables. One table (tM_FR_STOPS)contains all of the data I need to search, group and calculate. Whereas, the other table (tM_FR_WIP_UnitID_FastStops) contains records of criteria.

In tM_FR_WIP_UnitID_FastStops I have a startgroup, endgroup and along with a line number. For this example I have only two records, but I will probably have hundreds of records in a few months.

I need to query this using this criteria BETWEEN [tM_FR_WIP_UnitID_FastStops].[StartGroup] AND [tM_FR_WIP_UnitID_FastStops].[EndGroup] for [tM_FR_STOPS].[tRSSQL_TRANS].

as well as

I need to used this criteria [tM_FR_WIP_UnitID_FastStops].[Line] for [tM_FR_STOPS].[bLINE].



MY GOAL:

I need this query to SUM the [tM_FR_WIP_UnitID_FastStops].[bFAST_STOP]


This query would be easy if there was only one record in [tM_FR_WIP_UnitID_FastStops], but since there is multiple records, I am having a difficult time doing this.


Here is the script to create everything to get started.

Thanks!!

***********************************************************

--CREATES tM_FR_STOPS Table

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tM_FR_STOPS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tM_FR_STOPS]
GO

CREATE TABLE [dbo].[tM_FR_STOPS] (
[tRSSQL_TRANS] [datetime] NULL ,
[nLINE] [tinyint] NULL ,
[nWEIGHT] [int] NULL ,
[bLINE_START] [tinyint] NULL ,
[bFAST_STOP] [tinyint] NULL ,
[bEND_OF_REEL] [tinyint] NULL
) ON [PRIMARY]
GO

--INSERT INTO tM_FR_STOPS Table

INSERT INTO tM_FR_STOPS(tRSSQL_TRANS,nWEIGHT,bLINE_START,bFAST_STOP,bEND_OF_REEL,nLINE)
SELECT '6/15/2006 3:25:26 AM',0,1,0,0,11 UNION ALL
SELECT '6/15/2006 4:01:14 AM',815,0,1,0,11 UNION ALL
SELECT '6/15/2006 4:03:36 AM',819,1,0,0,11 UNION ALL
SELECT '6/15/2006 4:55:57 AM',2015,0,0,1,11 UNION ALL
SELECT '6/15/2006 1:41:43 AM',0,1,0,0,12 UNION ALL
SELECT '6/15/2006 2:14:06 AM',478,0,1,0,12 UNION ALL
SELECT '6/15/2006 2:27:12 AM',478,1,0,0,12 UNION ALL
SELECT '6/15/2006 2:32:37 AM',558,0,1,0,12 UNION ALL
SELECT '6/15/2006 2:34:24 AM',558,1,0,0,12 UNION ALL
SELECT '6/15/2006 2:38:08 AM',612,0,1,0,12 UNION ALL
SELECT '6/15/2006 2:49:30 AM',614,0,0,0,12 UNION ALL
SELECT '6/15/2006 2:49:31 AM',614,0,0,0,12 UNION ALL
SELECT '6/15/2006 2:51:51 AM',614,1,0,0,12 UNION ALL
SELECT '6/15/2006 3:52:21 AM',1511,0,0,1,12

--CREATES tM_FR_WIP_UnitID_FastStops Table

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tM_FR_WIP_UnitID_FastStops]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tM_FR_WIP_UnitID_FastStops]
GO

CREATE TABLE [dbo].[tM_FR_WIP_UnitID_FastStops] (
[nLine] [int] NULL ,
[Weight] [bigint] NULL ,
[StartGroup] [datetime] NULL ,
[EndGroup] [datetime] NULL ,
[UnitNo] [numeric](18, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO

--INSERT INTO tM_FR_WIP_UnitID_FastStops Table

INSERT INTO tM_FR_WIP_UnitID_FastStops(nLine,Weight,StartGroup,EndGroup)
SELECT 11,2015,'6/15/2006 3:25:26 AM','6/15/2006 4:55:57 AM' UNION ALL
SELECT 12,1511,'6/15/2006 1:41:43 AM','6/15/2006 3:52:21 AM'

***********************************************************

X002548
Not Just a Number

15586 Posts
Posted - 2006-07-06 : 11:22:47
First, I gotta say, that's a great post...the only thing missing is what the final result should be...let me give this a shot



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

chippyles
Yak Posting Veteran

68 Posts
Posted - 2006-07-06 : 11:35:44
The column on the right should SUM up all of the Fast Stops in tM_FR_STOPS using tM_FR_WIP_UnitID_FastStops as the criteria.

This can be shown as a query. I will make it into a table later.

Below are the actual results that I need to see.


nLine	Weight	StartGroup	EndGroup	UnitNo	Fast_Stop
11	2015	6/15/2006 3:25	6/15/2006 4:55	1	1
12	1511	6/15/2006 1:41	6/15/2006 3:52	2	3
Go to Top of Page

chippyles
Yak Posting Veteran

68 Posts
Posted - 2006-07-06 : 14:57:30
Any help here?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2006-07-06 : 15:30:51
In your expected result set, you have a column named UnitNo. How is that data calculated?

Tara Kizer
aka tduggan
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2006-07-06 : 15:33:27
Here is what I came up with (minus the UnitNo part since I don't know what that is yet):



SELECT fs.nLine, fs.Weight, fs.StartGroup, fs.EndGroup, t.bFAST_STOP
FROM tM_FR_WIP_UnitID_FastStops fs
INNER JOIN
(
	SELECT f.nLine, SUM(bFAST_STOP) AS bFAST_STOP
	FROM tM_FR_WIP_UnitID_FastStops f
	INNER JOIN tM_FR_STOPS s
	ON f.StartGroup <= s.tRSSQL_TRANS AND f.EndGroup >= s.tRSSQL_TRANS AND f.nLine = s.nLINE
	GROUP BY f.nLine
) t
ON fs.nLine = t.nLine


The key to figuring out my query is to run the derived table separately. Here's the derived table:

SELECT fs.nLine, SUM(bFAST_STOP) AS bFAST_STOP
FROM tM_FR_WIP_UnitID_FastStops fs
INNER JOIN tM_FR_STOPS s
ON fs.StartGroup <= s.tRSSQL_TRANS AND fs.EndGroup >= s.tRSSQL_TRANS AND fs.nLine = s.nLINE
GROUP BY fs.nLine

You then join on this table with the table that contains the rest of the data that you need.

Tara Kizer
aka tduggan
Go to Top of Page

chippyles
Yak Posting Veteran

68 Posts
Posted - 2006-07-06 : 15:50:01
UnitNo is a counter to give each reel a unique number

[UnitNo] [numeric](18, 0) IDENTITY (1, 1) NOT NULL

Thanks for your response
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2006-07-06 : 15:52:10
Ahhh, sorry about that. I didn't notice UnitNo in the CREATE TABLE statement. I was looking at the INSERTs only.

You can then just add fs.UnitNo to the outer query then. 


SELECT fs.nLine, fs.Weight, fs.StartGroup, fs.EndGroup, fs.UnitNo, t.bFAST_STOP
FROM tM_FR_WIP_UnitID_FastStops fs
INNER JOIN
(
	SELECT f.nLine, SUM(bFAST_STOP) AS bFAST_STOP
	FROM tM_FR_WIP_UnitID_FastStops f
	INNER JOIN tM_FR_STOPS s
	ON f.StartGroup <= s.tRSSQL_TRANS AND f.EndGroup >= s.tRSSQL_TRANS AND f.nLine = s.nLINE
	GROUP BY f.nLine
) t
ON fs.nLine = t.nLine


Let us know if this works for you.

Tara Kizer
aka tduggan
Go to Top of Page

chippyles
Yak Posting Veteran

68 Posts
Posted - 2006-07-07 : 09:17:52
I am not exactly sure I follow. It seems like it worked in your example, but when I applied it to my main table, it added up too many records. I have added more records to the --INSERT INTO tM_FR_STOPS Table and the --INSERT INTO tM_FR_WIP_UnitID_FastStops Table section of the script.

If your code still works with the new data, could you show me how to set up your code into one query?

Using the new script below this is the result I get for each of your SELECTs


nLine	Weight	StartGroup	EndGroup	UnitNo	bFast_Stop
11	2015	6/15/06 3:25 AM	6/15/06 4:55 AM	1	1
12	1511	6/15/06 1:41 AM	6/15/06 3:52 AM	2	3
1	2011	7/7/06 2:09 AM	7/7/06 4:24 AM	3	2
2	2028	7/7/06 1:05 AM	7/7/06 3:13 AM	4	2
13	2018	7/7/06 4:57 AM	7/7/06 7:00 AM	5	2

nLine	bFast_Stop				
1	2				
2	2				
11	1				
12	3				
13	2




Thanks!!

***********************************************************

--CREATES tM_FR_STOPS Table

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tM_FR_STOPS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tM_FR_STOPS]
GO

CREATE TABLE [dbo].[tM_FR_STOPS] (
[tRSSQL_TRANS] [datetime] NULL ,
[nLINE] [tinyint] NULL ,
[nWEIGHT] [int] NULL ,
[bLINE_START] [tinyint] NULL ,
[bFAST_STOP] [tinyint] NULL ,
[bEND_OF_REEL] [tinyint] NULL
) ON [PRIMARY]
GO

--INSERT INTO tM_FR_STOPS Table

INSERT INTO tM_FR_STOPS(tRSSQL_TRANS,nWEIGHT,bLINE_START,bFAST_STOP,bEND_OF_REEL,nLINE)
SELECT '7/7/2006 12:04:42 AM',1079,1,0,0,2 UNION ALL
SELECT '7/7/2006 12:05:41 AM',57,0,1,0,7 UNION ALL
SELECT '7/7/2006 12:05:43 AM',0,1,1,0,32 UNION ALL
SELECT '7/7/2006 12:08:35 AM',1,0,0,0,26 UNION ALL
SELECT '7/7/2006 12:11:09 AM',1,0,0,0,23 UNION ALL
SELECT '7/7/2006 12:13:25 AM',0,1,1,0,24 UNION ALL
SELECT '7/7/2006 12:14:49 AM',1,0,0,0,32 UNION ALL
SELECT '7/7/2006 12:15:49 AM',1,0,0,0,24 UNION ALL
SELECT '7/7/2006 12:15:57 AM',0,1,1,0,24 UNION ALL
SELECT '7/7/2006 12:16:13 AM',1,0,0,0,24 UNION ALL
SELECT '7/7/2006 12:17:15 AM',1,0,0,0,27 UNION ALL
SELECT '7/7/2006 12:21:09 AM',1,0,0,0,25 UNION ALL
SELECT '7/7/2006 12:21:17 AM',1,0,0,0,41 UNION ALL
SELECT '7/7/2006 12:22:13 AM',1,0,0,0,33 UNION ALL
SELECT '7/7/2006 12:35:35 AM',0,1,1,0,32 UNION ALL
SELECT '7/7/2006 12:39:21 AM',1,0,0,0,32 UNION ALL
SELECT '7/7/2006 12:57:48 AM',1,0,0,0,23 UNION ALL
SELECT '7/7/2006 12:58:22 AM',0,1,1,0,23 UNION ALL
SELECT '7/7/2006 12:59:34 AM',1,0,0,0,23 UNION ALL
SELECT '7/7/2006 1:01:16 AM',0,1,1,0,23 UNION ALL
SELECT '7/7/2006 1:03:48 AM',1,0,0,0,25 UNION ALL
SELECT '7/7/2006 1:05:46 AM',1,0,0,0,23 UNION ALL
SELECT '7/7/2006 1:09:00 AM',1,0,0,0,40 UNION ALL
SELECT '7/7/2006 1:09:24 AM',0,1,1,0,33 UNION ALL
SELECT '7/7/2006 1:13:34 AM',1,0,0,0,26 UNION ALL
SELECT '7/7/2006 1:16:46 AM',0,1,1,0,24 UNION ALL
SELECT '7/7/2006 1:19:36 AM',1,0,0,0,32 UNION ALL
SELECT '7/7/2006 1:19:44 AM',1,0,0,0,24 UNION ALL
SELECT '7/7/2006 1:24:05 AM',1,0,0,0,45 UNION ALL
SELECT '7/7/2006 1:25:46 AM',0,1,1,0,27 UNION ALL
SELECT '7/7/2006 1:28:15 AM',0,1,1,0,26 UNION ALL
SELECT '7/7/2006 1:30:35 AM',1,0,0,0,27 UNION ALL
SELECT '7/7/2006 1:35:29 AM',0,1,1,0,41 UNION ALL
SELECT '7/7/2006 1:38:33 AM',1,0,0,0,41 UNION ALL
SELECT '7/7/2006 1:43:15 AM',1,0,0,0,33 UNION ALL
SELECT '7/7/2006 1:46:15 AM',1,0,0,0,25 UNION ALL
SELECT '7/7/2006 1:47:45 AM',1,0,0,0,27 UNION ALL
SELECT '7/7/2006 1:48:41 AM',1,0,0,0,23 UNION ALL
SELECT '7/7/2006 1:53:49 AM',1,0,0,0,41 UNION ALL
SELECT '7/7/2006 2:01:51 AM',0,1,1,0,41 UNION ALL
SELECT '7/7/2006 2:03:48 AM',1,0,0,0,26 UNION ALL
SELECT '7/7/2006 2:06:02 AM',1,0,0,0,41 UNION ALL
SELECT '7/7/2006 2:08:25 AM',0,1,1,0,41 UNION ALL
SELECT '7/7/2006 2:17:06 AM',1,0,0,0,45 UNION ALL
SELECT '7/7/2006 2:19:22 AM',0,1,1,0,32 UNION ALL
SELECT '7/7/2006 2:20:00 AM',1,0,0,0,41 UNION ALL
SELECT '7/7/2006 2:20:22 AM',0,1,1,0,24 UNION ALL
SELECT '7/7/2006 2:20:46 AM',0,1,1,0,23 UNION ALL
SELECT '7/7/2006 2:21:38 AM',0,1,1,0,25 UNION ALL
SELECT '7/7/2006 2:22:36 AM',1,0,0,0,32 UNION ALL
SELECT '7/7/2006 2:23:06 AM',1,0,0,0,25 UNION ALL
SELECT '7/7/2006 2:24:14 AM',1,0,0,0,24 UNION ALL
SELECT '7/7/2006 2:26:24 AM',0,1,1,0,41 UNION ALL
SELECT '7/7/2006 2:27:06 AM',0,1,1,0,25 UNION ALL
SELECT '7/7/2006 2:27:26 AM',1,0,0,0,41 UNION ALL
SELECT '7/7/2006 2:30:52 AM',1,0,0,0,40 UNION ALL
SELECT '7/7/2006 2:40:26 AM',1,0,0,0,23 UNION ALL
SELECT '7/7/2006 2:40:42 AM',1,0,0,0,25 UNION ALL
SELECT '7/7/2006 2:49:57 AM',0,1,1,0,41 UNION ALL
SELECT '7/7/2006 2:52:49 AM',1,0,0,0,41 UNION ALL
SELECT '7/7/2006 2:53:39 AM',1,0,0,0,23 UNION ALL
SELECT '7/7/2006 2:54:05 AM',1,0,0,0,26 UNION ALL
SELECT '7/7/2006 2:59:53 AM',0,1,1,0,33 UNION ALL
SELECT '7/7/2006 3:00:21 AM',0,1,1,0,41 UNION ALL
SELECT '7/7/2006 3:02:11 AM',1,0,0,0,41 UNION ALL
SELECT '7/7/2006 3:12:41 AM',1,0,0,0,27 UNION ALL
SELECT '7/7/2006 3:16:49 AM',1,0,0,0,33 UNION ALL
SELECT '7/7/2006 3:17:47 AM',1,0,0,0,45 UNION ALL
SELECT '7/7/2006 3:21:09 AM',0,1,1,0,24 UNION ALL
SELECT '7/7/2006 3:22:21 AM',1,0,0,0,24 UNION ALL
SELECT '7/7/2006 3:23:59 AM',0,1,1,0,45 UNION ALL
SELECT '7/7/2006 3:27:58 AM',0,1,1,0,27 UNION ALL
SELECT '7/7/2006 3:29:28 AM',1,0,0,0,45 UNION ALL
SELECT '7/7/2006 3:35:36 AM',1,0,0,0,33 UNION ALL
SELECT '7/7/2006 3:39:46 AM',1,0,0,0,23 UNION ALL
SELECT '7/7/2006 3:41:12 AM',1,0,0,0,27 UNION ALL
SELECT '7/7/2006 3:41:20 AM',1,0,0,0,24 UNION ALL
SELECT '7/7/2006 3:42:44 AM',0,1,1,0,45 UNION ALL
SELECT '7/7/2006 3:44:28 AM',1,0,0,0,32 UNION ALL
SELECT '7/7/2006 3:46:00 AM',1,0,0,0,25 UNION ALL
SELECT '7/7/2006 3:47:32 AM',1,0,0,0,41 UNION ALL
SELECT '7/7/2006 3:52:38 AM',1,0,0,0,40 UNION ALL
SELECT '7/7/2006 3:58:16 AM',1,0,0,0,45 UNION ALL
SELECT '7/7/2006 4:00:16 AM',0,1,1,0,23 UNION ALL
SELECT '7/7/2006 4:03:26 AM',1,0,0,0,23 UNION ALL
SELECT '7/7/2006 4:07:24 AM',0,1,1,0,32 UNION ALL
SELECT '7/7/2006 4:09:39 AM',0,1,1,0,45 UNION ALL
SELECT '7/7/2006 4:09:51 AM',1,0,0,0,26 UNION ALL
SELECT '7/7/2006 4:16:15 AM',0,1,1,0,33 UNION ALL
SELECT '7/7/2006 4:25:13 AM',1,0,0,0,33 UNION ALL
SELECT '7/7/2006 4:28:37 AM',1,0,0,0,23 UNION ALL
SELECT '7/7/2006 4:28:59 AM',1,0,0,0,25 UNION ALL
SELECT '7/7/2006 4:37:30 AM',0,1,1,0,41 UNION ALL
SELECT '7/7/2006 4:38:54 AM',0,1,1,0,24 UNION ALL
SELECT '7/7/2006 4:40:55 AM',1,0,0,0,41 UNION ALL
SELECT '7/7/2006 4:41:01 AM',1,0,0,0,24 UNION ALL
SELECT '7/7/2006 4:43:42 AM',0,1,1,0,41 UNION ALL
SELECT '7/7/2006 4:44:18 AM',0,1,1,0,24 UNION ALL
SELECT '7/7/2006 4:44:26 AM',1,0,0,0,41 UNION ALL
SELECT '7/7/2006 4:47:02 AM',1,0,0,0,32 UNION ALL
SELECT '7/7/2006 4:48:04 AM',0,1,1,0,32 UNION ALL
SELECT '7/7/2006 4:48:08 AM',0,1,1,0,33 UNION ALL
SELECT '7/7/2006 4:50:18 AM',1,0,0,0,24 UNION ALL
SELECT '7/7/2006 4:52:42 AM',0,1,1,0,41 UNION ALL
SELECT '7/7/2006 4:52:42 AM',0,1,1,0,40 UNION ALL
SELECT '7/7/2006 4:58:34 AM',1,0,0,0,33 UNION ALL
SELECT '7/7/2006 4:59:04 AM',1,0,0,0,27 UNION ALL
SELECT '7/7/2006 4:59:14 AM',1,0,0,0,32 UNION ALL
SELECT '7/7/2006 5:00:14 AM',0,1,1,0,32 UNION ALL
SELECT '7/7/2006 5:01:16 AM',1,0,0,0,40 UNION ALL
SELECT '7/7/2006 5:01:54 AM',1,0,0,0,41 UNION ALL
SELECT '7/7/2006 5:02:04 AM',0,1,1,0,41 UNION ALL
SELECT '7/7/2006 5:02:52 AM',1,0,0,0,32 UNION ALL
SELECT '7/7/2006 5:09:52 AM',0,1,1,0,32 UNION ALL
SELECT '7/7/2006 5:12:06 AM',1,0,0,0,25 UNION ALL
SELECT '7/7/2006 5:12:24 AM',1,0,0,0,32 UNION ALL
SELECT '7/7/2006 5:16:29 AM',1,0,0,0,23 UNION ALL
SELECT '7/7/2006 5:22:43 AM',1,0,0,0,40 UNION ALL
SELECT '7/7/2006 5:23:15 AM',0,1,1,0,32 UNION ALL
SELECT '7/7/2006 5:24:29 AM',1,0,0,0,32 UNION ALL
SELECT '7/7/2006 5:29:45 AM',0,1,1,0,32 UNION ALL
SELECT '7/7/2006 5:31:55 AM',1,0,0,0,32 UNION ALL
SELECT '7/7/2006 5:32:51 AM',1,0,0,0,26 UNION ALL
SELECT '7/7/2006 5:47:37 AM',0,1,1,0,26 UNION ALL
SELECT '7/7/2006 5:50:23 AM',0,1,1,0,24 UNION ALL
SELECT '7/7/2006 5:50:51 AM',1,0,0,0,26 UNION ALL
SELECT '7/7/2006 5:58:16 AM',1,0,0,0,41 UNION ALL
SELECT '7/7/2006 5:58:50 AM',0,1,1,0,41 UNION ALL
SELECT '7/7/2006 5:59:22 AM',0,1,1,0,26 UNION ALL
SELECT '7/7/2006 6:03:22 AM',1,0,0,0,26 UNION ALL
SELECT '7/7/2006 6:04:00 AM',1,0,0,0,24 UNION ALL
SELECT '7/7/2006 6:05:46 AM',0,1,1,0,26 UNION ALL
SELECT '7/7/2006 6:07:08 AM',1,0,0,0,26 UNION ALL
SELECT '7/7/2006 6:12:26 AM',1,0,0,0,41 UNION ALL
SELECT '7/7/2006 6:17:14 AM',1,0,0,0,33 UNION ALL
SELECT '7/7/2006 6:17:36 AM',0,1,1,0,41 UNION ALL
SELECT '7/7/2006 6:19:30 AM',1,0,0,0,25 UNION ALL
SELECT '7/7/2006 6:25:16 AM',1,0,0,0,27 UNION ALL
SELECT '7/7/2006 6:28:26 AM',1,0,0,0,23 UNION ALL
SELECT '7/7/2006 6:31:52 AM',0,1,1,0,24 UNION ALL
SELECT '7/7/2006 6:34:22 AM',1,0,0,0,41 UNION ALL
SELECT '7/7/2006 6:36:41 AM',0,1,1,0,33 UNION ALL
SELECT '7/7/2006 6:41:21 AM',0,1,1,0,25 UNION ALL
SELECT '7/7/2006 6:43:31 AM',1,0,0,0,40 UNION ALL
SELECT '7/7/2006 6:44:49 AM',1,0,0,0,25 UNION ALL
SELECT '7/7/2006 6:53:31 AM',0,1,1,0,25 UNION ALL
SELECT '7/7/2006 7:03:19 AM',1,0,0,0,41 UNION ALL
SELECT '7/7/2006 7:04:57 AM',0,1,1,0,41 UNION ALL
SELECT '7/7/2006 7:16:52 AM',1,0,0,0,42 UNION ALL
SELECT '7/7/2006 7:17:56 AM',0,1,1,0,40 UNION ALL
SELECT '7/7/2006 7:18:16 AM',0,1,1,0,42 UNION ALL
SELECT '7/7/2006 7:20:20 AM',1,0,0,0,24 UNION ALL
SELECT '7/7/2006 7:21:38 AM',0,1,1,0,24 UNION ALL
SELECT '7/7/2006 7:21:44 AM',1,0,0,0,23 UNION ALL
SELECT '7/7/2006 7:23:28 AM',1,0,0,0,40 UNION ALL
SELECT '7/7/2006 7:23:52 AM',1,0,0,0,25 UNION ALL
SELECT '7/7/2006 7:27:04 AM',1,0,0,0,26 UNION ALL
SELECT '7/7/2006 7:30:24 AM',1,0,0,0,32 UNION ALL
SELECT '7/7/2006 7:38:30 AM',1,0,0,0,25 UNION ALL
SELECT '7/7/2006 7:39:22 AM',1,0,0,0,41 UNION ALL
SELECT '7/7/2006 7:52:57 AM',0,1,1,0,32 UNION ALL
SELECT '7/7/2006 7:54:37 AM',0,1,1,0,23 UNION ALL
SELECT '7/7/2006 7:54:53 AM',0,1,1,0,26 UNION ALL
SELECT '7/7/2006 7:55:17 AM',0,1,1,0,25 UNION ALL
SELECT '6/15/2006 3:25:26 AM',0,1,0,0,11 UNION ALL
SELECT '6/15/2006 4:01:14 AM',815,0,1,0,11 UNION ALL
SELECT '6/15/2006 4:03:36 AM',819,1,0,0,11 UNION ALL
SELECT '6/15/2006 1:41:43 AM',0,1,0,0,12 UNION ALL
SELECT '6/15/2006 2:14:06 AM',478,0,1,0,12 UNION ALL
SELECT '6/15/2006 2:27:12 AM',478,1,0,0,12 UNION ALL
SELECT '6/15/2006 2:32:37 AM',558,0,1,0,12 UNION ALL
SELECT '6/15/2006 2:34:24 AM',558,1,0,0,12 UNION ALL
SELECT '6/15/2006 2:38:08 AM',612,0,1,0,12 UNION ALL
SELECT '6/15/2006 2:49:30 AM',614,0,0,0,12 UNION ALL
SELECT '6/15/2006 2:49:31 AM',614,0,0,0,12 UNION ALL
SELECT '6/15/2006 2:51:51 AM',614,1,0,0,12 UNION ALL
SELECT '7/7/2006 12:07:39 AM',0,1,1,1,33 UNION ALL
SELECT '7/7/2006 12:09:07 AM',0,1,1,1,23 UNION ALL
SELECT '7/7/2006 12:16:17 AM',0,1,1,1,25 UNION ALL
SELECT '7/7/2006 12:18:21 AM',0,1,1,1,41 UNION ALL
SELECT '7/7/2006 12:54:40 AM',0,1,1,1,23 UNION ALL
SELECT '7/7/2006 1:01:26 AM',0,1,1,1,25 UNION ALL
SELECT '7/7/2006 1:06:26 AM',0,1,1,1,40 UNION ALL
SELECT '7/7/2006 1:09:22 AM',0,1,1,1,26 UNION ALL
SELECT '7/7/2006 1:11:34 AM',0,1,1,1,32 UNION ALL
SELECT '7/7/2006 1:43:21 AM',0,1,1,1,27 UNION ALL
SELECT '7/7/2006 1:43:53 AM',0,1,1,1,25 UNION ALL
SELECT '7/7/2006 1:46:49 AM',0,1,1,1,23 UNION ALL
SELECT '7/7/2006 1:51:15 AM',0,1,1,1,41 UNION ALL
SELECT '7/7/2006 2:07:41 AM',0,1,1,1,45 UNION ALL
SELECT '7/7/2006 2:27:56 AM',0,1,1,1,40 UNION ALL
SELECT '7/7/2006 2:49:41 AM',0,1,1,1,26 UNION ALL
SELECT '7/7/2006 2:51:35 AM',0,1,1,1,23 UNION ALL
SELECT '7/7/2006 3:09:07 AM',0,1,1,1,27 UNION ALL
SELECT '7/7/2006 3:11:27 AM',0,1,1,1,45 UNION ALL
SELECT '7/7/2006 3:20:19 AM',0,1,1,1,25 UNION ALL
SELECT '7/7/2006 3:27:10 AM',0,1,1,1,24 UNION ALL
SELECT '7/7/2006 3:27:14 AM',0,1,1,1,33 UNION ALL
SELECT '7/7/2006 3:31:06 AM',0,1,1,1,32 UNION ALL
SELECT '7/7/2006 3:37:00 AM',0,1,1,1,23 UNION ALL
SELECT '7/7/2006 3:39:36 AM',0,1,1,1,41 UNION ALL
SELECT '7/7/2006 3:50:04 AM',0,1,1,1,40 UNION ALL
SELECT '7/7/2006 3:55:02 AM',0,1,1,1,26 UNION ALL
SELECT '7/7/2006 4:25:47 AM',0,1,1,1,25 UNION ALL
SELECT '7/7/2006 4:26:29 AM',0,1,1,1,23 UNION ALL
SELECT '7/7/2006 4:47:10 AM',0,1,1,1,27 UNION ALL
SELECT '7/7/2006 5:09:00 AM',0,1,1,1,25 UNION ALL
SELECT '7/7/2006 5:10:54 AM',0,1,1,1,26 UNION ALL
SELECT '7/7/2006 5:12:14 AM',0,1,1,1,23 UNION ALL
SELECT '7/7/2006 5:20:06 AM',0,1,1,1,40 UNION ALL
SELECT '7/7/2006 5:52:31 AM',0,1,1,1,25 UNION ALL
SELECT '7/7/2006 5:59:44 AM',0,1,1,1,23 UNION ALL
SELECT '7/7/2006 6:09:42 AM',0,1,1,1,33 UNION ALL
SELECT '7/7/2006 6:20:48 AM',0,1,1,1,27 UNION ALL
SELECT '7/7/2006 6:41:47 AM',0,1,1,1,40 UNION ALL
SELECT '7/7/2006 6:42:19 AM',0,1,1,1,26 UNION ALL
SELECT '7/7/2006 6:50:03 AM',0,1,1,1,32 UNION ALL
SELECT '7/7/2006 6:53:45 AM',0,1,1,1,41 UNION ALL
SELECT '7/7/2006 7:11:42 AM',0,1,1,1,23 UNION ALL
SELECT '7/7/2006 7:33:36 AM',0,1,1,1,25 UNION ALL
SELECT '7/7/2006 7:47:10 AM',0,1,1,1,27 UNION ALL
SELECT '6/15/2006 4:55:57 AM',2015,0,0,1,11 UNION ALL
SELECT '7/7/2006 12:09:21 AM',0,1,0,0,14 UNION ALL
SELECT '7/7/2006 12:12:07 AM',596,0,0,0,11 UNION ALL
SELECT '7/7/2006 12:13:56 AM',2018,0,1,1,10 UNION ALL
SELECT '7/7/2006 12:16:19 AM',597,1,0,0,11 UNION ALL
SELECT '7/7/2006 12:17:09 AM',0,1,0,0,10 UNION ALL
SELECT '7/7/2006 12:30:55 AM',1508,0,1,1,12 UNION ALL
SELECT '7/7/2006 12:33:34 AM',0,1,0,0,12 UNION ALL
SELECT '7/7/2006 12:41:07 AM',1527,0,1,1,8 UNION ALL
SELECT '7/7/2006 12:45:34 AM',0,1,0,0,8 UNION ALL
SELECT '7/7/2006 12:51:07 AM',59,1,0,0,7 UNION ALL
SELECT '7/7/2006 12:52:33 AM',571,0,1,0,10 UNION ALL
SELECT '7/7/2006 12:56:21 AM',1508,0,1,1,11 UNION ALL
SELECT '7/7/2006 12:58:56 AM',0,1,0,0,11 UNION ALL
SELECT '7/7/2006 1:02:58 AM',1687,0,0,0,13 UNION ALL
SELECT '7/7/2006 1:03:17 AM',573,1,0,0,10 UNION ALL
SELECT '7/7/2006 1:03:47 AM',2025,0,1,1,2 UNION ALL
SELECT '7/7/2006 1:04:14 AM',1689,1,0,0,13 UNION ALL
SELECT '7/7/2006 1:05:55 AM',0,1,0,0,2 UNION ALL
SELECT '7/7/2006 1:12:54 AM',369,0,1,0,7 UNION ALL
SELECT '7/7/2006 1:19:41 AM',2018,0,1,1,13 UNION ALL
SELECT '7/7/2006 1:21:44 AM',372,1,0,0,7 UNION ALL
SELECT '7/7/2006 1:24:03 AM',0,1,0,0,13 UNION ALL
SELECT '7/7/2006 1:35:02 AM',1492,0,0,0,1 UNION ALL
SELECT '7/7/2006 1:36:01 AM',1496,1,0,0,1 UNION ALL
SELECT '7/7/2006 1:39:58 AM',341,0,0,0,13 UNION ALL
SELECT '7/7/2006 1:41:34 AM',342,1,0,0,13 UNION ALL
SELECT '7/7/2006 1:43:36 AM',2015,0,1,1,14 UNION ALL
SELECT '7/7/2006 1:49:46 AM',0,1,0,0,14 UNION ALL
SELECT '7/7/2006 1:53:08 AM',1899,0,0,0,9 UNION ALL
SELECT '7/7/2006 2:05:08 AM',1510,0,1,1,11 UNION ALL
SELECT '7/7/2006 2:06:43 AM',1982,0,0,0,1 UNION ALL
SELECT '7/7/2006 2:09:14 AM',0,1,0,0,1 UNION ALL
SELECT '7/7/2006 2:10:44 AM',1509,0,1,1,12 UNION ALL
SELECT '7/7/2006 2:14:37 AM',0,1,0,0,11 UNION ALL
SELECT '7/7/2006 2:18:40 AM',0,1,0,0,12 UNION ALL
SELECT '7/7/2006 2:20:31 AM',1529,0,1,1,8 UNION ALL
SELECT '7/7/2006 2:23:22 AM',1900,1,0,0,9 UNION ALL
SELECT '7/7/2006 2:23:50 AM',0,1,0,0,8 UNION ALL
SELECT '7/7/2006 2:26:39 AM',1339,0,0,0,7 UNION ALL
SELECT '7/7/2006 2:29:08 AM',1340,1,0,0,7 UNION ALL
SELECT '7/7/2006 2:29:19 AM',1342,0,0,0,7 UNION ALL
SELECT '7/7/2006 2:30:55 AM',369,0,0,0,11 UNION ALL
SELECT '7/7/2006 2:32:05 AM',2038,0,1,1,9 UNION ALL
SELECT '7/7/2006 2:32:21 AM',2019,0,1,1,10 UNION ALL
SELECT '7/7/2006 2:33:49 AM',372,1,0,0,11 UNION ALL
SELECT '7/7/2006 2:34:24 AM',0,1,0,0,9 UNION ALL
SELECT '7/7/2006 2:36:50 AM',0,1,0,0,10 UNION ALL
SELECT '7/7/2006 2:40:25 AM',1343,1,0,0,7 UNION ALL
SELECT '7/7/2006 2:42:17 AM',296,0,1,0,8 UNION ALL
SELECT '7/7/2006 2:46:02 AM',1433,0,0,0,7 UNION ALL
SELECT '7/7/2006 2:49:24 AM',299,1,0,0,8 UNION ALL
SELECT '7/7/2006 2:50:14 AM',1434,1,0,0,7 UNION ALL
SELECT '7/7/2006 2:50:51 AM',1693,0,1,0,2 UNION ALL
SELECT '7/7/2006 2:52:11 AM',1854,0,1,0,13 UNION ALL
SELECT '7/7/2006 2:52:22 AM',1697,1,0,0,2 UNION ALL
SELECT '7/7/2006 2:55:11 AM',567,0,1,0,12 UNION ALL
SELECT '7/7/2006 3:02:09 AM',1857,1,0,0,13 UNION ALL
SELECT '7/7/2006 3:03:49 AM',1584,0,0,0,14 UNION ALL
SELECT '7/7/2006 3:09:08 AM',1586,1,0,0,14 UNION ALL
SELECT '7/7/2006 3:09:48 AM',2018,0,1,1,13 UNION ALL
SELECT '7/7/2006 3:11:04 AM',570,1,0,0,12 UNION ALL
SELECT '7/7/2006 3:13:08 AM',2028,0,1,1,2 UNION ALL
SELECT '7/7/2006 3:14:50 AM',712,0,0,0,8 UNION ALL
SELECT '7/7/2006 3:14:54 AM',0,1,0,0,2 UNION ALL
SELECT '7/7/2006 3:15:56 AM',0,1,0,0,13 UNION ALL
SELECT '7/7/2006 3:18:34 AM',712,1,0,0,8 UNION ALL
SELECT '7/7/2006 3:23:47 AM',1507,0,1,1,11 UNION ALL
SELECT '7/7/2006 3:27:20 AM',2035,0,1,1,7 UNION ALL
SELECT '7/7/2006 3:28:01 AM',0,1,0,0,11 UNION ALL
SELECT '7/7/2006 3:29:22 AM',2017,0,1,1,14 UNION ALL
SELECT '7/7/2006 3:32:52 AM',0,1,0,0,7 UNION ALL
SELECT '7/7/2006 3:34:37 AM',0,1,0,0,14 UNION ALL
SELECT '7/7/2006 3:39:16 AM',1014,0,1,0,10 UNION ALL
SELECT '7/7/2006 3:40:47 AM',1017,1,0,0,10 UNION ALL
SELECT '7/7/2006 3:41:44 AM',1083,0,1,0,9 UNION ALL
SELECT '7/7/2006 3:42:08 AM',1085,1,0,0,9 UNION ALL
SELECT '7/7/2006 3:42:12 AM',1085,0,0,0,9 UNION ALL
SELECT '7/7/2006 3:42:28 AM',1086,1,0,0,9 UNION ALL
SELECT '7/7/2006 3:51:40 AM',1635,0,1,0,1 UNION ALL
SELECT '7/7/2006 3:59:31 AM',1639,1,0,0,1 UNION ALL
SELECT '7/7/2006 4:08:54 AM',1529,0,1,1,8 UNION ALL
SELECT '7/7/2006 4:11:45 AM',0,1,0,0,8 UNION ALL
SELECT '7/7/2006 4:13:45 AM',1509,0,1,1,12 UNION ALL
SELECT '7/7/2006 4:16:08 AM',0,1,0,0,12 UNION ALL
SELECT '7/7/2006 4:24:05 AM',2011,0,1,1,1 UNION ALL
SELECT '7/7/2006 4:26:03 AM',0,1,0,0,1 UNION ALL
SELECT '7/7/2006 4:34:31 AM',1509,0,1,1,11 UNION ALL
SELECT '7/7/2006 4:37:22 AM',0,1,0,0,11 UNION ALL
SELECT '7/7/2006 4:41:18 AM',2038,0,1,1,9 UNION ALL
SELECT '7/7/2006 4:42:29 AM',2020,0,1,1,10 UNION ALL
SELECT '7/7/2006 4:43:27 AM',0,1,0,0,9 UNION ALL
SELECT '7/7/2006 4:46:17 AM',199,0,1,0,11 UNION ALL
SELECT '7/7/2006 4:46:23 AM',0,1,0,0,10 UNION ALL
SELECT '7/7/2006 4:51:27 AM',2015,0,1,1,13 UNION ALL
SELECT '7/7/2006 4:52:30 AM',99,0,0,0,10 UNION ALL
SELECT '7/7/2006 4:52:41 AM',1559,0,0,0,2 UNION ALL
SELECT '7/7/2006 4:52:41 AM',419,0,0,0,1 UNION ALL
SELECT '7/7/2006 4:54:00 AM',100,1,0,0,10 UNION ALL
SELECT '7/7/2006 4:57:40 AM',0,1,0,0,13 UNION ALL
SELECT '7/7/2006 4:58:56 AM',1562,1,0,0,2 UNION ALL
SELECT '7/7/2006 4:59:32 AM',422,1,0,0,1 UNION ALL
SELECT '7/7/2006 5:03:19 AM',251,0,0,0,10 UNION ALL
SELECT '7/7/2006 5:04:15 AM',203,1,0,0,11 UNION ALL
SELECT '7/7/2006 5:06:38 AM',253,0,1,0,11 UNION ALL
SELECT '7/7/2006 5:09:04 AM',2017,0,1,1,14 UNION ALL
SELECT '7/7/2006 5:11:28 AM',257,1,0,0,11 UNION ALL
SELECT '7/7/2006 5:12:54 AM',0,1,0,0,14 UNION ALL
SELECT '7/7/2006 5:20:45 AM',604,0,0,0,9 UNION ALL
SELECT '7/7/2006 5:22:08 AM',1770,0,1,0,7 UNION ALL
SELECT '7/7/2006 5:22:26 AM',201,0,0,0,14 UNION ALL
SELECT '7/7/2006 5:24:01 AM',204,1,0,0,14 UNION ALL
SELECT '7/7/2006 5:24:32 AM',252,1,0,0,10 UNION ALL
SELECT '7/7/2006 5:25:37 AM',267,0,0,0,10 UNION ALL
SELECT '7/7/2006 5:27:48 AM',2026,0,1,1,2 UNION ALL
SELECT '7/7/2006 5:28:27 AM',638,0,0,0,11 UNION ALL
SELECT '7/7/2006 5:28:29 AM',1774,1,0,0,7 UNION ALL
SELECT '7/7/2006 5:29:46 AM',0,1,0,0,2 UNION ALL
SELECT '7/7/2006 5:40:39 AM',641,1,0,0,11 UNION ALL
SELECT '7/7/2006 5:42:03 AM',589,0,0,0,14 UNION ALL
SELECT '7/7/2006 5:44:38 AM',2033,0,1,1,7 UNION ALL
SELECT '7/7/2006 5:45:41 AM',1530,0,1,1,8 UNION ALL
SELECT '7/7/2006 5:47:22 AM',0,1,0,0,7 UNION ALL
SELECT '7/7/2006 5:50:25 AM',590,1,0,0,14 UNION ALL
SELECT '7/7/2006 5:55:10 AM',1510,0,1,1,12 UNION ALL
SELECT '7/7/2006 5:58:01 AM',1,1,0,0,8 UNION ALL
SELECT '7/7/2006 5:59:18 AM',0,1,0,0,12 UNION ALL
SELECT '7/7/2006 6:02:37 AM',606,1,0,0,9 UNION ALL
SELECT '7/7/2006 6:02:48 AM',1136,0,0,0,11 UNION ALL
SELECT '7/7/2006 6:07:47 AM',269,1,0,0,10 UNION ALL
SELECT '7/7/2006 6:08:36 AM',280,0,0,0,10 UNION ALL
SELECT '7/7/2006 6:09:32 AM',282,1,0,0,10 UNION ALL
SELECT '7/7/2006 6:09:45 AM',284,0,0,0,10 UNION ALL
SELECT '7/7/2006 6:22:03 AM',1141,1,0,0,11 UNION ALL
SELECT '7/7/2006 6:31:15 AM',1964,0,0,0,13 UNION ALL
SELECT '7/7/2006 6:34:06 AM',1409,0,0,0,11 UNION ALL
SELECT '7/7/2006 6:39:51 AM',2012,0,1,1,1 UNION ALL
SELECT '7/7/2006 6:41:30 AM',0,1,0,0,1 UNION ALL
SELECT '7/7/2006 6:44:20 AM',286,1,0,0,10 UNION ALL
SELECT '7/7/2006 6:45:00 AM',296,0,1,0,10 UNION ALL
SELECT '7/7/2006 6:55:55 AM',1967,1,0,0,13 UNION ALL
SELECT '7/7/2006 6:55:57 AM',1967,0,1,0,13 UNION ALL
SELECT '7/7/2006 6:56:48 AM',2015,0,1,1,14 UNION ALL
SELECT '7/7/2006 6:57:27 AM',1968,1,0,0,13 UNION ALL
SELECT '7/7/2006 6:59:24 AM',0,1,0,0,14 UNION ALL
SELECT '7/7/2006 6:59:26 AM',912,0,1,0,12 UNION ALL
SELECT '7/7/2006 7:00:09 AM',2018,0,1,1,13 UNION ALL
SELECT '7/7/2006 7:03:53 AM',0,1,0,0,13 UNION ALL
SELECT '7/7/2006 7:12:33 AM',283,0,1,0,14 UNION ALL
SELECT '7/7/2006 7:21:27 AM',366,0,1,0,13 UNION ALL
SELECT '7/7/2006 7:21:28 AM',298,1,0,0,10 UNION ALL
SELECT '7/7/2006 7:22:13 AM',1351,0,0,0,8 UNION ALL
SELECT '7/7/2006 7:32:07 AM',2038,0,1,1,9 UNION ALL
SELECT '7/7/2006 7:33:57 AM',1412,1,0,0,11 UNION ALL
SELECT '7/7/2006 7:34:38 AM',0,1,0,0,9 UNION ALL
SELECT '7/7/2006 7:35:58 AM',2026,0,1,1,2 UNION ALL
SELECT '7/7/2006 7:38:16 AM',1508,0,1,1,11 UNION ALL
SELECT '7/7/2006 7:39:44 AM',0,1,0,0,2 UNION ALL
SELECT '7/7/2006 7:42:04 AM',915,1,0,0,12 UNION ALL
SELECT '7/7/2006 7:48:46 AM',1353,1,0,0,8 UNION ALL
SELECT '7/7/2006 7:51:25 AM',1056,0,1,0,12 UNION ALL
SELECT '7/7/2006 7:52:51 AM',2035,0,1,1,7 UNION ALL
SELECT '7/7/2006 7:53:32 AM',1130,0,1,0,1 UNION ALL
SELECT '7/7/2006 7:55:36 AM',334,0,1,0,9 UNION ALL
SELECT '7/7/2006 7:55:40 AM',848,0,1,0,10 UNION ALL
SELECT '7/7/2006 7:57:03 AM',271,0,1,0,2 UNION ALL
SELECT '7/7/2006 7:59:42 AM',1530,0,1,1,8 UNION ALL
SELECT '6/15/2006 3:52:21 AM',1511,0,0,1,12


--CREATES tM_FR_WIP_UnitID_FastStops Table

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tM_FR_WIP_UnitID_FastStops]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tM_FR_WIP_UnitID_FastStops]
GO

CREATE TABLE [dbo].[tM_FR_WIP_UnitID_FastStops] (
[nLine] [int] NULL ,
[Weight] [bigint] NULL ,
[StartGroup] [datetime] NULL ,
[EndGroup] [datetime] NULL ,
[UnitNo] [numeric](18, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO

--INSERT INTO tM_FR_WIP_UnitID_FastStops Table

INSERT INTO tM_FR_WIP_UnitID_FastStops(nLine,Weight,StartGroup,EndGroup)
SELECT 11,2015,'6/15/2006 3:25:26 AM','6/15/2006 4:55:57 AM' UNION ALL
SELECT 12,1511,'6/15/2006 1:41:43 AM','6/15/2006 3:52:21 AM' UNION ALL
SELECT 1,2011,'7/7/2006 2:09:14 AM','7/7/2006 4:24:05 AM' UNION ALL
SELECT 2,2028,'7/7/2006 1:05:55 AM','7/7/2006 3:13:08 AM' UNION ALL
SELECT 13,2018,'7/7/2006 4:57:40 AM','7/7/2006 7:00:09 AM'
Go to Top of Page

chippyles
Yak Posting Veteran

68 Posts
Posted - 2006-07-07 : 09:21:18
I forgot to list what the results should be...


nLine	Weight	StartGroup	EndGroup	UnitNo	bFast_Stop
11	2015	6/15/06 3:25 AM	6/15/06 4:55 AM	1	1
12	1511	6/15/06 1:41 AM	6/15/06 3:52 AM	2	3
1	2011	7/7/06 2:09 AM	7/7/06 4:24 AM	3	0
2	2028	7/7/06 1:05 AM	7/7/06 3:13 AM	4	0
13	2018	7/7/06 4:57 AM	7/7/06 7:00 AM	5	1

nLine	bFast_Stop				
1	0				
2	0				
11	1				
12	3				
13	1
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2006-07-07 : 12:28:43
I do not understand your new expected result set, specifically the ones that say 0 for the fast stop part. Let's take nLine = 1 for example.

SELECT * FROM tM_FR_STOPS WHERE nLINE = 1

This returns me:


tRSSQL_TRANS                                           nLINE nWEIGHT     bLINE_START bFAST_STOP bEND_OF_REEL 
------------------------------------------------------ ----- ----------- ----------- ---------- ------------ 
2006-07-07 01:35:02.000                                1     1492        0           0          0
2006-07-07 01:36:01.000                                1     1496        1           0          0
2006-07-07 02:06:43.000                                1     1982        0           0          0
2006-07-07 02:09:14.000                                1     0           1           0          0
2006-07-07 03:51:40.000                                1     1635        0           1          0
2006-07-07 03:59:31.000                                1     1639        1           0          0
2006-07-07 04:24:05.000                                1     2011        0           1          1
2006-07-07 04:26:03.000                                1     0           1           0          0
2006-07-07 04:52:41.000                                1     419         0           0          0
2006-07-07 04:59:32.000                                1     422         1           0          0
2006-07-07 06:39:51.000                                1     2012        0           1          1
2006-07-07 06:41:30.000                                1     0           1           0          0
2006-07-07 07:53:32.000                                1     1130        0           1          0


SELECT * FROM tM_FR_WIP_UnitID_FastStops WHERE nLINE = 1

This returns me:


nLine       Weight               StartGroup                                             EndGroup                                               UnitNo               
----------- -------------------- ------------------------------------------------------ ------------------------------------------------------ -------------------- 
1           2011                 2006-07-07 02:09:14.000                                2006-07-07 04:24:05.000                                3


So we've got dates from 2006-07-07 02:09:14.000 to 2006-07-07 04:24:05.000.

So now let's look at tM_FR_WIP_UnitID_FastStops again but only with the data that is between those dates.

SELECT * FROM tM_FR_STOPS WHERE nLINE = 1
AND tRSSQL_TRANS BETWEEN '2006-07-07 02:09:14.000' AND '2006-07-07 04:24:05.000'

This returns me:


tRSSQL_TRANS                                           nLINE nWEIGHT     bLINE_START bFAST_STOP bEND_OF_REEL 
------------------------------------------------------ ----- ----------- ----------- ---------- ------------ 
2006-07-07 02:09:14.000                                1     0           1           0          0
2006-07-07 03:51:40.000                                1     1635        0           1          0
2006-07-07 03:59:31.000                                1     1639        1           0          0
2006-07-07 04:24:05.000                                1     2011        0           1          1




If we sum up bFast_Stop, we get 2 not 0.

So how do you get 0 for nLine = 1?

Tara Kizer
aka tduggan
Go to Top of Page

chippyles
Yak Posting Veteran

68 Posts
Posted - 2006-07-07 : 14:23:51
Line 1 is my bad. I was looking at the wrong data set.

I added line 12 to the criteria and I noticed the problem I am trying to describe. Here is the new code for the INSERT.


--INSERT INTO tM_FR_WIP_UnitID_FastStops Table

INSERT INTO tM_FR_WIP_UnitID_FastStops(nLine,Weight,StartGroup,EndGroup)
SELECT 11,2015,'6/15/2006 3:25:26 AM','6/15/2006 4:55:57 AM' UNION ALL
SELECT 12,1511,'6/15/2006 1:41:43 AM','6/15/2006 3:52:21 AM' UNION ALL
SELECT 1,2011,'7/7/2006  2:09:14 AM','7/7/2006  4:24:05 AM' UNION ALL
SELECT 2,2028,'7/7/2006  1:05:55 AM','7/7/2006  3:13:08 AM' UNION ALL
SELECT 13,2018,'7/7/2006  4:57:40 AM','7/7/2006  7:00:09 AM' UNION ALL
SELECT 12,1509,'7/7/2006 12:33:34 AM','7/7/2006 2:10:44 AM'


When you execute your code you will see that two records of 12 line having 4 Fast Stops each. Technically, one record of 12 line has 3 stops and the other has 1.

I was able to modify your code and retrieve good results. I basically grouped on EndGroup. So I think we are on the right track. However, I am a little lost when it comes to your other query. If you can help me with that, it would be much appreciated.


--This seems to work!!
SELECT     TOP 100 PERCENT fs.nLine, fs.EndGroup, SUM(s.bFAST_STOP) AS bFAST_STOP
FROM         dbo.tM_FR_WIP_UnitID_FastStops fs INNER JOIN
                      dbo.tM_FR_STOPS s ON fs.StartGroup <= s.tRSSQL_TRANS AND fs.EndGroup >= s.tRSSQL_TRANS AND fs.nLine = s.nLINE
GROUP BY fs.nLine, fs.EndGroup
ORDER BY fs.EndGroup



--Not sure how to do what I did in the above code here.
SELECT fs.nLine, fs.Weight, fs.StartGroup, fs.EndGroup, fs.UnitNo, t.bFAST_STOP
FROM tM_FR_WIP_UnitID_FastStops fs
INNER JOIN
(
	SELECT f.nLine, SUM(bFAST_STOP) AS bFAST_STOP
	FROM tM_FR_WIP_UnitID_FastStops f
	INNER JOIN tM_FR_STOPS s
	ON f.StartGroup <= s.tRSSQL_TRANS AND f.EndGroup >= s.tRSSQL_TRANS AND f.nLine = s.nLINE
	GROUP BY f.nLine
) t
ON fs.nLine = t.nLine
Go to Top of Page

chippyles
Yak Posting Veteran

68 Posts
Posted - 2006-07-07 : 14:27:45
I think I am getting it now...


SELECT     fs.nLine, fs.Weight, fs.StartGroup, fs.EndGroup, fs.UnitNo, t.bFAST_STOP
FROM         dbo.tM_FR_WIP_UnitID_FastStops fs INNER JOIN
                          (SELECT     f.nLine, SUM(bFAST_STOP) AS bFAST_STOP, f.EndGroup
                            FROM          tM_FR_WIP_UnitID_FastStops f INNER JOIN
                                                   tM_FR_STOPS s ON f.StartGroup <= s.tRSSQL_TRANS AND f.EndGroup >= s.tRSSQL_TRANS AND f.nLine = s.nLINE
                            GROUP BY f.nLine, f.EndGroup) t ON fs.nLine = t.nLine AND fs.EndGroup = t.EndGroup
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site