Sub Query Find Next Row Where Statement

[JayRab]

Looking for some help with some SQL sub query writing.



I have a table that has at least 13 machines each with multiple events per day/ shift.


I am essentially looking for the entry before and after each event. For example Tsugami 9 has a shift change on index 17143 1/2019/2019 3:30:01 pm I want to find the Previous and Next Index these would be 17142 and 17469.

I can get this easily if they were in sequential order

PrevEntryIdx: (SELECT Index From [tblTest1] as ALias WHERE Alias.Index = ([tblTest1].Index -1) and Alias.Description = [tblTest1].[description] )

but that is not the case and I get blanks when the previous Index is a different machine.


I believe I need to replace the where clause in my statement with a max() or top 1 sub query but I am not sure how to do it.

SELECT Index from [tblTest1] as Alias WHERE Index = ("INSERT SUBQUERY HERE WITH TOP/MAX where Description matchs and Alias.Index < Index")

Any thoughts or assistance is appreciated. I will post some of what I tried later.

Thanks
JR

[Micron]

Instead of retrieving the prior or following index, why don't you sort by machine then by date and get the prior and following dates?
To clarify, I don't mean sort the table. I mean either in this query, or what might be required, is to sort in a query then use that query as the basis of this query instead of a table.

[JayRab]

Well I did get a working sub query but it is painfully slow


Here is the code

PrevEnd: (SELECT Index FROM [tblTest1] as Alias WHERE Index = (SELECT Max(Index) FROM [tblTest1] as Alias2 WHERE Alias2.Index < [tblTest1].[Index] and Alias2.Description = [tblTest1].[description]) AND Alias.Description = [tblTest1].[description])

I will try your suggestion Micron and see if it is any more painless.

Ultimately I plan on using this to create running time charts and Machine On Machine Off times. I will need to look at the entries after shift change and if that entry is Machine Down the Machine was run through shift change so Machine on = Shift Change EventTime and if Machine Up was after shift change then the Machine Up event time is Machine On Times. I will need to do the same looking at the previous entries for Machine Off time etc...

I will let you know if I find a faster better solution to the above.

Thanks

[orange]

It might be more efficient if you could post a copy of the database. You could anonymize the data if it is sensitive.
If necessary you can use names like Daffy Duck, Porky Pig, Polly Dacktel, Cetyl Alquihaul etc. Or you can get test data names from internet. I have used (http://www.databasetestdata.com/#)

[accesstos]

These queries is pretty close to yours and I'm afraid just as slow. Maybe you have to apply the queries on a subset of your records (e.g. only for a specific day) to reduce the execute time.

Query1. (To be used in Query2)

SELECT Alias1.Index, Alias1.[Description], 
(SELECT TOP 1 Alias2.Index  
FROM tblTest1 AS Alias2  
WHERE Alias2.Index<Alias1.Index  
ORDER BY  Alias2.Index DESC) AS PreviousIndex, 
(SELECT TOP 1 Alias2.Index  
FROM tblTest1 AS Alias2  
WHERE Alias2.Index>Alias1.Index  
ORDER BY  Alias2.Index) AS NextIndex
FROM tblTest1 AS Alias1, tblTest1 AS Alias2
WHERE (Alias1.Index=Alias2.Index);

Query2. (Results view)

SELECT DISTINCT Query1.[Desc], 
Query1.IndexID AS CurrentIndex, 
PreIDs.IndexID AS PerviousIndex, 
NextIDs.IndexID AS NextIndex
FROM (tblIndex AS PreIDs INNER JOIN tblIndex AS NextIDs 
ON PreIDs.[Desc]=NextIDs.[Desc]) INNER JOIN Query1 
ON (PreIDs.IndexID=Query1.PreviousIndex) 
AND (NextIDs.IndexID=Query1.NextIndex);

I hope it helps.
John

[ArviLaanemets]

I assume Index is an autonumeric or at least Long Integer field.

1. You must have textboxes in form where the Index and description values are stored (a textbox with current Index as source, or it is calculated based on description and log event time in some other controls). E.g. txtIndexSearched and txtDescriptionSearched;
2. You need an event in form, which triggers the calculation of previous and next Index values;
3. The event creates a Query Strings like:

..
Qstr1 = "SELECT TOP 1 Index FROM tblIndex WHERE Index < " & txtIndexSearched & " AND description = '" & txtDescriptionSearched & "' ORDER BY Index ASC"
Qstr2 = "SELECT TOP 1 Index FROM tblIndex WHERE Index > " & txtIndexSearched & " AND description = '" & txtDescriptionSearched & "' ORDER BY Index DESC"

and runs them. The 1st one returns previous index, the 2nd one next index. You must have 2 additional unbound controls in form, and the event scripts sets their values equal with respective returned values.

This is for case when you want to previous and next index for certain base index. When you want the same to store previous and next index values for every row in table, then it will be much more complicated, especially when you want to calculate previous and next indexes every time some entry in table is changed.

You also can try a parameter query, which uses IndexSearched parameter, something like (this is on fly, so I'm not sure about syntax)

qPrevAndCurrAndNext =
SELECT prev.Index AS PrevIndex, curr.Index AS CurrIndex, next.Index AS NextIndex
FROM tblIndex curr, 
(SELECT TOP 1 x1.* FROM tblIndex x1 WHERE x1.description = curr.description AND x1.Index < curr.Index ORDER NY x1.Index ASC) prev,
(SELECT TOP 1 x2.* FROM tblIndex x1 WHERE x1.description = curr.description AND x1.Index > curr.Index ORDER NY x1.Index ASC) next
WHERE curr.Index = IndexSearched

[CJ_London]

Well I did get a working sub query but it is painfully slow

not sure if this has been covered but all fields used in the query criteria or for sorting should be indexed. Also be aware that Index is a reserved word. Doubt it will affect performance, but can produce misleading errors.

You mention that you are limiting to 'shift change' records, so ensure your tblIndex has a criteria to limit to just those records

also, you can eliminate one subquery by using TOP 3 and just have the subquery for the previous record - or TOP 2 and exclude the middle record if you don't need to see that.

actually - last suggestion doesn't work

however you can limit the dataset further by using criteria to limit the number of machines and/or date range

[JayRab]

not sure if this has been covered but all fields used in the query criteria or for sorting should be indexed. Also be aware that Index is a reserved word. Doubt it will affect performance, but can produce misleading errors.

You mention that you are limiting to 'shift change' records, so ensure your tblIndex has a criteria to limit to just those records

also, you can eliminate one subquery by using TOP 3 and just have the subquery for the previous record - or TOP 2 and exclude the middle record if you don't need to see that.

actually - last suggestion doesn't work

however you can limit the dataset further by using criteria to limit the number of machines and/or date range

Forgot to index those fields I have indexed them and it definitely has sped things up also change from generic index to idxevent. I will continue to work on it.

I will edit this post later and add a smaller version of the dataset and the query which is working. I will also attached an excel document that I am trying to eliminate the need of to allow access to query and generate the reports.

I will also try some of the other suggestions as well. Thanks for all the help guys.

[JayRab]

I assume Index is an autonumeric or at least Long Integer field.

1. You must have textboxes in form where the Index and description values are stored (a textbox with current Index as source, or it is calculated based on description and log event time in some other controls). E.g. txtIndexSearched and txtDescriptionSearched;
2. You need an event in form, which triggers the calculation of previous and next Index values;
3. The event creates a Query Strings like:

..
Qstr1 = "SELECT TOP 1 Index FROM tblIndex WHERE Index < " & txtIndexSearched & " AND description = '" & txtDescriptionSearched & "' ORDER BY Index ASC"
Qstr2 = "SELECT TOP 1 Index FROM tblIndex WHERE Index > " & txtIndexSearched & " AND description = '" & txtDescriptionSearched & "' ORDER BY Index DESC"

and runs them. The 1st one returns previous index, the 2nd one next index. You must have 2 additional unbound controls in form, and the event scripts sets their values equal with respective returned values.

This is for case when you want to previous and next index for certain base index. When you want the same to store previous and next index values for every row in table, then it will be much more complicated, especially when you want to calculate previous and next indexes every time some entry in table is changed.

You also can try a parameter query, which uses IndexSearched parameter, something like (this is on fly, so I'm not sure about syntax)

qPrevAndCurrAndNext =
SELECT prev.Index AS PrevIndex, curr.Index AS CurrIndex, next.Index AS NextIndex
FROM tblIndex curr, 
(SELECT TOP 1 x1.* FROM tblIndex x1 WHERE x1.description = curr.description AND x1.Index < curr.Index ORDER NY x1.Index ASC) prev,
(SELECT TOP 1 x2.* FROM tblIndex x1 WHERE x1.description = curr.description AND x1.Index > curr.Index ORDER NY x1.Index ASC) next
WHERE curr.Index = IndexSearched

index is autogenerated as more of the data is appended to the table from a datasource that only stores 1000 entries per machine. I run an append query everytime the database is opened to grab the newest data this allows for a larger dataset.

[JayRab]

see attached for database with table and query

in the end I want to replace an excel spread sheet that I pull the data from and put everything in access. The spreadsheet is attached.
The Data page looks like below

Eventually I am looking to get First Piece Last Piece data MTTR and MTTF data as an average for data and be able to do trend analysis on it as we implement new start up and shut procedures.

But I have to clear out a lot of the dirty data see the averageifs formulas used on the report information tab.

Thanks for the help/ideas.

[CJ_London]

I 'll take a look later, but please note Description is another reserved word

[JayRab]

I 'll take a look later, but please note Description is another reserved word

i remember index being one did not realize description is thanks for the heads up

[CJ_London]

your query doesn't mention Shift Change, instead it has 'Not In ("Log Out","Log In","New Job")'. So I am confused about what you really require. Also you are applying a date criteria to shortdate, which is derived from log_event_time - would be more efficient to apply the criteria to that field instead. Further your criteria is in UK format, for sql you need to use the US format of mm/dd/yyyy. Sqll is clever enough to know there are not 29 months, so corrects it. But if the day is less than 12 it will be interpreted as US (i.e. a day of 1/6/2019 will be interpreted as 6th Jan, not 1st June). Suggest you need to research and understand the date datatype, and then understand the difference between the format function and the format property.

Based on your original requirement I found this was the fastest - took 40 seconds on my machine for the whole dataset.

SELECT *
FROM (SELECT  A.idx, A.description, A.log_event_time, A.EventName
FROM tblTest1 AS A
WHERE (SELECT TOP 1 eventname FROM tblTest1 WHERE [description]=a.[description] AND log_event_time>a.log_event_time ORDER BY log_event_time, eventname desc)='Shift Change'
UNION ALL SELECT  A.idx, A.description, A.log_event_time, A.EventName
FROM tblTest1 AS A
WHERE (SELECT TOP 1 eventname FROM tblTest1 WHERE [description]=a.[description] AND log_event_time<a.log_event_time ORDER BY log_event_time desc, eventname desc)='Shift Change'
UNION ALL SELECT  A.idx, A.description, A.log_event_time, A.EventName
FROM tblTest1 AS A WHERE eventname='Shift Change')  AS B
ORDER BY B.description, B.log_event_time;

You have other fields which may be a better choice - numerical equivalents log-idx seems consistent with eventdate, although you have some data errors around 2/11/2018 and 29/01/2019 (perhaps ignore those without a log event name?, you know your data) and using log_event_type rather than eventname. Numbers take up less space than text so indexes are smaller and therefore faster. If you could work off log_idx by tidying up the data you would have a much faster query - this for example to 6 seconds

SELECT A.idx, B.log_idx, B.description, B.log_event_time, B.EventName
FROM tblTest1 AS A INNER JOIN tblTest1 AS B ON A.description = B.description
WHERE (((B.log_idx) Between [a].[log_idx]-1 And [a].[log_idx]+1) AND ((A.log_event_type)=5))
ORDER BY B.description, B.log_event_time

and this with a modified join took 4 seconds

SELECT A.idx, B.log_idx, B.description, B.log_event_time, B.EventName
FROM tblTest1 AS A INNER JOIN tblTest1 AS B ON A.description = B.description AND (B.log_idx Between [a].[log_idx]-1 And [a].[log_idx]+1)
WHERE A.log_event_type=5
ORDER BY B.description, B.log_event_time

for both of these I added the necessary indexes

So it may be worth your while spending a bit of time correcting your duplicate log_idx fields and having an 'early warning' audit query of some sort to check for duplicates as new data is added.

depends on whether this a full report requirement or a step toward it. If the latter, remove sorting from the main query, it will be quicker because it is one less thing to do. You sort at the final stage when there is least amount of rows to sort. - typically in the report

And as mentioned before, anything you can do to reduce the dataset will help performance. For example limiting to a single machine reduced the first query to 6 seconds and the other two to less than a second.