0

My database table looks like this:

ScannerStatusHistoryID  DetectorID                 TimeStamp                    HBD1    HBD2    HWD1    HWD2    RC1    RC2     RC3      RC4
          1                 948         2017-03-17 08:44:34.0000000 +01:00       3        3      3        0      3      3       0        3
          2                 948         2017-03-17 09:44:34.0000000 +01:00       3        3      3        0      3      3       0        3
          3                 948         2017-03-17 10:44:34.0000000 +01:00       3        2      3        0      3      3       0        3
          4                 948         2017-03-17 11:44:34.0000000 +01:00       3        2      3        0      3      3       0        1
          5                 948         2017-03-17 12:44:34.0000000 +01:00       3        2      3        0      3      3       0        1

And i have a query like this:

[Query]
        public IQueryable<ScannerStatusHistory> GetScannerStatus(int detectorID)
        {
            var lastRow = ObjectContext.ScannerStatusHistories.Where(t => t.DetectorID == detectorID).OrderByDescending(d => d.TimeStamp).FirstOrDefault();
            //Get last changed row
            if (lastRow != null)
            {
                var lastChangeRow = ObjectContext.ScannerStatusHistories
                .Where(t => t.DetectorID == detectorID
                    && (t.HBD1 != lastRow.HBD1 || t.HBD2 != lastRow.HBD2 || t.HWD1 != lastRow.HWD1 || t.HWD2 != lastRow.HWD2 || t.RC1 != lastRow.RC1 || t.RC2 != lastRow.RC2 || t.RC3 != lastRow.RC3 || t.RC4 != lastRow.RC4))
                .OrderByDescending(d => d.TimeStamp)
                .FirstOrDefault();
                //Return next row
                if (lastChangeRow != null)
                {
                    return ObjectContext.ScannerStatusHistories
                     .Where(x => lastChangeRow.TimeStamp < x.TimeStamp
                         && x.DetectorID == detectorID)
                     .OrderBy(d => d.TimeStamp)
                     .Take(1);
                }
            }
            return ObjectContext.ScannerStatusHistories.Where(t => t.DetectorID == detectorID).OrderBy(d => d.TimeStamp).Take(1);
        }

What it does, it searches for the last row where a change is seen between HBD1 Or HBD2 Or HWD1 Or HWd2 Or RC1 Or Rc2 Or Rc3 Or Rc4.

The result i'm getting right now based on the example is:

ScannerStatusHistoryID  DetectorID                 TimeStamp                    HBD1    HBD2    HWD1    HWD2    RC1    RC2     RC3      RC4
      4                   948         2017-03-17 11:44:34.0000000 +01:00         3        2      3        0      3      3       0        1

But this is only because 1 column has changed. and so it will grab that row. However, is there a posibillity to check this for every row, and show the date that column has changed. Instead of when 1 column has changed.

So the desired result would be:

HBD1 = 2017-03-17 08:44:34.0000000 +01:00 
HBD2 = 2017-03-17 10:44:34.0000000 +01:00 
HWD1 = 2017-03-17 08:44:34.0000000 +01:00 
HWD2 = 2017-03-17 08:44:34.0000000 +01:00 
RC1 = 2017-03-17 08:44:34.0000000 +01:00 
RC2 = 2017-03-17 08:44:34.0000000 +01:00 
RC3 = 2017-03-17 08:44:34.0000000 +01:00 
RC4 = 2017-03-17 11:44:34.0000000 +01:00

The Result i'm getting now:

HBD1 = 2017-03-17 11:44:34.0000000 +01:00
HBD2 = 2017-03-17 11:44:34.0000000 +01:00
HWD1 = 2017-03-17 11:44:34.0000000 +01:00
HWD2 = 2017-03-17 11:44:34.0000000 +01:00 
RC1 = 2017-03-17 11:44:34.0000000 +01:00
RC2 = 2017-03-17 11:44:34.0000000 +01:00
RC3 = 2017-03-17 11:44:34.0000000 +01:00
RC4 = 2017-03-17 11:44:34.0000000 +01:00

Is this posible to do in one query, or do i have to excecute 8 seperate queries which check the change state of each column?

Mitch
  • 1,173
  • 1
  • 10
  • 31
  • If you see last value repeated then more likely you forgot to [capture variable](http://stackoverflow.com/q/271440/1997232) somewhere. – Sinatr Mar 20 '17 at 13:40
  • Just at a quick glance - it looks like you only set your comparison row (lastRow) just the once before the query - this would not let you compare the difference between rows. You will just find the difference from the initial state which in descending time order will be the 11.44:34 row. – PaulF Mar 20 '17 at 14:04

0 Answers0