I have written the problem statement below, I would like to know how to use store procedure to achieve few things. These question on store procedures I have written at the end of the question, you can read the problem statement first, thank you.
Below is the current server entry.
ColID--------------Resource ID--------------Project-----------------Date-----------------------------Hours
1-------------------John-------------------------ABC-------------------5/16/2016-----------------------4
2-------------------John-------------------------ABC-------------------5/17/2016-----------------------5
3-------------------John-------------------------ABC-------------------5/18/2016-----------------------6
4-------------------John-------------------------ABC-------------------5/19/2016-----------------------7
Note:In a given date, hours should not exceed 9 hours.
Now from the UI, an user is assigning 4 hours from date 16th May to 19th May to the project XYZ,
Below will be the entry from UI form.
Resource name : John
Start Date : 16th May 2016
End Date : 19th May 2016
Hours Allocated: 4
Project: XYZ
After the above entry, the database should be something like below.
ColID-------------ResourceID-----------------Project---------------Date------------------------------Hours
1-------------------John-------------------------ABC-------------------5/16/2016-----------------------4
2-------------------John-------------------------ABC-------------------5/17/2016-----------------------5
3-------------------John-------------------------ABC-------------------5/18/2016-----------------------6
4-------------------John-------------------------ABC-------------------5/19/2016-----------------------7
5-------------------John-------------------------XYZ-------------------5/16/2016-----------------------4
6-------------------John-------------------------XYZ-------------------5/17/2016-----------------------4
7-------------------John-------------------------XYZ-------------------5/18/2016-----------------------3
8-------------------John-------------------------XYZ-------------------5/19/2016-----------------------2
Note:In a given date, hours should not exceed 9 hours.
Our aim is to allocate 4 hours from the above row ID 5 to 8.
Row ID 5 and 6 will be able to add 4 hours to it, because 16th May and 17th May doesnt go beyond 9 hrs
Row ID 7 will only be able to add 3 hours, since 18th May has only 3 hours left to allocate to. Max is 9 hrs
Row ID 8 will only be able to add 2 hours, since 19th May has only 3 hours left to allocate to. Max is 9 hrs
So can I write the store procedure in such a way that, it first checks the previous assigned hours on one particular day and then checks if it exceeds 9, for eg:
Newly assigned hours + previous assigned hours should be <= 9
if above statement is true then add newly assigned to that particular day
if above statement is false, then 9 - Previous assigned hours should be the logic.
So how this problem can be solved in store procedure?
Thanks,
Shannila