-1

we have lot of accounts like this..

            real one

id  meternumber AccNumber   firstReadingDate    lastReadingDate
1   2T29         092        **2012-05-19**          2016-03-30
3   151          092        1999-12-31          **2013-06-24**

How to compare firstreadingdate with lastreadingdate to list the accounts that first reading date must not less than lastreadingdate of another meter number. Should have to come as like below
needed as like below.

id  meternumber AccNumber   firstReadingDate    lastReadingDate
1   2T29         092        **2013-06-24**          2016-03-30
3   151          092        1999-12-31          **2012-05-19**

Actual table with active and deactive meter 

id      meternumber AccNumber   Active  firstReadingDate    lastReadingDate
170085  2T29           092           0        2012-05-19          2016-03-30 
184022  151            092           0        1999-12-31         2013-06-24 
170087  2T29           092           1        2016-03-31          NULL
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
callmesri
  • 3
  • 5
  • 1
    Are you using MySQL or Oracle? (Remove one of the tags.) – jarlh Sep 29 '16 at 10:22
  • Have you tried BETWEEN statement? – mfredy Sep 29 '16 at 10:22
  • 1
    "urgently" is not welcome here. please have a look at [ask], particularly "Pretend you're talking to a busy colleague ". Besides, have you tried anything? if so, post your code – Aleksej Sep 29 '16 at 10:22
  • https://docs.oracle.com/cd/B28359_01/server.111/b28286/conditions011.htm – mfredy Sep 29 '16 at 10:22
  • What is the logic for swapping these two values? – Tim Biegeleisen Sep 29 '16 at 10:22
  • Is the swapping limited to two rows each time, or, for example, do you expect to replace the first row with the third row in certain circumstances? – Neria Nachum Sep 29 '16 at 10:25
  • I removed the incompatible database tags. Please add back the tag for the database you are really using. – Gordon Linoff Sep 29 '16 at 10:27
  • thanks for the reply, Yes i need to replace the mismatch. and i am using SQL. actually meter inspection people are wrongly entered, the firstreading date of second meter and lastreading date of first meter. – callmesri Sep 29 '16 at 11:23
  • Again: What DBMS are you using? Is it MySQL? Is it Oracle? Is it something else? SQL is just the general language, and different DBMS don't feature the same functions. – Thorsten Kettner Sep 29 '16 at 11:26
  • As to active/deactive: So you want to ignore active records in this transformation? Then exclude them from your update with `where active = 0`. – Thorsten Kettner Sep 29 '16 at 11:28
  • Hi Thorsten, Thanks for the reply, LEAST and GREATEST not work in MSsql, is there any alternate. Thank you – callmesri Sep 29 '16 at 11:37
  • Hi Tim, thanks for your reply, Logic for this swapping, no two deactivated meters will have mis match date value in their firstreadingdate and lastreading date. First meter disconnected lastreading date should be less than second meter firstreading date. – callmesri Sep 29 '16 at 11:42
  • For simulating `GREATEST` and `LEAST`, read here: http://stackoverflow.com/questions/4725823/function-in-sql-server-2008-similar-to-greatest-in-mysql – Thorsten Kettner Sep 29 '16 at 13:32

1 Answers1

0

So per account you want:

  • for each firstReadingDate the lastReadingDate to be the smallest date after this firstReadingDate, no matter if that date is currently a firstReadingDate or a lastReadingDate.
  • for each lastReadingDate the firstReadingDate to be the highest date before this lastReadingDate, no matter if that date is currently a firstReadingDate or a lastReadingDate.

That gives:

update mytable
set lastReadingDate =
  least
  (
    (
      select min(firstReadingDate)
      from mytable other
      where other.accnumber = mytable.accnumber
      and other.firstReadingDate > mytable.firstReadingDate
    ),
    (
      select min(lastReadingDate)
      from mytable other
      where other.accnumber = mytable.accnumber
      and other.lastReadingDate > mytable.firstReadingDate
    )
  )
, firstReadingDate =
  greatest
  (
    (
      select max(firstReadingDate)
      from mytable other
      where other.accnumber = mytable.accnumber
      and other.firstReadingDate < mytable.lastReadingDate
    ),
    (
      select max(lastReadingDate)
      from mytable other
      where other.accnumber = mytable.accnumber
      and other.lastReadingDate < mytable.lastReadingDate
    )
  )
;

Well, there may be a more elegant solution, but at least this upadte statement is easy to read and understand.

BTW: With such a heavy transformation, you might want to make a backup of your table before doing the update.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Dear Thorsten, Thanks for your post, here both the meters are deactivated. suppose one meter get disconnected today and another installed tomorrow, then first meter reading date must not be tomorrow date. – callmesri Sep 29 '16 at 10:59
  • I must admit I don't understand what you are saying. The statement updates the rows as in your request. Are you saying that there exist another situation in which the update shall not occur? Can you edit your request then so that you show data that my update statement doesn't handle correctly? – Thorsten Kettner Sep 29 '16 at 11:08
  • sorry, correction in last statement " last meter reading date time must not be tomorrow's date" – callmesri Sep 29 '16 at 11:09
  • Oops, I confused some dates. I've updated my answer. And if a lastReadingDate must be at least two days after the firstReadingDate, then add that criteria: `and other.xxxReadingDate < mytable.lastReadingDate and other.xxxReadingDate > mytable.firstReadingDate + interval 1 day`. – Thorsten Kettner Sep 29 '16 at 11:22