rajesh1983
New Member
- Joined
- Jan 4, 2019
- Messages
- 4
Table 1:
<colgroup><col span="2"><col><col><col></colgroup><tbody> </tbody> |
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
Table 2: (Data in table 2 will be constant, it is fixed)
Row ID | Reg_start | Reg_End | Work_Start | Work_End | Test_Start | Test_End | Live Date |
1 | 11/28/2018 | 12/08/2018 | 12/09/2018 | 12/27/2018 | 12/29/2018 | 01/17/2019 | 01/19/2019 |
2 | 12/12/2018 | 12/22/2018 | 12/23/2018 | 01/10/2019 | 01/12/2019 | 01/31/2019 | 02/02/2019 |
3 | 12/26/2018 | 01/05/2019 | 01/06/2019 | 01/25/2019 | 01/26/2019 | 02/14/2019 | 02/16/2019 |
4 | 01/09/2019 | 01/20/2019 | 01/20/2019 | 02/07/2019 | 02/09/2019 | 02/28/2019 | 03/02/2019 |
5 | 01/22/2019 | 02/01/2019 | 02/02/2019 | 02/22/2019 | 02/23/2019 | 03/14/2019 | 03/16/2019 |
6 | 02/06/2019 | 02/16/2019 | 02/17/2019 | 03/07/2019 | 03/09/2019 | 03/28/2019 | 03/30/2019 |
7 | 02/27/2019 | 03/09/2019 | 03/10/2019 | 03/28/2019 | 03/30/2019 | 04/18/2019 | 04/20/2019 |
8 | 03/13/2019 | 03/23/2019 | 03/24/2019 | 04/11/2019 | 04/13/2019 | 05/02/2019 | 05/04/2019 |
9 | 03/27/2019 | 04/06/2019 | 04/07/2019 | 04/25/2019 | 04/27/2019 | 05/16/2019 | 05/18/2019 |
10 | 04/09/2019 | 04/19/2019 | 04/20/2019 | 05/08/2019 | 05/10/2019 | 05/30/2019 | 06/01/2019 |
11 | 04/30/2019 | 05/10/2019 | 05/11/2019 | 05/31/2019 | 06/01/2019 | 06/20/2019 | 06/22/2019 |
12 | 05/14/2019 | 05/24/2019 | 05/25/2019 | 06/12/2019 | 06/14/2019 | 07/03/2019 | 07/06/2019 |
13 | 05/29/2019 | 06/08/2019 | 06/09/2019 | 06/27/2019 | 06/29/2019 | 07/18/2019 | 07/20/2019 |
14 | 06/12/2019 | 06/22/2019 | 06/23/2019 | 07/11/2019 | 07/13/2019 | 08/01/2019 | 08/03/2019 |
15 | 07/03/2019 | 07/13/2019 | 07/14/2019 | 08/01/2019 | 08/03/2019 | 08/22/2019 | 08/24/2019 |
16 | 07/16/2019 | 07/26/2019 | 07/27/2019 | 08/14/2019 | 08/16/2019 | 09/05/2019 | 09/07/2019 |
17 | 07/31/2019 | 08/10/2019 | 08/11/2019 | 08/29/2019 | 08/31/2019 | 09/19/2019 | 09/21/2019 |
18 | 08/14/2019 | 08/24/2019 | 08/25/2019 | 09/12/2019 | 09/14/2019 | 10/03/2019 | 10/05/2019 |
19 | 08/27/2019 | 09/06/2019 | 09/07/2019 | 09/25/2019 | 09/27/2019 | 10/17/2019 | 10/19/2019 |
20 | 09/11/2019 | 09/21/2019 | 09/22/2019 | 10/10/2019 | 10/12/2019 | 10/31/2019 | 11/02/2019 |
21 | 09/24/2019 | 10/04/2019 | 10/05/2019 | 10/23/2019 | 10/25/2019 | 11/14/2019 | 11/16/2019 |
22 | 10/08/2019 | 10/18/2019 | 10/19/2019 | 11/06/2019 | 11/08/2019 | 11/27/2019 | 11/30/2019 |
23 | 10/23/2019 | 11/02/2019 | 11/03/2019 | 11/21/2019 | 11/23/2019 | 12/12/2019 | 12/14/2019 |
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
1. Hours required between Reg_start and Reg_End is 5% of hours
2. Hours required between Work_Start and Work_End is 95% of hours
3. Hours required between Test_Start and Test_End is 5% of hours
My requirement is if any day i run the report in table1.Hours occupied as of today i should get the number of hours occupied.
let see couple of examples
Example 1 for order id 121, Hours is 40 and Live date is 01/19/2019.
Table 1:
order ID | Name | Live Date | Hours | Hours occupied as of today |
121 | Rajesh | 01/19/2019 | 40 | ? |
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
In table 2 this row matches with row ID 1
Row ID | Reg_start | Reg_End | Work_Start | Work_End | Test_Start | Test_End | Live Date |
1 | 11/28/2018 | 12/08/2018 | 12/09/2018 | 12/27/2018 | 12/29/2018 | 01/17/2019 | 01/19/2019 |
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
- Reg_start and Reg_End dates are already passed so it should return 0 hours. (This has 5% value. 5% of 40 hrs)
- Work_Start and Work_End dates are also already passed it should return 0 hours. (This has 90% value. 90% of 40 hrs)
- Today's date falls between Test_Start and Test_End so it should return 2 hrs This has 5% value. 5% of 40 hrs =2hrs)
Example 2 for order id 124, Hours is 100 and Live date is 02/16/2019.
Table 1:
order ID | Name | Live Date | Hours | Hours occupied as of today |
124 | Rajesh | 02/16/2019 | 100 | ? |
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
In table 2 this row matches with row ID 3.
Row ID | Reg_start | Reg_End | Work_Start | Work_End | Test_Start | Test_End | Live Date |
3 | 12/26/2018 | 01/05/2019 | 01/06/2019 | 01/25/2019 | 01/26/2019 | 02/14/2019 | 02/16/2019 |
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
when i generate report as of today (Today date is 01/04/2019)
- Today's date falls between Reg_start and Reg_End dates, hence it should return 5 hours. (This has 5% value. 5% of 100 hrs= 5hrs)
- Work_Start and Work_End dates in future, hence it should return 0 hours. (This has 90% value. 90% of 100 hrs)
- Test_Start and Test_End dates is also in future, hence it should return 0 hrs (This has 5% value. 5% of 100 hrs ).
please help me to achieve this.