Need opinions on a query to evaluate recurring out of limit values

Question

I’ve been asked to create a report that shows the number of times
a set of values is consecutively out of limit. Once I have regressed through
the record set and I don’t find any more recurring out of limit values then we’re
done. Sounds fairly simple doesn’t it? Here’s the problem;  The data being logged is for a set of 30
machines. For every 12 hour shift I log 30 machines x 540 sections per machine
x 9 values per section that are evaluated. That’s 16,200 records per shift. I
am more or less perplexed on how to format a query to go back through each
record looking for consecutive out of limit values. It would have to be done by
Shift, by machine, and by section… so each section is compared to the previous
shift’s section for each machine until we find no more consecutive out of limit
values. I thought about doing this in one elaborate stored procedure but I’m
thinking this is going to be too complex. My other option is to grab a chunk of
data and have it sorted properly to allow the application to make the
evaluation and determination about the recurring values. For example I could
grab a week’s worth of data, that’s 226,800 records, and I could start spinning
through it to look for the recurrences. That approach I think would be very
resource intensive. I really don’t know the best approach and would like an
opinion on a possible method to generate the intended results.

Answer 1

This sounds to be more of functional description of your requirement.

What does out of limit mean. Is the value greater than a threshold value or something else.

hours, machines, sections, values: Are these technically column names/ tables or kind.

A better technical description would help others.

---

Welcome to MSDN Forums.
Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

You Value Community's efforts and Community values your faith.
- Arun Kumar Allu

Answer 2

Each one of the 9 values has some sort of limit that is evaluated by a comparison operator, i.e Value1 > 80, Value2 < 70, and so on.

The table structure looks like this;

CREATE Table #MachData (ShiftId Int, MachineNo SmallInt, MotorNo SmallInt, Val1 SmallInt, Val2 SmallInt, Val3 SmallInt, Val4 SmallInt, Val5 SmallInt, Val6 SmallInt, Val7 SmallInt, Val8 SmallInt, Val9 SmallInt)

DROP Table #MachData

For each Shift there are 30 Machines. For each Machine there are 540 motors. For each motor there is 9 pieces of data recorded for it. I need to evaluate the most recent value of each motor to (a) see if it is out of limit and if it is then (b) see if the previous shift's value is also out of limit, and then (c) continue the comparison until the value is no longer out of limit. In the end I need a count of the number of times this particular value has consecutively been out of limit from the most recent shift that has been queried. Each value may or may not be out of limit and the number of times the value has been out of limit could vary. If you think it would be beneficial I could dummy up some DDL for a sample. My inquiry was more or less for asking for an "approach" to this problem instead of looking for some TSQL to solve it... although it may come to that. I hope this helps.

Answer 3

What exactly do you mean by " see if it is out of limit ", since you have nine values. Do you want to track each value individually or do you call "out of limit" if any of the nine values were out of the limit?

It is extremelly helpful posting sample data in the form of "insert" statement, and expected result.

---

AMB

Some guidelines for posting questions...

Answer 4

You could write a query that would return the result you want from this table, but given the amount of data you will have, the query would be very inefficient.  And if this is a one time query or very rarely run query that you can do in off hours, then I would write that query.  If that's the way you want to go, let us know, we should be able to help with it.

But I'm guessing from your discussion so far, your going to run this query at least moderately often.  So you may want to go a different way.  You could create a new table

Create #MachStatus(MachineNo SmallInt, MotorNo SmallInt, NbrOfTimesOutOfLimit SmallInt)

Then whenever you insert new rows in the #MachData table you update the data in this table also.  The psuedo code for that would be something like

Insert new row in #MachData, then check to see if this MachineNo and MotorNo is already in the #MachStatus table.  If it isn't, add it to that table setting the value of NbrOfTimesOutOfLimit to either zero or one depending on whether or not the row added to #MachData is within limits.  If there is already a row for this MachineNo and MotorNo in the #MachStatus table, then if the new row is within limits, just set the value of NbrOfTimesOutOfLimit to 0, if it is out of limits, add one to the value of NbrOfTimesOutOfLimit.  This insert or update could be done with a single MERGE statement.

You would need to be sure that every processs that inserts rows in #MachData also correctly updates #MachStatus.  One way to do this would be to put an INSERT TRIGGER on #MachData.

Once you have done this then finding the number of consecutive times each machine/motor has been out of limits is just

SELECT MachineNo, MotorNo, NbrOfTimesOutOfLimit FROM #MachStatus

You should note that doing this in this manner violates normalization rules.  So you get all the problems that come with doing that.  It not much of a problem if all you ever do is insert new rows into #MachData.  It won't work well if you commonly delete or update old rows in #MachData.  It also only helps if the result you want is how many consecutive times has each Machine/Motor been out of limits as of the current time.  It won't help you get the result of a question like how many consecutive times was each Machine/Motor out of limits as of last Tuesday.

Tom

Answer 5

An approach would be to reach the point where you have a resultset of all Shifts, and within that each machine and motor and reading, but also a generated row-number (probably via "row_number() over..." for each Shift.  Then an ordered by SELECT from that list could be ordered by Machine/Motor/Shift.

Then a "select distinct RowNumber" on that list, and join it to itself looking for any match where the row-number is either 1 over or 1 below the Left sides row number.  The "DISTINCT" portion  of the query is just to generate a list of rogue rownumbers (without further work, the list from the join would have paired off results sometimes, but not others: using DISTINCT is a decent shortcut just to get the list).  Then use that list as a subquery or with a CTE, to pull the full dataset.

Here's a mocked up and elementary version of the approach, using nested CTEs. 

Declare @TT Table (ID int, Reading int)
Declare @LowestValid int, @highestValid int

Set @LowestValid = 3
Set @HighestValid = 4

Insert @TT (ID, Reading) -- Generate a list of test data
            Select 1, 1		-- Bad
  UNION ALL Select 2, 1		-- Bad
  UNION ALL Select 3, 2		-- Bad
  UNION ALL Select 4, 2		-- Bad
  UNION ALL Select 5, 2		-- Bad
  UNION ALL Select 6, 3		-- Good
  UNION ALL Select 7, 3		-- Good	
  Union all Select 8, 4		-- Good
  Union All Select 9, 4		-- Good
  UNION ALL Select 10, 5	-- Bad
  UNION ALL Select 11, 5	-- Bad
  UNION ALL Select 12, 6	-- Bad
  UNION ALL Select 13, 7	-- Bad
  UNION ALL Select 14, 4	-- Good
  UNION ALL Select 15, 4	-- Good
  UNION ALL Select 16, 9	-- Bad, but NOT contiguous to another bad value, so won't be listed
  UNION ALL Select 17, 4	-- Good
  UNION ALL Select 18, 9	-- Bad
  UNION ALL Select 19, 9	-- Bad
  
 ;With OBLISTMAKER (ID, Reading, IsValid) as 
 (
	 Select *
	  , Case When Reading between @LowestValid and @HighestValid then 1 else 0 End as IsValid
	   From @TT
 )
 , OBLISTIDONLY (ID) as 
 (
	 Select Distinct OL1.ID from OBLISTMAKER OL1 -- Just generates the list of IDs that were consecutive "Bad" ones.  
	  Inner Join OBLISTMAKER OL2 on Abs(OL2.ID - OL1.ID) = 1 and OL2.IsValid = 0
	  Where OL1.IsValid = 0
 )
 Select * from OBLISTIDONLY 
  Order by ID

Answer 6

Thank you all for the responses and the suggestions. Actually since my original inquiry the scope has changed a bit and the user now only wants to report on 3 values instead of 9. Johnqflorida, I will try to take your example and apply it to my table. It will be some 8 or 10 hours before I can report back and/or post some markup to help make some sense of all this. Until then, thanks again for the help.

Answer 7

My apologies for taking so long to get back to my inquiry. I was diverted to another project but I am now back to this problem. I have tried some of the suggestions but I am not getting the correct output. Here's some markup and a sample of what the output should look like.

CREATE Table #TempTable (ShiftId Int, Machine SmallInt, Motor SmallInt, Val1 SmallInt, Val2 SmallInt, Val3 SmallInt)

/******************************************************
    NOTE: ShiftId's are not necessarily sequential.
******************************************************/

-- Shift #1 / Machine #1
INSERT INTO #TempTable VALUES(1, 1,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(1, 1,	2, 100, 100, 100) -- Motor 2
INSERT INTO #TempTable VALUES(1, 1,	3, 100, 100, 100) -- Motor 3

-- Shift #1 / Machine #2
INSERT INTO #TempTable VALUES(1, 2,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(1, 2,	2, 100, 100, 100) -- Motor 2
INSERT INTO #TempTable VALUES(1, 2,	3, 100, 100, 100) -- Motor 3

-- Shift #1 / Machine #3
INSERT INTO #TempTable VALUES(1, 3,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(1, 3,	2, 100, 100, 100) -- Motor 2
INSERT INTO #TempTable VALUES(1, 3,	3, 100, 100, 100) -- Motor 3

/*-------------------------------------------*/

-- Shift #2 / Machine #1
INSERT INTO #TempTable VALUES(2, 1,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(2, 1,	2, 100, 100, 100) -- Motor 2
INSERT INTO #TempTable VALUES(2, 1,	3, 100, 100, 100) -- Motor 3

-- Shift #2 / Machine #2
INSERT INTO #TempTable VALUES(2, 2,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(2, 2,	2, 100, 101, 100) -- Motor 2
INSERT INTO #TempTable VALUES(2, 2,	3, 100, 100, 100) -- Motor 3

-- Shift #2 / Machine #3
INSERT INTO #TempTable VALUES(2, 3,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(2, 3,	2, 100, 100, 100) -- Motor 2
INSERT INTO #TempTable VALUES(2, 3,	3, 100, 100, 100) -- Motor 3

/*-------------------------------------------*/

-- Shift #4 / Machine #1
INSERT INTO #TempTable VALUES(4, 1,	1, 199, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(4, 1,	2, 100, 100, 100) -- Motor 2
INSERT INTO #TempTable VALUES(4, 1,	3, 100, 100, 100) -- Motor 3

-- Shift #4 / Machine #2
INSERT INTO #TempTable VALUES(4, 2,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(4, 2,	2, 100, 101, 100) -- Motor 2
INSERT INTO #TempTable VALUES(4, 2,	3, 100, 100, 100) -- Motor 3

-- Shift #4 / Machine #3
INSERT INTO #TempTable VALUES(4, 3,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(4, 3,	2, 100, 100, 100) -- Motor 2
INSERT INTO #TempTable VALUES(4, 3,	3, 100, 100, 100) -- Motor 3

/*-------------------------------------------*/

-- Shift #5 / Machine #1
INSERT INTO #TempTable VALUES(5, 1,	1, 199, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(5, 1,	2, 100, 100, 100) -- Motor 2
INSERT INTO #TempTable VALUES(5, 1,	3, 100, 100, 100) -- Motor 3

-- Shift #5 / Machine #2
INSERT INTO #TempTable VALUES(5, 2,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(5, 2,	2, 100, 100, 100) -- Motor 2
INSERT INTO #TempTable VALUES(5, 2,	3, 100, 100, 100) -- Motor 3

-- Shift #5 / Machine #3
INSERT INTO #TempTable VALUES(5, 3,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(5, 3,	2, 100, 100, 100) -- Motor 2
INSERT INTO #TempTable VALUES(5, 3,	3, 100, 100, 100) -- Motor 3

/*-------------------------------------------*/

-- Shift #7 / Machine #1
INSERT INTO #TempTable VALUES(7, 1,	1, 199, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(7, 1,	2, 100, 100, 100) -- Motor 2
INSERT INTO #TempTable VALUES(7, 1,	3, 100, 100, 100) -- Motor 3

-- Shift #7 / Machine #2
INSERT INTO #TempTable VALUES(7, 2,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(7, 2,	2, 100, 100, 100) -- Motor 2
INSERT INTO #TempTable VALUES(7, 2,	3, 100, 100, 100) -- Motor 3

-- Shift #7 / Machine #3
INSERT INTO #TempTable VALUES(7, 3,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(7, 3,	2, 100, 100, 100) -- Motor 2
INSERT INTO #TempTable VALUES(7, 3,	3, 100, 100, 100) -- Motor 3

/*-------------------------------------------*/

DECLARE @ShiftId Int = 4
DECLARE @AlarmVal1 SmallInt = 125
DECLARE @AlarmVal2 SmallInt = 100
DECLARE @AlarmVal3 SmallInt = 50


SELECT * FROM #TempTable

go

DROP Table #TempTable

My objective is to show the number of consecutive days that one or more of the motor values was out of tolerance from the specified ShiftId. Note that the ShiftId is not sequential but it is ordered. In my example I have motor #2 out of tolerance during shifts 2 & 4. I would expect that the number of out of tolerance days would = 2 if shift 4 was selected. If shift 2 was selected then the number of out of tolerance days for motor 2 would be = 1. We're looking at this regressively so it is from the shift picked and shifts prior, not subsequent to the shift picked.

-- Sample output

ShiftId Machine Motor Val1 Val1Days Val2 Val2Days Val3 Val3Days
4           2            2     100       0       101     2         100     0

Right now I have more than 2,600,000 records in this table. My attempts at this have been somewhat inefficient and because of the skips in the ShiftId I can't seem to get a number past the skip. This is kicking my butt and I would appreciate any insight on how to solve this.

Answer 8

I think these gaps in the ShiftId are the issue since I don't have any sort of consecutive value to go against. I'll see if there is a way for me to use a "ShiftSequence" number. I do have a ShiftSchedule table where the start and end shift dates and times are defined and hopefully that will be the place to put this shiftsequence number and I won't have to modify any other tables or stored procedures.

Answer 9

Is there a typo in the example or your narrative?  I do see two out of range (OOR) examples for shift 4, but not two OORs for machine 2, it's one OOR for machine 1 and one OOR for machine 2.

Here's one way to get a sequential number for shift, btw:

SELECT * 
  , Row_Number() over(partition by shiftid order by shiftid) as ShifSeq
  FROM #TempTable

EDIT: Actually, you may like this better:

SELECT * 
  , Row_Number() over(partition by shiftid order by shiftid) as ShifSeq
  , Dense_Rank() over( order by shiftid) as ShiftNo
  FROM #TempTable

Answer 10

Shift 2, Machine 2, Motor 2, Value 2 AND Shift 4, Machine 2, Motor 2, Value 2 are two consecutive out of tolerance numbers.

Before we go too far I think I'm really close to getting the results that I want. I'm able to generate some results based on the ShiftId but it only returns data for sequential ShiftId's based on a specified ShiftId. Whenever it hits a gap then it assumes we're done. I'm trying to create a cte with a ShiftSequence number that I can draw on. I'm able to create a temp table using an Identity field to generate the numeric sequence.

CREATE Table #Temp(ShiftScheduleId Int, ShiftSequence Int Identity(1,1), ShiftCode Char(1), ShiftNum SmallInt, ShiftStart SmallDateTime, ShiftEnd SmallDateTime)

INSERT INTO #Temp (ShiftScheduleId, ShiftCode, ShiftNum, ShiftStart, ShiftEnd)
SELECT ShiftScheduleId, ShiftCode, ShiftNum, ShiftStart, ShiftEnd
FROM [Shift.Schedule] WHERE ShiftNum > 0 ORDER BY ShiftScheduleId

SELECT * FROM #Temp


DROP Table #Temp

Answer 11

Ok, that's good news.  Below seems reasonably close to what you might have been looking for too, although you might need to add one additional CTE to sum up the count of days... what is below is a list of days.  (Incidentally, the data samples listed earlier, EVERYTHING was out of range because of @AlarmVal3=50, I had to change it to 100 for my example).

		;With STATLIST as
		(
		SELECT * 
		  , Row_Number() over(partition by shiftid order by shiftid) as ShifSeq
		  , Dense_Rank() over( order by shiftid) as ShiftNo
		  , Case When VAL1 > @AlarmVal1 Then 1 When Val2 > @AlarmVal2 Then 1 When Val3 > @AlarmVal3 Then 1 Else 0 End as OOR_Exists
		  FROM #TempTable
		)  
		, SUMMLIST as  /* Actually, I don't think this will be needed.  Leaving it in just for fun.*/
		(
		   Select ShiftID, Machine, Motor, ShiftNo, Count(OOR_Exists) as CtOOR
		     from STATLIST
		    Group by ShiftID, Machine, Motor, ShiftNo
		) 
		Select SL1.* 
		  From STATLIST SL1
		 Inner Join STATLIST SL2 on Abs(SL2.ShiftNO - SL1.ShiftNO) = 1
								and SL2.Motor = SL1.Motor
								and SL2.OOR_Exists > 0 
								and SL2.OOR_Exists = SL1.OOR_Exists
								and SL2.ShiftID <= @SHIFTID

Oh, unless I'm mistaken, the Dense_Rank call gave you a sequential shiftNo.

Answer 12

Ok, Thats looking good. I need to see if I can migrate this to my working database.

Answer 13

I got problems. I tried to apply this idea to my working database. I note that the cte STATLIST grabs all the records in the table. I have over 2.6 million records in my table so I basically went after all the records that were out of tolerance. Ok, that brought me down to about 78,000 records. This will grow over time so the performance won't get better. Next I'm finding duplicate records in my final query. I can also do this in the sample by setting Machine 2, Motor 2, Value 2 to 101 for shifts 1, 2, 4, 5, & 7. Be sure to set @ShiftId = 7 before trying it. Well I fixed that by doing a SELECT DISTINCT SL1.* FROM STATLIST...

Now for the next thing try setting  Machine 2, Motor 2, Value 2 to 101 for shifts 1, 2, 5, & 7. I get two sets of recurring out of tolerance values. I would like to only get the first set from shift 7 backwards which would be shifts 5 & 7. I think if I can get that done then I'll be home free although in my testing I am only dealing with one alarm value and haven't set anything for the total 3 values that's required. Guess I'll cross that bridge when I get to it.

Thanks!

Answer 14

I forgot to point that out:  If 3 and 4 have out of range records, but 4 and 5 do too, then the 4 items will show up twice. The Select Distinct took care of that, as you said.  But at the same time, get rid of the "*" from my example (bad form, ok for temp table examples only) and replace with specific column names. 

And the nature of CTE's is that the query won't really bring in all 2.6 million, it will only bring in those that match at the point the CTE's query is used, so only those records that match the join/where criteria will be read.

I don't think I understrand the two sets question, although I think our recreated test data has gotten different.  When I run it, things seem OK.  Repost your data generation, and your current query, just in case some difference came along?

EDIT: Regarding speed:  You might be a candidate for trying this out in a temp table for your real runs:  Either within the CTE or in a prior step, populate a table, doing the row_number/rank stuff up front, without and also with an index (try both ways).  Sometimes when the number of rows gets high, the numbers fabricated by the windowing functions (rank, etc.) don't always play nicely with speed.

Answer 15

As the final output I would really like to show 1 record for each machine/motor that has recurring (consecutive) out of tolerance values;

-- Sample output

ShiftId Machine Motor Val1 Val1Days Val2 Val2Days Val3 Val3Days
7           2            2     100       0       101     2         100     0

In this markup, Machine 2, Motor 2 has two sets of recurrences and that's because there is a skip in the ShiftNo. So in other words if we specify ShiftId 7, counting back from there we have two consecutive shifts that have recurring out of tolerance values for that Machine/Motor/Value combination. Shift 3 didn't have any out of tolerance values so we don't care what happened prior to that. I only care about consecutive out of tolerance values from the specified ShiftId backwards. Once we hit a shift that is clear from any out of tolerance values, then we're done. I still have to play with other Machine/Motor/Value combinations but I thought I should get the simple stuff cleared up first then move on to the complications.

CREATE Table #TempTable (ShiftId Int, Machine SmallInt, Motor SmallInt, Val1 SmallInt, Val2 SmallInt, Val3 SmallInt)

/******************************************************
    NOTE: ShiftId's are not necessarily sequential.
******************************************************/

-- Shift #1 / Machine #1
INSERT INTO #TempTable VALUES(1, 1,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(1, 1,	2, 100, 100, 100) -- Motor 2
INSERT INTO #TempTable VALUES(1, 1,	3, 100, 100, 100) -- Motor 3

-- Shift #1 / Machine #2
INSERT INTO #TempTable VALUES(1, 2,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(1, 2,	2, 100, 101, 100) -- Motor 2
INSERT INTO #TempTable VALUES(1, 2,	3, 100, 100, 100) -- Motor 3

-- Shift #1 / Machine #3
INSERT INTO #TempTable VALUES(1, 3,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(1, 3,	2, 100, 100, 100) -- Motor 2
INSERT INTO #TempTable VALUES(1, 3,	3, 100, 100, 100) -- Motor 3

/*-------------------------------------------*/

-- Shift #2 / Machine #1
INSERT INTO #TempTable VALUES(2, 1,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(2, 1,	2, 100, 100, 100) -- Motor 2
INSERT INTO #TempTable VALUES(2, 1,	3, 100, 100, 100) -- Motor 3

-- Shift #2 / Machine #2
INSERT INTO #TempTable VALUES(2, 2,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(2, 2,	2, 100, 101, 100) -- Motor 2
INSERT INTO #TempTable VALUES(2, 2,	3, 100, 100, 100) -- Motor 3

-- Shift #2 / Machine #3
INSERT INTO #TempTable VALUES(2, 3,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(2, 3,	2, 100, 100, 100) -- Motor 2
INSERT INTO #TempTable VALUES(2, 3,	3, 100, 100, 100) -- Motor 3

/*-------------------------------------------*/

-- Shift #4 / Machine #1
INSERT INTO #TempTable VALUES(4, 1,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(4, 1,	2, 100, 100, 100) -- Motor 2
INSERT INTO #TempTable VALUES(4, 1,	3, 100, 100, 100) -- Motor 3

-- Shift #4 / Machine #2
INSERT INTO #TempTable VALUES(4, 2,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(4, 2,	2, 100, 100, 100) -- Motor 2
INSERT INTO #TempTable VALUES(4, 2,	3, 100, 100, 100) -- Motor 3

-- Shift #4 / Machine #3
INSERT INTO #TempTable VALUES(4, 3,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(4, 3,	2, 100, 100, 100) -- Motor 2
INSERT INTO #TempTable VALUES(4, 3,	3, 100, 100, 100) -- Motor 3

/*-------------------------------------------*/

-- Shift #5 / Machine #1
INSERT INTO #TempTable VALUES(5, 1,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(5, 1,	2, 100, 100, 100) -- Motor 2
INSERT INTO #TempTable VALUES(5, 1,	3, 100, 100, 100) -- Motor 3

-- Shift #5 / Machine #2
INSERT INTO #TempTable VALUES(5, 2,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(5, 2,	2, 100, 101, 100) -- Motor 2
INSERT INTO #TempTable VALUES(5, 2,	3, 100, 100, 100) -- Motor 3

-- Shift #5 / Machine #3
INSERT INTO #TempTable VALUES(5, 3,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(5, 3,	2, 100, 100, 100) -- Motor 2
INSERT INTO #TempTable VALUES(5, 3,	3, 100, 100, 100) -- Motor 3

/*-------------------------------------------*/

-- Shift #7 / Machine #1
INSERT INTO #TempTable VALUES(7, 1,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(7, 1,	2, 100, 100, 100) -- Motor 2
INSERT INTO #TempTable VALUES(7, 1,	3, 100, 100, 100) -- Motor 3

-- Shift #7 / Machine #2
INSERT INTO #TempTable VALUES(7, 2,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(7, 2,	2, 100, 101, 100) -- Motor 2
INSERT INTO #TempTable VALUES(7, 2,	3, 100, 100, 100) -- Motor 3

-- Shift #7 / Machine #3
INSERT INTO #TempTable VALUES(7, 3,	1, 100, 100, 100) -- Motor 1
INSERT INTO #TempTable VALUES(7, 3,	2, 100, 100, 100) -- Motor 2
INSERT INTO #TempTable VALUES(7, 3,	3, 100, 100, 100) -- Motor 3

/*-------------------------------------------*/

DECLARE @ShiftId Int = 7
DECLARE @AlarmVal1 SmallInt = 125
DECLARE @AlarmVal2 SmallInt = 100
DECLARE @AlarmVal3 SmallInt = 100


;With STATLIST as
		(
		SELECT * 
		  , Row_Number() over(partition by shiftid order by shiftid) as ShifSeq
		  , Dense_Rank() over( order by shiftid) as ShiftNo
		  , Case When VAL1 > @AlarmVal1 Then 1 When Val2 > @AlarmVal2 Then 1 When Val3 > @AlarmVal3 Then 1 Else 0 End as OOR_Exists
		  FROM #TempTable
		)  
		, SUMMLIST as  /* Actually, I don't think this will be needed.  Leaving it in just for fun.*/
		(
		   Select ShiftID, Machine, Motor, ShiftNo, Count(OOR_Exists) as CtOOR
		     from STATLIST
		    Group by ShiftID, Machine, Motor, ShiftNo
		) 
		Select SL1.* 
		  From STATLIST SL1
		 Inner Join STATLIST SL2 on Abs(SL2.ShiftNO - SL1.ShiftNO) = 1
								and SL2.Motor = SL1.Motor
								and SL2.OOR_Exists > 0 
								and SL2.OOR_Exists = SL1.OOR_Exists
								and SL2.ShiftID <= @SHIFTID

go

DROP Table #TempTable

Answer 16

Try this... (FYI, this is one of the best things about CTEs... Readability.   In this set of examples, the CTEs make things way more readable than if we were doing this via nested queries, views (ok, that might stir up an argument), or other methods.)

Update #TEMPTABLE
  Set Val3 = 101  -- Recreate an "only one series of OOR values"
  where ShiftID in (1, 2) and Machine = 3 and motor = 3
  
;With STATLIST as
		(
		SELECT * 
		  , Row_Number() over(partition by shiftid order by shiftid) as ShifSeq
		  , Dense_Rank() over( order by shiftid) as ShiftNo
		  , Case When VAL1 > @AlarmVal1 Then 1 When Val2 > @AlarmVal2 Then 1 When Val3 > @AlarmVal3 Then 1 Else 0 End as OOR_Exists
		  FROM #TempTable
		)  
		, OUTPUTLIST as
		(
		Select SL1.*, Row_Number() over(partition by SL2.Machine, SL2.Motor order by SL2.ShiftNo  Desc  ) as MMSeqNo 
		  From STATLIST SL1
		 Inner Join STATLIST SL2 on Abs(SL2.ShiftNO - SL1.ShiftNO) = 1
								and SL2.Motor = SL1.Motor
								and SL2.OOR_Exists > 0 
								and SL2.OOR_Exists = SL1.OOR_Exists
								and SL2.ShiftID <= @SHIFTID
		)
		Select * from OUTPUTLIST -- TopTwoOnly -- 
		  where MMSEQNO <=2  

Answer 17

>>> where MMSEQNO <=2   <<<<

But how do I know there is two consecutive values.? There could be dozens. It just depends on what the query dredges up. That's why I would like to have a count of consecutive out of tolerance values for each Machine/Motor combination. The output could look like this; (Assuming all alarm values are set = 100)

-- Sample output

ShiftId Machine Motor Val1 Val1Days Val2 Val2Days Val3 Val3Days
7           2            2     100       0       101     2         100     0

or this;

ShiftId Machine Motor Val1 Val1Days Val2 Val2Days Val3 Val3Days
7           2            2     100       0       101     2         100     0

7           2            3     116      4       100     0         100     0

7           3          1     100       0       101     2         105    3

7           3           3     100       0       100     0         123     6

So to summarize the objective, I only want Distinct Machine/Motor records where any one or more values are out of tolerance from the specified ShiftId. There should be a count of consecutive occurances of the out of tolerance value(s) from the specified ShiftId. The Values, Val1, Val2, etc. are the values that occured on the specified ShiftID. I'm sorry to be such a pain but I'm pretty much at my limit of knowledge on how to accomplish this. I do appreciate you (JohnQ) sticking with me and helping out.

Answer 18

If there weren't two consecutive values, they wouldn't be part of the resultset to begin with, right? 

This is just forcing only the last two (or the only two when there are only two) to be included in the final output.

EDIT: FYI, I'm a tad confused by the requirement.  However, you're probably starting to get a feel for the ranking and windowing functions by all the examples of their use in these queries.  Here's an attempt at a count.

;With STATLIST as
		(
		SELECT * 
		  , Row_Number() over(partition by shiftid order by shiftid) as ShifSeq
		  , Dense_Rank() over( order by shiftid) as ShiftNo
		  , Case When VAL1 > @AlarmVal1 Then 1 When Val2 > @AlarmVal2 Then 1 When Val3 > @AlarmVal3 Then 1 Else 0 End as OOR_Exists
		 FROM #TempTable
		)  
		, OUTPUTLIST as
		(
		Select SL1.*
		   , Row_Number() over(partition by SL2.Machine, SL2.Motor order by SL2.ShiftNo  Desc  ) as MMSeqNo 
		  From STATLIST SL1
		 Inner Join STATLIST SL2 on Abs(SL2.ShiftNO - SL1.ShiftNO) = 1
								and SL2.Motor = SL1.Motor
								and SL2.OOR_Exists > 0 
								and SL2.OOR_Exists = SL1.OOR_Exists
								and SL2.ShiftID <= @SHIFTID
		)
		Select *
		   , Sum(OOR_Exists) over(partition by Machine, Motor) as CtOOR
		 FROM OUTPUTLIST -- TopTwoOnly -- 
		  where MMSEQNO <=2  

Answer 19

This works but it is a very simple example. I can specify an Id and it will return a count of the number of times a value recurs. This starts getting exponentially complicated when I introduce a machine number. and a motor number.

CREATE Table #TempTable (ValId Int, Val1 SmallInt, Val2 SmallInt, Val3 SmallInt)

INSERT INTO #TempTable VALUES(1,	1,	2,	3)
INSERT INTO #TempTable VALUES(2,	4,	2,	6)
INSERT INTO #TempTable VALUES(3,	1,	2,	3)
INSERT INTO #TempTable VALUES(4,	1,	2,	3)
INSERT INTO #TempTable VALUES(5,	1,	5,	6)
INSERT INTO #TempTable VALUES(6,	4,	2,	6)

DECLARE @VId Int = 4
DECLARE @V1 SmallInt = 1
DECLARE @V2 SmallInt = 2
DECLARE @V3 SmallInt = 3

;WITH cte AS (SELECT *, 
ROW_NUMBER() OVER (ORDER BY ValID) - ROW_NUMBER() OVER (PARTITION BY Val1 ORDER BY ValID) AS Grp1,
ROW_NUMBER() OVER (ORDER BY ValID) - ROW_NUMBER() OVER (PARTITION BY Val2 ORDER BY ValID) AS Grp2,  
ROW_NUMBER() OVER (ORDER BY ValID) - ROW_NUMBER() OVER (PARTITION BY Val3 ORDER BY ValId) AS Grp3
FROM #TempTable ),

cte4 AS (SELECT * FROM cte WHERE ValID = @VId)

SELECT * FROM (

	SELECT COUNT(*) AS [COUNT Val], 'CountVal1' AS [TYPE]
	FROM cte INNER JOIN cte4 ON cte.Val1 = cte4.Val1 AND cte.Grp1 = cte4.Grp1  and cte.Val1 = @V1 

	UNION ALL

	SELECT COUNT(*) AS [COUNT Val], 'CountVal2' AS [TYPE]
	FROM cte INNER JOIN cte4 ON cte.Val2 = cte4.Val2 AND cte.Grp2 = cte4.Grp2  and cte.Val2 = @V2 

	UNION ALL

	SELECT COUNT(*) AS [COUNT Val], 'CountVal3' AS [TYPE]
	FROM cte INNER JOIN cte4 ON cte.Val3 = cte4.Val3 AND cte.Grp3 = cte4.Grp3  and cte.Val3 = @V3  ) Src

 PIVOT (MAX([COUNT Val]) FOR [TYPE] IN ([CountVal1],[CountVal2],[CountVal3])) pvt 


DROP Table #TempTable

I would like the output to look like...

Mach / Motor / Val1Occurences / Val2Occurences / Val3Occurences

I only want one Mach / Motor combination and that is based on the shift that I selected.

If any one or more of the values is out of tolerance then the record gets returned.

Answer 20

Ok, I'm really really close but I need one final bit of help. I am able to get multiple groups of consecutive recurrences of out of tolerance data. The last thing I need to do is use a @ShiftSchedule Parameter to select a count of out of tolerance values for anything that is in sequence based on the relationship of the ShiftScheduleId (SSId) to ShiftSeq. In other words, if the parameter @ShiftSchedule = 642 then I would expect to count the values where ShiftSeq is between 127 AND 135. Note that ShiftSeq has a break at 135 then jumps to 137. I don't care about a break in sequence (ShiftSeq) either before or after the specified ShiftId.

Mach	Motor	Val1	Val2	Val3	SSID	ShiftSeq
1	114	57.7	57.7	1	641	127
1	114	40.2	40.2	0	642	128
1	114	65.8	65.8	0	644	129
1	114	64.8	64.8	2	645	130
1	114	26.7	26.7	0	647	131
1	114	46.2	46.2	0	648	132
1	114	27.8	27.8	0	650	133
1	114	16.6	16.6	1	651	134
1	114	74.5	74.5	4	653	135
1	114	38.4	38.4	0	656	137
1	114	0.8	0.8	0	657	138
1	114	1.0	1.0	11	659	139
1	114	0.6	0.6	0	660	140
1	114	62.5	62.5	13	662	141
1	121	57.7	57.7	1	641	127
1	121	40.2	40.2	0	642	128
1	121	65.8	65.8	0	644	129
1	121	64.8	64.8	2	645	130
1	121	26.7	26.7	0	647	131
1	121	46.2	46.2	0	648	132
1	121	27.8	27.8	0	650	133
1	121	16.6	16.6	1	651	134
1	121	74.5	74.5	4	653	135
1	121	38.4	38.4	0	656	137
1	121	0.8	0.8	0	657	138
1	121	1.0	1.0	11	659	139
1	121	0.6	0.6	0	660	140
1	121	62.5	62.5	13	662	141
3	117	57.7	57.7	1	641	127
3	117	40.2	40.2	0	642	128
3	117	65.8	65.8	0	644	129
3	117	64.8	64.8	2	645	130
3	117	26.7	26.7	0	647	131
3	117	46.2	46.2	0	648	132
3	117	27.8	27.8	0	650	133
3	117	16.6	16.6	1	651	134
3	117	74.5	74.5	4	653	135
3	117	38.4	38.4	0	656	137
3	117	0.8	0.8	0	657	138
3	117	1.0	1.0	11	659	139
3	117	0.6	0.6	0	660	140
3	117	62.5	62.5	13	662	141
3	129	57.7	57.7	1	641	127
3	129	40.2	40.2	0	642	128
3	129	65.8	65.8	0	644	129
3	129	64.8	64.8	2	645	130
3	129	26.7	26.7	0	647	131
3	129	46.2	46.2	0	648	132
3	129	27.8	27.8	0	650	133
3	129	16.6	16.6	1	651	134
3	129	74.5	74.5	4	653	135
3	129	38.4	38.4	0	656	137
3	129	0.8	0.8	0	657	138
3	129	1.0	1.0	11	659	139
3	129	0.6	0.6	0	660	140
3	129	62.5	62.5	13	662	141

In the above example, using the following Alarm Points;

Val1 < 15; Val2 < 75; Val3 > 10

If I passed in a  @ShiftSchedule = 659 I would expect the output to look like this (I left the count the same across all mach/motors to simplify things)

Mach	Motor	V1Cnt	V2Cnt	V3Cnt	
1	114	3	5	2	
1	121	3	5	2
3	117	3	5	2	
3	129	3	5	2

Here is the markup to generate a table with this data

CREATE Table #TempTable(Mach Int, Motor Int, Val1 Float, Val2 Float, Val3 Float, SSid Int, ShiftSeq Int)

INSERT INTO #TempTable VALUES(1, 114, 57.7, 57.7, 1, 641, 127)
INSERT INTO #TempTable VALUES(1, 114, 40.2, 40.2, 0, 642, 128)
INSERT INTO #TempTable VALUES(1, 114, 65.8,	65.8, 0, 644, 129)
INSERT INTO #TempTable VALUES(1, 114, 64.8,	64.8, 2, 645, 130)
INSERT INTO #TempTable VALUES(1, 114, 26.7,	26.7, 0, 647, 131)
INSERT INTO #TempTable VALUES(1, 114, 46.2,	46.2, 0, 648, 132)
INSERT INTO #TempTable VALUES(1, 114, 27.8,	27.8, 0, 650, 133)
INSERT INTO #TempTable VALUES(1, 114, 16.6,	16.6, 1, 651, 134)
INSERT INTO #TempTable VALUES(1, 114, 74.5,	74.5, 4, 653, 135)
INSERT INTO #TempTable VALUES(1, 114, 38.4,	38.4, 0, 656, 137)
INSERT INTO #TempTable VALUES(1, 114, 0.8, 0.8,	0, 657,	138)
INSERT INTO #TempTable VALUES(1, 114, 1.0, 1.0,	11, 659,	139)
INSERT INTO #TempTable VALUES(1, 114, 0.6, 0.6,	0, 660,	140)
INSERT INTO #TempTable VALUES(1, 114, 62.5, 62.5, 13, 662, 141)

INSERT INTO #TempTable VALUES(1, 121, 57.7, 57.7, 1, 641, 127)
INSERT INTO #TempTable VALUES(1, 121, 40.2, 40.2, 0, 642, 128)
INSERT INTO #TempTable VALUES(1, 121, 65.8,	65.8, 0, 644, 129)
INSERT INTO #TempTable VALUES(1, 121, 64.8,	64.8, 2, 645, 130)
INSERT INTO #TempTable VALUES(1, 121, 26.7,	26.7, 0, 647, 131)
INSERT INTO #TempTable VALUES(1, 121, 46.2,	46.2, 0, 648, 132)
INSERT INTO #TempTable VALUES(1, 121, 27.8,	27.8, 0, 650, 133)
INSERT INTO #TempTable VALUES(1, 121, 16.6,	16.6, 1, 651, 134)
INSERT INTO #TempTable VALUES(1, 121, 74.5,	74.5, 4, 653, 135)
INSERT INTO #TempTable VALUES(1, 121, 38.4,	38.4, 0, 656, 137)
INSERT INTO #TempTable VALUES(1, 121, 0.8, 0.8,	0, 657,	138)
INSERT INTO #TempTable VALUES(1, 121, 1.0, 1.0,	11, 659,	139)
INSERT INTO #TempTable VALUES(1, 121, 0.6, 0.6,	0, 660,	140)
INSERT INTO #TempTable VALUES(1, 121, 62.5, 62.5, 13, 662, 141)

INSERT INTO #TempTable VALUES(3, 117, 57.7, 57.7, 1, 641, 127)
INSERT INTO #TempTable VALUES(3, 117, 40.2, 40.2, 0, 642, 128)
INSERT INTO #TempTable VALUES(3, 117, 65.8,	65.8, 0, 644, 129)
INSERT INTO #TempTable VALUES(3, 117, 64.8,	64.8, 2, 645, 130)
INSERT INTO #TempTable VALUES(3, 117, 26.7,	26.7, 0, 647, 131)
INSERT INTO #TempTable VALUES(3, 117, 46.2,	46.2, 0, 648, 132)
INSERT INTO #TempTable VALUES(3, 117, 27.8,	27.8, 0, 650, 133)
INSERT INTO #TempTable VALUES(3, 117, 16.6,	16.6, 1, 651, 134)
INSERT INTO #TempTable VALUES(3, 117, 74.5,	74.5, 4, 653, 135)
INSERT INTO #TempTable VALUES(3, 117, 38.4,	38.4, 0, 656, 137)
INSERT INTO #TempTable VALUES(3, 117, 0.8, 0.8,	0, 657,	138)
INSERT INTO #TempTable VALUES(3, 117, 1.0, 1.0,	11, 659,	139)
INSERT INTO #TempTable VALUES(3, 117, 0.6, 0.6,	0, 660,	140)
INSERT INTO #TempTable VALUES(3, 117, 62.5, 62.5, 13, 662, 141)

INSERT INTO #TempTable VALUES(3, 129, 57.7, 57.7, 1, 641, 127)
INSERT INTO #TempTable VALUES(3, 129, 40.2, 40.2, 0, 642, 128)
INSERT INTO #TempTable VALUES(3, 129, 65.8,	65.8, 0, 644, 129)
INSERT INTO #TempTable VALUES(3, 129, 64.8,	64.8, 2, 645, 130)
INSERT INTO #TempTable VALUES(3, 129, 26.7,	26.7, 0, 647, 131)
INSERT INTO #TempTable VALUES(3, 129, 46.2,	46.2, 0, 648, 132)
INSERT INTO #TempTable VALUES(3, 129, 27.8,	27.8, 0, 650, 133)
INSERT INTO #TempTable VALUES(3, 129, 16.6,	16.6, 1, 651, 134)
INSERT INTO #TempTable VALUES(3, 129, 74.5,	74.5, 4, 653, 135)
INSERT INTO #TempTable VALUES(3, 129, 38.4,	38.4, 0, 656, 137)
INSERT INTO #TempTable VALUES(3, 129, 0.8, 0.8,	0, 657,	138)
INSERT INTO #TempTable VALUES(3, 129, 1.0, 1.0,	11, 659,	139)
INSERT INTO #TempTable VALUES(3, 129, 0.6, 0.6,	0, 660,	140)
INSERT INTO #TempTable VALUES(3, 129, 62.5, 62.5, 13, 662, 141)

DECLARE @V1 Int = 15
DECLARE @V2 Int = 75
DECLARE @V3 Int = 10
DECLARE @ShiftSchedule Int = 659

SELECT * FROM #TempTable


go

DROP Table #TempTable

Answer 21

Getting even closer now. This gets the data in the format that I want. Now all I got to do is figure out the Min and Max ShiftSeq based on the incoming ShiftSchedule.

;WITH cte AS (SELECT * FROM #TempTable WHERE ShiftSeq Between 137 AND 141)

SELECT Mach, Motor, 
[Val1Count] = (SELECT COUNT(Val1) FROM cte WHERE cte.Val1 < @v1 AND tt.Mach = cte.Mach AND tt.Motor = cte.Motor),
[Val2Count] = (SELECT COUNT(Val2) FROM cte WHERE cte.Val2 < @v2 AND tt.Mach = cte.Mach AND tt.Motor = cte.Motor),
[Val3Count] = (SELECT COUNT(Val3) FROM cte WHERE cte.Val3 > @v3 AND tt.Mach = cte.Mach AND tt.Motor = cte.Motor)

FROM #TempTable tt
WHERE ShiftSeq BETWEEN 137 AND 141
GROUP BY Mach, Motor
ORDER BY Mach, Motor

Answer 22

What version of SQL Server?  (FYI, 2012 has some cool new features, including LAG() for looking up a previous value in a rowset). 

Below is a stab at something... I'm not sure if it would help or hurt in what you're looking for:

;WITH cte AS (SELECT * FROM #TempTable WHERE ShiftSeq Between 137 AND 141)

SELECT Mach, Motor, max(MaxSS), Max(MinSS),
[Val1Count] = (SELECT COUNT(Val1) FROM cte WHERE cte.Val1 < @v1 AND tt.Mach = cte.Mach AND tt.Motor = cte.Motor),
[Val2Count] = (SELECT COUNT(Val2) FROM cte WHERE cte.Val2 < @v2 AND tt.Mach = cte.Mach AND tt.Motor = cte.Motor),
[Val3Count] = (SELECT COUNT(Val3) FROM cte WHERE cte.Val3 > @v3 AND tt.Mach = cte.Mach AND tt.Motor = cte.Motor)

FROM #TempTable tt
Inner Join (Select Mach as MMMach, Motor as MMMOtor, Max(ShiftSeq) as MaxSS, Min(ShiftSeq) as MinSS from #TempTable Group by Mach, Motor) as MaxMin on MMMach = Mach and MMMotor = Motor
WHERE ShiftSeq BETWEEN 137 AND 141
GROUP BY Mach, Motor
ORDER BY Mach, Motor

Answer 23

Johnq, I appreciate the reply but what I was asking is how to determine the Min(ShiftSeq) AND the Max(ShiftSeq) based on @SSId parameter. Once I have the Min and Max ShiftSeq I can then run this final query and show the recuring number of days for any Mach/Motor combination that has one or more out of tolerance values based on the incoming SSId and for the number of consecutive ShiftSeq values. Here is a simple example;

SSId ShiftSeq
222 1
223 2
225 3
231 4
257 9
259 10
260 11
272 19
275 21
276 22

So If I chose SSId = 225 I would expect that the Min(ShiftSeq) = 1 and the Max(ShiftSeq) = 4 because the corresponding ShiftSeq number next to SSId 225 is 3. So I need to look forwards and backwards in the ShiftSeq column from the value 3 to find where the sequence starts and where the sequence ends.

If I chose SSId = 222 or 223 or 231 the result would be the same as if chosing 225. I hope this makes sense. Oh, I am using SQL Server 2008 R2 and I don't have any control over that as it is what my customer currently has installed.

CREATE Table #Temp2(SSId Int, ShiftSeq Int)

INSERT INTO #Temp2 VALUES(222, 1)
INSERT INTO #Temp2 VALUES(223, 2)
INSERT INTO #Temp2 VALUES(225, 3)
INSERT INTO #Temp2 VALUES(231, 4)
INSERT INTO #Temp2 VALUES(257, 9)
INSERT INTO #Temp2 VALUES(259, 10)
INSERT INTO #Temp2 VALUES(260, 11)
INSERT INTO #Temp2 VALUES(272, 19)
INSERT INTO #Temp2 VALUES(275, 21)
INSERT INTO #Temp2 VALUES(276, 22)

DECLARE @SSID Int = 225

SELECT MIN(ShifSeq), MAX(ShiftSeq)
FROM #Temp2
WHERE -- This is where I get lost

DROP Table #Temp2

Answer 24

Here's an approach to finding gaps in a sequence.  I doubt it's the only way to do it (I seem to recall seeing a thread or two in the past on this topic too).  I think the approach listed here is relatively sound, though.  Another nested CTE in the chain oughta do the trick.  Your initial analysis way at the start of the thread was true, though.. this has been far more complex than meets the eye.  It's almost on the cusp of "do in a programming language instead of SQL", but after all this, I think you've just about gotten it!

		Declare @MyList Table (MyID int identity, MyNum int)
		Insert @MyList Select 1 UNION ALL Select 2 UNION ALL Select 3 UNION ALL Select 4
		                        UNION ALL Select 6 UNION ALL Select 8 UNION ALL Select 9
		                        UNION ALL Select 12 UNION ALL Select 13 UNION ALL Select 20  /* A list with some missing values */
		Declare @SeqList Table (SeqNo int primary key)
		Insert @SeqList Select top 100 Row_Number() over(Order by @@ServerName) from SYS.MESSAGES  /* A list of all values in a sequence */

		;With GapList as /* A list of all values in the sequence, identifying where @MYLIST had values */
		(
		Select NULL as MyID, SeqNo as MyNum, 0 as HasMatch  from @SeqList where SeqNo <= (Select Max(MyNum) from @MyList)
		EXCEPT
		Select NULL, MyNum, 0 from @MyList
		UNION
		Select MyID, MyNum, 1
		 from @MyList
		)  
		, FullList as /* An exploded list (contains excess records, has to be processed/grouped later */
		(
		 Select *
		   from GapList GL
		   LEFT JOIN (Select Mynum as NextGap from GapList GLN where HasMatch = 0) NextGapJ
		              on NextGapJ.NextGap > GL.MyNum and NextGapJ.NextGap > GL.MyNum
		   LEFT JOIN (Select Mynum as PrevGap from GapList GLN where HasMatch = 0) PrevGapJ 
		              on PrevGapJ.PrevGap < GL.MyNum and PrevGapJ.PrevGap < GL.MyNum
    	)  
         Select MyNum, Min(NextGap) as NextGap, Max(PrevGap) as PrevGap 
           From FullList 
          Group by MyNum 

Answer 25

Ok, I think I have a very simple and eloquent way to do this. Just specify the desired SSid and this will give the Min and Max ShiftSeq values;

CREATE Table #Temp2(SSId Int, ShiftSeq Int)

INSERT INTO #Temp2 VALUES(222, 1)
INSERT INTO #Temp2 VALUES(223, 2)
INSERT INTO #Temp2 VALUES(225, 3)
INSERT INTO #Temp2 VALUES(231, 4)
INSERT INTO #Temp2 VALUES(257, 9)
INSERT INTO #Temp2 VALUES(259, 10)
INSERT INTO #Temp2 VALUES(260, 11)
INSERT INTO #Temp2 VALUES(272, 19)
INSERT INTO #Temp2 VALUES(275, 21)
INSERT INTO #Temp2 VALUES(276, 22)

DECLARE @SSID Int = 225

;With cte1 AS (SELECT SSId, ShiftSeq, RowNum = ROW_NUMBER() OVER (ORDER BY ShiftSeq DESC) FROM #Temp2),

cte2 AS (SELECT SSid, ShiftSeq, GROUPING = ShiftSeq + RowNum FROM cte1)

SELECT MIN(ShiftSeq) AS MinShiftSeq, MAX(ShiftSeq) AS MaxShiftSeq
FROM cte2
WHERE GROUPING = (SELECT GROUPING FROM cte2 WHERE SSId = @SSID)

DROP Table #Temp2

Yes, I agree with you Johnq, this just doesn't look that complicated but as you start to peel off the layers it gets complex real fast. I considered using the application for some of the data extraction but I know from past experience looping through thousands of records isn't exactly an efficient thing and especially considering that the results of this query ends up on a web page. I don't want the end users looking at an hourglass if it can be avoided. I felt like there was a way to do this fairly efficiently through TSQL but there is no question that I hit the limit of my skill level trying to figure this one out. I need to apply this latest discovery to what I have now and hopefully (fingers crossed) it will generate the desired results. I'll post back my success or failure. Thanks for your help!

Answer 26

Very cool... I'd more label that final grouping method as "Subtly complex and Tricky" rather than "Simple and Eloquent", but in the end it looks like it works! I'll have to keep that trick in mind. :-)

This is definitely something to document thoroughly... It will be just as tricky for the next person who inherits the code as it was to get it written in the first place.

Answer 27

DECLARE	@AlarmVal1 SMALLINT = 125,
	@AlarmVal2 SMALLINT = 100,
	@AlarmVal3 SMALLINT = 100,
	@ShiftID INT = 4

/*
	Solution by SwePeso
*/
DECLARE	@ConsecutiveCount INT = 1	-- Try change to 2

-- Create staging table
CREATE TABLE	#Stage
		(
			ShiftID INT,
			ShiftSeq INT,
			Machine INT,
			Motor INT,
			Val1 INT,
			Seq1 INT,
			Val2 INT,
			Seq2 INT,
			Val3 INT,
			Seq3 INT
		)

-- Create unique clustered index on staging table
CREATE UNIQUE CLUSTERED INDEX UCX_Stage ON #Stage (ShiftID, Machine, Motor)

-- Populate staging table with rows having alarming value
;WITH cteSource(ShiftID, ShiftSeq, Machine, Motor, Val1, Flag1, Val2, Flag2, Val3, Flag3)
AS (
	SELECT	ShiftID,
		DENSE_RANK() OVER (ORDER BY ShiftID) AS ShiftSeq,
		Machine,
		Motor,
		Val1,
		CASE
			WHEN Val1 > @AlarmVal1 THEN 1
			ELSE 0
		END AS Flag1,
		Val2,
		CASE
			WHEN Val2 > @AlarmVal2 THEN 1
			ELSE 0
		END AS Flag2,
		Val3,
		CASE
			WHEN Val3 > @AlarmVal3 THEN 1
			ELSE 0
		END AS Flag3
	FROM	#TempTable
)
INSERT	#Stage
	(
		ShiftID,
		ShiftSeq,
		Machine,
		Motor,
		Val1,
		Seq1,
		Val2,
		Seq2,
		Val3,
		Seq3
	)
SELECT	ShiftID,
	ShiftSeq,
	Machine,
	Motor,
	Val1,
	NULLIF(Flag1 * ShiftSeq - Flag1 * ROW_NUMBER() OVER (PARTITION BY Machine, Motor, Flag1 ORDER BY ShiftID), 0) AS Seq1,
	Val2,
	NULLIF(Flag2 * ShiftSeq - Flag2 * ROW_NUMBER() OVER (PARTITION BY Machine, Motor, Flag2 ORDER BY ShiftID), 0) AS Seq2,
	Val3,
	NULLIF(Flag3 * ShiftSeq - Flag3 * ROW_NUMBER() OVER (PARTITION BY Machine, Motor, Flag3 ORDER BY ShiftID), 0) AS Seq3
FROM	cteSource
WHERE	1 IN (Flag1, Flag2, Flag3)
	AND ShiftID <= @ShiftID

-- Display the final resultset
;WITH cteSource(ShiftID, Machine, Motor, Val1, Seq1, Cnt1, Val2, Seq2, Cnt2, Val3, Seq3, Cnt3)
AS (
	SELECT	ShiftID,
		Machine,
		Motor,
		Val1,
		Seq1,
		ShiftSeq - Seq1 AS Cnt1,
		Val2,
		Seq2,
		ShiftSeq - Seq2 AS Cnt2,
		Val3,
		Seq3,
		ShiftSeq - Seq3 AS Cnt3
	FROM	#Stage
)
SELECT	ShiftID,
	Machine,
	Motor,
	Val1,
	ISNULL(Cnt1, 0) AS Val1Days,
	Val2,
	ISNULL(Cnt2, 0) AS Val2Days,
	Val3,
	ISNULL(Cnt3, 0) AS Val3Days

FROM	(
		SELECT	ShiftID,
			Machine,
			Motor,
			Val1,
			Cnt1,
			Val2,
			Cnt2,
			Val3,
			Cnt3,
			ROW_NUMBER() OVER (PARTITION BY Machine, Motor, Seq1 ORDER BY Cnt1 DESC) AS a,
			ROW_NUMBER() OVER (PARTITION BY Machine, Motor, Seq2 ORDER BY Cnt2 DESC) AS b,
			ROW_NUMBER() OVER (PARTITION BY Machine, Motor, Seq3 ORDER BY Cnt3 DESC) AS c
		FROM	cteSource
	) AS d
WHERE	Cnt1 >= @ConsecutiveCount AND a = 1
	OR Cnt2 >= @ConsecutiveCount AND b = 1
	OR Cnt3 >= @ConsecutiveCount AND c = 1

-- Clean up
DROP TABLE	#Stage

Have fun!

---

N 56°04'39.26"
E 12°55'05.63"

Answer 28

Any progress?

Actually I've hit a roadblock. I thought I had this thing working but it's not functioning the way I intentioned. I had to lay off of it for a coouple of days to attend to some other tasks. I hope to tinker with it some more in the next day and I'll report back.