# how to apply business rules with out breaking to patch missing data

### Question

• Hello Experts,

I am very new to SQL And i have no idea how to apply below  rules

Data is polling for every 5 minutes so 12 time intervals per hour 6:00  -->1

6:05---->2

-

-

6:50---->11

6:55---->12

6:50---->11

 Missing value Patching Rule No values missing No patching 0 Time interval 1 is null Time interval 1 is patched with the value from time interval number 2 subject to this not being null. 1.1 Time interval 12 is null Time interval 12 is patched as the value of time interval 11 subject to this not being null. 1.2 Time intervals 1 and 2 are null Time interval 1 and 2 are both patched with the value of time interval 3 subject to this not being null 1.3 Two consecutive time intervals (excluding both 1 & 2 or both 11&12) are null e.g. time interval 3 and 4 are null Average the preceding and succeeding time intervals of the missing 2 values. If time intervals 3 and 4 are null then these become the average of time intervals 2 and 5. 1.4 Time intervals 11 and 12 are missing Time interval 11 and 12 are both patched with the value of time interval 10 subject to this not being null 1.5 Some time intervals between 2 and 11 are null with 6 or more non null time intervals Time interval is patched with the average of interval – 1 and interval + 1 subject to these not being null. For example if time interval 5 was null this would be patched with an average of time interval 4 and time interval 6 n.b this rule can happen up to a maximum of 5 times. 1.6 Three consecutive time intervals are missing Set all time intervals for the period as null 2.1 More than 6 time intervals are null Set all time intervals for the period as null 2.2

CREATE TABLE DATA_MIN

(

DAYASNUMBER    INTEGER,

TIMEID         INTEGER,

COSIT          INTEGER,

LANEDIRECTION  INTEGER,

VOLUME         INTEGER,

AVGSPEED       INTEGER,

PMLHGV         INTEGER,

CLASS1VOLUME   INTEGER,

CLASS2VOLUME   INTEGER,

CLASS3VOLUME   INTEGER,

)

Sampledata

DAYASNUMBER TIMEID      COSIT     LANEDIRECTION    VOLUME    AVGSPEED PMLHGV    CLASS1vol  LINK_ID

20,140,110  201,401,102,315    5    1    47    12,109    0    45    5,001

20,140,110  201,401,102,325    5    1    33    12,912    0    29    5,001

20,140,110  201,401,102,330    5    1    39    14,237    0    37    5,001

20,140,110  201,401,102,345    5    1    45    12,172    0    42    5,001

20,140,110  201,401,102,350    5    1    30    12,611    0    29    5,001

20,140,111  201,401,100,000    5    1    30    12,611    0    29    5,001

output something like FOR above sample data

DAYASNUMBER TIMEID      COSIT     LANEDIRECTION    VOLUME    AVGSPEED PMLHGV    CLASS1  LINK_ID                                                                                                                                                                                                                                                                                                                                                                                            Rule

20,140,110  201,401,102,315    5     1    47    12,109    0    45   5,001                                                                                                 0

20,140,110  201,401,102,320    5    1    40    12,109    0    45    5,001                                                                                                 1.4(patched row)

20,140,110  201,401,102,325    5    1    33    12,912    0    29    5,001                                                                                                  0

20,140,110  201,401,102,330    5    1    39    14,237    0    37    5,001                                                                                                   0

20,140,110  201,401,102,335    5    1    42    14,237    0    37    5,001                                                                                               1.4(patched row)

20,140,110  201,401,102,345    5    1    45    12,172    0    42    5,001                                                                                                  0

20,140,110  201,401,102,350    5    1    30    12,611    0    29    5,001                                                                                                  0

20,140,110  201,401,102,355    5    1    30    12,611    0    29    5,001                                                                                                   1.2(patched row)

20,140,111  201,401,100,000    5    1    30    12,611    0    29    5,001

Any help and suggestions to extend the code to achieve would be greatly appreciate.

Note:Here the key value is Volume to be patched for missed time intervals