Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
<body><p>Hi,</p> <p>I have problems calculating the availability/preoccupation of items in a hire-shop. </p> <p>I have these tables:</p> <p><col width="87"></col><col span="2" width="85"></col><col width="86"></col> <tr height="20"> <td width="87" class="xl66" height="20"> <p> Items:</p> <p> <table width="344" cellpadding="0" cellspacing="0" border="1" class="MsoNormalTable"> <tbody> <tr> <td valign="bottom" width="87"> <p ><b>ItemNumber</span></b></span></p> </td> <td valign="bottom" width="85"> <p ><b>CreatedDate</span></b></span></p> </td> <td valign="bottom" width="85"> <p ><b>DeletedFlag</span></b></span></p> </td> <td valign="bottom" width="87"> <p ><b>DeletedDate</span></b></span></p> </td> </tr> <tr> <td valign="bottom" width="87"> <p >No1</span></p> </td> <td valign="bottom" width="85"> <p align="right" >01-01-2006</span></p> </td> <td valign="bottom" width="85"> <p >True</span></p> </td> <td valign="bottom" width="87"> <p align="right" >15-12-2008</span></p> </td> </tr> <tr> <td valign="bottom" width="87"> <p >No2</span></p> </td> <td valign="bottom" width="85"> <p align="right" >01-02-2006</span></p> </td> <td valign="bottom" width="85"> <p >False</span></p> </td> <td valign="bottom" width="87"></td> </tr> <tr> <td valign="bottom" width="87"> <p >No3</span></p> </td> <td valign="bottom" width="85"> <p align="right" >15-07-2007</span></p> </td> <td valign="bottom" width="85"> <p >True</span></p> </td> <td valign="bottom" width="87"> <p align="right" >29-11-2007</span></p> </td> </tr> <tr> <td valign="bottom" width="87"> <p >No4</span></p> </td> <td valign="bottom" width="85"> <p align="right" >01-01-2006</span></p> </td> <td valign="bottom" width="85"> <p >False</span></p> </td> <td valign="bottom" width="87"></td> </tr> </tbody> </table> </p> </td> <td width="85" class="xl66"><br /></td> <td width="85" class="xl66"><br /></td> <td width="86" class="xl66"><br /></td> </tr> </p> <p>Turnover:</p> <table width="258" cellpadding="0" cellspacing="0" border="1" class="MsoNormalTable"> <tbody> <tr> <td valign="bottom" width="88"> <p ><b>ItemNumber</span></b></p> </td> <td valign="bottom" width="85"> <p ><b>Year</span></b></p> </td> <td valign="bottom" width="85"> <p ><b>Days</span></b></p> </td> </tr> <tr> <td valign="bottom" width="88"> <p >No1</span></p> </td> <td valign="bottom" width="85"> <p align="right" >2006</span></p> </td> <td valign="bottom" width="85"> <p >180</span></p> </td> </tr> <tr> <td valign="bottom" width="88"> <p >No1</span></p> </td> <td valign="bottom" width="85"> <p align="right" >2007</span></p> </td> <td valign="bottom" width="85"> <p >75</span></p> </td> </tr> <tr> <td valign="bottom" width="88"> <p >No1</span></p> </td> <td valign="bottom" width="85"> <p align="right" >2008</span></p> </td> <td valign="bottom" width="85"> <p >5</span></p> </td> </tr> <tr> <td valign="bottom" width="88"> <p >No2</span></p> </td> <td valign="bottom" width="85"> <p align="right" >2006</span></p> </td> <td valign="bottom" width="85"> <p >60</span></p> </td> </tr> <tr> <td valign="bottom" width="88"> <p >No2</span></p> </td> <td valign="bottom" width="85"> <p align="right" >2007</span></p> </td> <td valign="bottom" width="85"> <p >90</span></p> </td> </tr> <tr> <td valign="bottom" width="88"> <p >No3</span></p> </td> <td valign="bottom" width="85"> <p align="right" >2007</span></p> </td> <td valign="bottom" width="85"> <p >80</span></p> </td> </tr> </tbody> </table> <td width="85" class="xl66"><br /></td> <td width="85" class="xl66"><br /></td> <td width="86" class="xl66"><br /></td> <td width="85" class="xl66"><br /></td> <td width="85" class="xl66"><br /></td> <td width="86" class="xl66"><br /></td> <td width="85" class="xl66"><br /></td> <td width="85" class="xl66"><br /></td> <td width="86" class="xl66"><br /></td> <p>For each year/item i would like to calculate the percentage of days the item has been let. </p> <p>Ex: Item No1/2008. This should be the 5 / days between 01-01-2008 and 15-12-2008/100 = 1,42%, and Item No4 should be 0% for all years. </p> <p>I hope that my question makes sense</p> <p> <div class="aTranslator" id="tran-en"> <div></div> </div> <div class="aTranslator" id="tran-de"></div> </p></body>
subtract the first date plus 1 from the last date
Date = date field that you are reffering to.
=
max(Date)-(min(Date)-1) /5I need to calculate the total availability within each year.
So f.eks Item "No1" is availabel 365 days in 2007, but only 350 days in 2008 (due to the deleted date)
What i need to do is to calculate the availability for each Item every year, checking for the CreatedDate and/or DeletedDate.