none
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

    1. 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

     

     


    This will be more info table structure

     CREATE TABLE DATA_MIN

    (

      DAYASNUMBER    INTEGER,

      TIMEID         INTEGER,

      COSIT          INTEGER,

      LANEDIRECTION  INTEGER,

      VOLUME         INTEGER,

      AVGSPEED       INTEGER,

      PMLHGV         INTEGER,

      CLASS1VOLUME   INTEGER,

      CLASS2VOLUME   INTEGER,

      CLASS3VOLUME   INTEGER,

      LINK_ID        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


    Thanks in advance
    • Edited by UBHAS Friday, May 02, 2014 8:02 AM
    Thursday, May 01, 2014 9:18 PM

All replies

  • row_number() OVER (PARTITION BY LANEDIRECTION ORDER BY TIMEID) AS RN,DAYASNUMBER,(*to_date*(timeid,'yyyymmdd hh24miss')) as cte, COSIT,

    Are you in the right place? to_date is an Oracle function, and this forum is for Microsoft SQL Server which is a different product.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, May 01, 2014 10:15 PM