Help with query - SQL Server Forums

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

SQL Server Development (2000)

Help with query

Author

Topic

jpiscit1
Posting Yak Master

130 Posts

Posted - 2005-05-09 : 16:05:05

I have one table: tbl_1PMProduction

The Four columns I am working with are:

RecordDate_Time - Date and Time record (all records are usually at one minute intervals when product is running)
WBDiscoRunning - Has two states OFF/RUNNING
CouchShtBrk - Has two states CLEAR/BREAK
StockPumpRunning - Has two states OFF/RUNNING

The following Select over an hour looks like this:

2005-05-09 11:33:52.400 OFF CLEAR RUNNING
2005-05-09 11:32:52.390 OFF CLEAR RUNNING
2005-05-09 11:31:52.390 OFF CLEAR RUNNING
2005-05-09 11:30:52.370 OFF CLEAR RUNNING
2005-05-09 11:29:52.350 RUNNING CLEAR RUNNING
2005-05-09 11:28:52.340 RUNNING CLEAR RUNNING
2005-05-09 11:27:52.340 RUNNING CLEAR RUNNING
2005-05-09 11:26:52.320 RUNNING CLEAR RUNNING
2005-05-09 11:25:52.310 RUNNING CLEAR RUNNING
2005-05-09 11:24:52.290 RUNNING CLEAR RUNNING
2005-05-09 11:23:52.290 RUNNING CLEAR RUNNING
2005-05-09 11:22:52.280 RUNNING CLEAR RUNNING
2005-05-09 11:21:47.260 RUNNING CLEAR RUNNING
2005-05-09 11:20:47.250 RUNNING CLEAR RUNNING
2005-05-09 11:19:47.230 RUNNING CLEAR RUNNING
2005-05-09 11:18:47.210 RUNNING CLEAR RUNNING
2005-05-09 11:17:47.210 RUNNING CLEAR RUNNING
2005-05-09 11:16:47.200 RUNNING CLEAR RUNNING
2005-05-09 11:15:47.180 RUNNING CLEAR RUNNING
2005-05-09 11:14:47.170 RUNNING CLEAR RUNNING
2005-05-09 11:12:52.170 RUNNING CLEAR RUNNING
2005-05-09 11:11:52.150 RUNNING CLEAR RUNNING
2005-05-09 11:10:52.140 RUNNING CLEAR RUNNING
2005-05-09 11:09:52.120 RUNNING CLEAR RUNNING
2005-05-09 11:08:52.120 RUNNING CLEAR RUNNING
2005-05-09 11:07:47.100 RUNNING CLEAR RUNNING
2005-05-09 11:06:47.090 RUNNING CLEAR RUNNING
2005-05-09 11:05:52.070 RUNNING CLEAR RUNNING
2005-05-09 11:04:47.060 RUNNING BREAK RUNNING
2005-05-09 11:03:47.040 RUNNING BREAK RUNNING
2005-05-09 11:02:47.010 RUNNING BREAK RUNNING
2005-05-09 10:38:47.010 RUNNING CLEAR RUNNING
2005-05-09 10:37:47.000 RUNNING CLEAR RUNNING
2005-05-09 10:36:46.980 RUNNING CLEAR RUNNING
2005-05-09 10:35:46.960 RUNNING CLEAR RUNNING
2005-05-09 10:34:46.960 RUNNING CLEAR RUNNING

I want to create a query that can provide me with the max and min times when the following occurs:
WBDiscoRunning = 'Running' AND CouchShtBrk = 'Clear' AND StockPumpRunning = 'Running'.
But I am not sure how to break it down. For example, if I were to query the above records I would expect to get 2 records results sets:

1st Start time was 2005-05-09 10:34:46.960, Stop time was 2005-05-09 10:38:47.010. Total time = 4...min
2nd start time was 2005-05-09 11:05:52.070, Stop time was 2005-05-09 11:29:52.350. Total time = 34...min

Total time 38 min (34 + 4).

Here was my statement that is not working:

Select
MIN (RecordDate_Time) as [StartTime],
MAX (RecordDate_Time) as StopTime,
(DateDiff (ss, MIN (RecordDate_Time), MAX (RecordDate_Time))/60.0) as TotalTimeMIN
From tbl_1PMProduction
Where (RecordDate_Time >= '5/9/2005 10:34:00 AM' AND RecordDate_Time <= '5/9/2005 11:34:00 AM')
AND (WBDiscoRunning = 'Running' AND CouchShtBrk = 'Clear' AND StockPumpRunning
= 'Running')
Order by MIN (RecordDate_Time) DESC

This produces a total time instead of the itemized time as shown above. Here were the results:

2005-05-09 10:34:46.960
2005-05-09 11:29:52.350
55.100000

Im not sure how to break this down. Can anyone offer a suggestion?

thanks

Your limits are only as far as you set your boundries....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-09 : 16:34:24

Please provide the table structure with CREATE TABLE statement, sample data with INSERT INTO statements, and the expected result set using that sample data.

Tara

jpiscit1
Posting Yak Master

130 Posts

Posted - 2005-05-10 : 07:55:18

Sorry. here it is:

CREATE TABLE [dbo].[test] (
[RecordDate_Time] [datetime] NULL ,
[StockPumpRunning] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CouchShtBrk] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WBDiscoRunning] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:33:52.400','OFF', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:32:52.400','OFF', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:31:52.400','OFF', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:30:52.400','OFF', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:29:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:28:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:27:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:26:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:25:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:24:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:23:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:22:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:21:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:20:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:19:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:18:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:17:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:16:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:15:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:14:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:13:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:12:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:11:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:10:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:09:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:08:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:07:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:06:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:05:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:04:52.400','RUNNING', 'BREAK', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:03:52.400','RUNNING', 'BREAK', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 11:02:52.400','RUNNING', 'BREAK', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 10:38:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 10:38:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 10:37:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 10:36:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 10:35:52.400','RUNNING', 'CLEAR', 'RUNNING')
Insert into tbl_1PMProduction (RecordDate_Time, WBDiscoRunning, CouchShtBrk, StockPumpRunning)
values ('2005-05-09 10:34:52.400','RUNNING', 'CLEAR', 'RUNNING')

Would expect to see:

StopTime StartTime Total Time
2005-05-09 11:29:52.400 2005-05-09 11:05:52.120 24
2005-05-09 10:38:52.400 2005-05-09 10:34:52.400 4

Your limits are only as far as you set your boundries....

jpiscit1
Posting Yak Master

130 Posts

Posted - 2005-05-10 : 12:14:36

Create Table should read

CREATE TABLE [dbo].[tbl_1PMProduction] (
[RecordDate_Time] [datetime] NULL ,
[StockPumpRunning] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CouchShtBrk] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WBDiscoRunning] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Basically. When the conditions =
WBDiscoRunning 'RUNNING'
AND
CouchShtBrk = 'CLEAR'
AND
StockPumpRunning = 'RUNNING'
We are making money.

If any of those conditions are not met, we are not. I want to know the time each time we made money from the start (when conditions are true) to the stop (when conditions are not true).

I am looking for a break down of these times since it changes throughout a day.

Thanks.

Your limits are only as far as you set your boundries....

jpiscit1
Posting Yak Master

130 Posts

Posted - 2005-05-11 : 06:01:29

As requested the table structure has been provided and insert statements as needed. Any body have any suggestions how to structure the query I am looking for?

Need more info ?

Your limits are only as far as you set your boundries....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources