I have an idea that I am trying to implement but can't seem to get a focused direction. I will explain the concept and then what I have established to help in getting the full picture.
I would like to cull out records that have like information for (3) consecutive weeks in a row. There is one record per week generated, and there could be 20 weeks or more of records created. I would like to look at all the records and look for patterns of fields that have the same information for (3) weeks in a row. Once I have found the pattern I would like to tag them which should be easy enough.
I am working with (1) table in which all records are stored in.
This is and inspection table (OBI) where information is stored with respects to that weeks inspections for each JOBID.
This inspection has (7) lineitems and each lineitem is rated from 1 - 5
If any (1) of the (7) lineitems fails (or scores 3 - 5) for (3) weeks in a row I need to flag this record (JOBID) to be dealt with separately.
I am looking for ideas on how to make this as dynamic a possible, please don't be shy.