SQLTeam.com | Weblogs | Forums

How to create the store procedure for this scenario


#1

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