1

I am new at SQL. The following is the type of data I am working with:

Sample input:

Description                                       Timestamp
-----------                                       ---------
The *machine1- is being analysed                  (time)
The *wheel- is working properly                   (time)
The *motor- requires maintenance                  (time)
The *machine2- is being analysed                  (time)
The *handle- is working properly                  (time)
The *wheel- requires maintenance                  (time)
.
.
.

The table, Machines, is arranged in order of Timestamp of the record. When arranged in this order, the records follow this pattern:

"..... being analysed" ==> ".... working properly" ==> ".... requires maintenance"

So, the logic can be like this: Once you encounter ".... requires maintenance" event, extract data from this event and the two previous events and display them in a single record.

Or, Once you encounter "..... being analysed" event, extract data from this event and the two next events and display them in a single record.

I need to go through thousands of the above events, check the type of description, and extract the relevant information in tables.

Desired output:

Machine        Working Part        Damaged Part
-------        ------------        ------------
machine1       wheel               motor
machine2       handle              wheel
.
.
.

The query I have written:

select 
case when Description like '%analysed%' then SUBSTRING(Description, POSITION('*' IN Description)+1, POSITION('-' IN Description) - POSITION('*' IN Description)-1) end as Machine,
case when Description like '%working properly%' then SUBSTRING(Description, POSITION('*' IN Description)+1, POSITION('-' IN Description) - POSITION('*' IN Description)-1) end as Working Part,
case when Description like '%maintenance%' then SUBSTRING(Description, POSITION('*' IN Description)+1, POSITION('-' IN Description) - POSITION('*' IN Description)-1) end as Damaged Part
from Machines
order by Timestamp asc

But my output is like this:

Machine        Working Part        Damaged Part
-------        ------------        ------------
machine1       
               wheel
                                   motor
machine2       
               handle
                                   wheel
.
.
.

I am using Vertica and I have read online that it is not good at working with loops and variables. Can someone please tell me how I can map these data from a set of 3 statements into a single record as mentioned in the desired output and display them as tables?

Arthur
  • 21
  • 2
  • I removed the inconsistent database tags. Please tag only with the database you are really using. Also, do you have an id of some sort that identifies which values should be together in the result set? – Gordon Linoff May 05 '21 at 15:00
  • Please [edit] your question to explain your logic for determining that the third row of your sample data (`*motor- requires maintenance`) relates to your first row (`machine1`), rather than your fourth row (`machine2`). As you work out this logic think of your SQL table as a bag of index cards, one per row, in no particular order. – O. Jones May 05 '21 at 15:01
  • @O.Jones, The table, Machines, is arranged in order of Timestamp of the record. When arranged in this order, the records follow this pattern: "..... being analysed" ==> ".... working properly" ==> ".... requires maintenance" So, the logic can be like this: Once you encounter ".... requires maintenance" event, extract data from this event and the two previous events and display them in a single record. Or, Once you encounter "..... being analysed" event, extract data from this event and the two next events and display them in a single record. – Arthur May 05 '21 at 15:44

0 Answers0