compute overtime (TUR 2006-7-31)

say we know the clock-in/clock out pairs on an employee on a given date. An employee must work N minutes in a day. The time spent in the office after N minutes is overtime. We need to calculate the (possible) minutes of overtime an employee works on a given date. Besides, overtime must be returned split into special bands: Ord_day integer, /* minutes of overtime in band 6:00-22:00 of a work day */ Ord_night integer, /* minutes of overtime in band 22:00-6:00 of a work day */ Hol_day integer, /* minutes of overtime in band 6:00-22:00 of a holiday */ Hol_night integer, /* minutes of overtime in band 22:00-6:00 of a holiday */ You will write the following SP: create procedure SplitOvertime_( curdpt integer, /* department ID */ curemp integer, /* employee ID */ curdat date, /* current date */ N integer, /* the no of minutes to work in a day (overtime starts after that) */ night_fm integer /* starting time for night hours (in minutes after 0:00) es: 22*60 */ night_to integer /* final time for night hours (in minutes after 0:00) es: 6*60 */ mode integer /* 0= overtime belongs to the day a shift starts */ /* 1= overtime belongs to the day a shift ends */ /* 2= overtime belongs to the day they are actually worked */ ) returns ( Ord_day integer, /* minutes of overtime in band 6:00-22:00 of a work day */ Ord_night integer, /* minutes of overtime in band 22:00-6:00 of a work day */ Hol_day integer, /* minutes of overtime in band 6:00-22:00 of a holiday */ Hol_night integer, /* minutes of overtime in band 22:00-6:00 of a holiday */ id_shf integer /* worked shift ID */ ) if the employee works multiple shifts in a day (this is quite possible) the SP will return multiple rows . Coding rules apply. SQL must be for Firebird 1.5 ## Deliverables 1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done. 2) Deliverables must be in ready-to-run condition, as follows (depending on the nature of the deliverables): a) For web sites or other server-side deliverables intended to only ever exist in one place in the Buyer's environment--Deliverables must be installed by the Seller in ready-to-run condition in the Buyer's environment. b) For all others including desktop software or software the buyer intends to distribute: A software installation package that will install the software in ready-to-run condition on the platform(s) specified in this bid request. 3) All deliverables will be considered "work made for hire" under U.S. Copyright law. Buyer will receive exclusive and complete copyrights to all work purchased. (No GPL, GNU, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site per the coder's Seller Legal Agreement). ## Platform SQL