How to get the earliest Startdate for unit that may have been transferred.
Hint Ref: 021601080031
Hint Date: 08/01/2016
Hint Details:
When determining the Start Date of a Subcontract Unit, should that item have been transferred, then the start date of the 'live' subcontract record is not tsub contract start date of the unit trail, or the customer's actual first move-in date.
When determining the length of stay for an individual customer's contract item, for example, it may not have been originally moved in on the same date as the contract was created, so you might get an incorrect result when trying to determine the actual customer move in date.
To do this, we would need to follow the trail of transfers in order to find the original subcontract record to get the right start date.
The function below does this for you;
Pass a SubcontractID and the date returned is the original move in subcontract record's start-date, no matter how many time the unit may have been transferred:
create FUNCTION "spaceman"."GetSubcontractStartDate"
(in iSubid char(20))
returns date
begin
declare thisstartdate date;
declare nextsubid char(20);
select Startdate,transferedfrom into thisstartdate,nextsubid from subcontract where subcontractid=iSubid;
while nextsubid is not null loop
select Startdate,transferedfrom into thisstartdate,nextsubid from subcontract where subcontractid=nextsubid
end loop;
return(thisstartdate)
end
(Please Note: This Procedure can be destructive and should only be used by Advanced Users. RADical Systems (UK) Limited or its Partners cannot be held responsible, in anyway, for any consequence of using this or any other Database Function, Procedure, XML Script or SQL command. Responsibility resides solely with the user.
IT IS HIGHLY RECOMMENDED THAT A FULL AND VALID SPACE MANAGER DATABASE BACKUP IS TAKEN AND VERIFIED AS VALID BEFORE MAKING ANY CHANGES TO THE DATABASE.)