Dears.
I need help to find the way to calculate if some service level agreement were fulfilled in order to control my telecommunication project, i have three diferent tables in a excel sheet.
The first table call, Table1.xls contain (Is only an example the table have data fo more than 10000 registers):
TipoCombSitioA
| Alarm | TSLA(min) |
|---|
| ASINONO | AC | 180 |
| ASISISI | AC | 180 |
| BNOSINO | AC | 540 |
| ASISISI | DC | 120 |
The Second Table call Table2.xls contain (Is only an example the table have data fo more than 10000 registers):
| SiteID | Initial Date | Initial Time | Solution Date | Solution Time | Ticket Number | Category |
|---|
| 1645 | 25-Oct-2015 | 00:00 | 25-Oct-2015 | 17:00 | 0001 | AC |
| 1657 | 23-Oct-2015 | 10:20 | 24-Oct-2015 | 01:00 | 0002 | DC |
| 1648 | 25-Oct-2015 | 00:36 | 19-Nov-2015 | 07:00 | 0003 | AA |
| 1627 | 10-Oct-2015 | 12:56 | 25-Oct-2015 | 13:56 | 0004 | AC |
and The third table call Table 3.xls contain(Is only an example the table have data fo more than 10000 registers):
| Asset Number | TipoCombSitio |
|---|
| 1645 | ASINONO |
| 1657 | ASISISI |
| 1648 | BNOSINO |
| 1627 | ASISISI |
According to the tables my idea is detect if all the tickets in the table two fulfill or not the SLA that is related in the table 1, for that i think in something like this, IF TipoCombSitioA from the table 1 is equal to the TipoCombSitio from table 3 and Category from the table 3 is equal to Alarm in the Table1, then take Solution Date and Solution Time and Initial Date and Initial Time and substract them in order to obtain the duration time in minutes, and if this result is higher than TSLA in table 1 create an new field in the database that said NOT FULFILL or FULFILL if is minor.
In the tables example, the first register must say NOT Fulfill because the SLA is 180Min and the duration time was 1020min
Really apreciate your help if is possible to do this.
