<style>p { margin: 0; }span.fr-emoticon.fr-emoticon-img { background-repeat: no-repeat !important; font-size: inherit; height: 1em; width: 1em; min-height: 20px; min-width: 20px; display: inline-block; margin: -0.1em 0.1em 0.1em; line-height: 1; vertical-align: middle; } span.fr-emoticon { font-weight: normal; font-family: "Apple Color Emoji", "Segoe UI Emoji", "NotoColorEmoji", "Segoe UI Symbol", "Android Emoji", "EmojiSymbols"; display: inline; line-height: 0; } blockquote { border-left: solid 2px #5e35b1; color: #5e35b1; margin-left:0; padding-left:5px;}blockquote blockquote{ border-color: #00bcd4; color: #00bcd4;}blockquote blockquote blockquote{ border-color: #43a047; color: #43a047;} table.grid{ border-collapse: collapse;} table.grid td, table.grid th { border: 1px solid #ddd;} .fr-fic.fr-dib{ display: block; margin: 5px auto;}.fr-fic.fr-dib.fr-fir{ text-align: right; margin: 5px 0 5px auto;}.fr-fic.fr-dib.fr-fil{ text-align: left; margin: 5px auto 5px 0;}.fr-fic.fr-dii{ float: none; margin: 5px auto;}.fr-fic.fr-dii.fr-fil{ float: left; margin: 5px auto;}.fr-fic.fr-dii.fr-fir{ float: right; margin: 5px auto;}img.fr-dib.fr-fir { margin-right: 0; text-align: right;}img.fr-dib.fr-fil { margin-left: 0; text-align: left;}img.fr-dib { margin: 5px auto; display: block; float: none;}img.fr-bordered { box-sizing: content-box; border: solid 5px #CCC;}img.fr-shadow { box-shadow: 10px 10px 5px 0px #cccccc;}img.fr-rounded { border-radius: 10px; -moz-border-radius: 10px; -webkit-border-radius: 10px; -moz-background-clip: padding; -webkit-background-clip: padding-box; background-clip: padding-box;}</style><p><strong>In this guide we will cover:</strong></p><p><strong>- What is a Database Function?</strong></p><p><strong>- List of Database Functions</strong></p><p><strong>- Example Report</strong></p><p><br></p><p><br></p><p><strong><span style="font-size: 14pt;">What is a Database Function?</span></strong></p><p>A database function is a section of text that runs a built-in SQL query when used in another query. This can be used to format a report without making your own query unnecessarily long or complicated.</p><p><br></p><p>To use these, add "dbo." in front of the function name within the query.</p><p><br></p><p><strong><span style="font-size: 14pt;">List of Database Functions</span></strong></p><p>Halo has the following stored procedures. For the below, the parameters are listed as their datatype and what the values should relate to.</p><p style="margin: 0px;"><br style="color: rgb(0, 0, 0); font-family: "Segoe UI", "Segoe UI Web (West European)", "Segoe UI", -apple-system, BlinkMacSystemFont, Roboto, "Helvetica Neue", sans-serif; font-size: 13px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;"></p><ul id="isPasted"><li><strong>VariableWorkTime </strong>(datetime startdate, datetime enddate).</li><li><strong>Get_ServiceUser_By_ServiceID_Userid </strong>(int serviceid, int userid).</li><li><strong>GetSLATimeLeft </strong>(datetime enddate, int slaid).</li><li><strong>Fn_GetWorkingHours_startday </strong>(datetime startdate, int slaid, int timezoneOffset).</li><li><strong>Fn_GetWorkingHours_endday </strong>(datetime enddate, int slaid, int timezoneOffset).</li><li><strong>Fn_GetWorkingHours_workdaysbetween </strong>(datetime startdate, datetime enddate, int slaid, bit hoursNotDays, int timezoneOffest).</li><li><strong>Fn_GetWorkingHours </strong>(datetime slaFixByDate, int slaid, varchar(255) timezone_override).</li><li><strong>Fn_FormatTime </strong>(float time).</li><li><strong>Fn_FormatTimeChar </strong>(float time).</li><li><strong>GetMonthlyValue </strong>(float amount, int period).</li><li><strong>Fn_GetWorkingHours_datetimes </strong>(datetime startdate, datetime slaFixByDate, int slaid, int faultid, varchar(255) timezone_override).</li><li><strong>getInvoiceLineQuantity </strong>(float quantity, int lineid, int clientid, int recurringInvoiceQuantityType). </li><li><strong>GetSLATimeLeft </strong>(datetime Date, int SLAID) </li><li><strong>GetTimeBetweenDatesWithSLAID </strong>(datetime Start Date, datetime End Date, int SLA ID) </li><li><strong>GetTimeBetweenDatesWithWorkdayID</strong> (datetime Start Date, datetime End Date, int Workday ID)</li></ul><p><br></p><p><strong><span style="font-size: 14pt;">Example Report</span></strong></p><p>Let's look at an example of how this works. Here we have a report that shows time taken.</p><p><br></p><div style="color: #000000;background-color: #fffffe;font-family: Consolas, 'Courier New', monospace;font-weight: normal;font-size: 14px;line-height: 19px;white-space: pre;" id="isPasted"><span style="color: #0000ff;">select</span><span style="color: #000000;"> </span><span style="color: #0000ff;">top</span><span style="color: #000000;"> </span><span style="color: #098658;">10</span><span style="color: #000000;"> </span><span style="color: #c700c7;">round</span><span style="color: #000000;">(elapsedhrs,</span><span style="color: #098658;">2</span><span style="color: #000000;">) </span><span style="color: #0000ff;">as</span><span style="color: #000000;"> [Time Taken] </span><span style="color: #0000ff;">from</span><span style="color: #000000;"> faults<br></span><span style="color: #0000ff;">where</span><span style="color: #000000;"> fdeleted</span><span style="color: #778899;">=</span><span style="color: #098658;">0</span></div><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjE5Mzk1MjFhLTA3MzMtNDIxOS04ZjllLTY3MDhiZjExNjI5YiJ9.GG8l2CW2rWlSagPN0fDnX-Q7XhsTkKFIV1XsIeOtHOE" class="fr-fic fr-fil fr-dib" width="153" height="98"></p><p><strong><span style="font-size: 10pt;">Fig 1. Report result without dbo.</span></strong></p><p><br></p><p>Inserting the "<strong>fn_formattime</strong>" function with "<strong>dbo.</strong>" in front of it will format the report numbers as hours:minutes instead of as a decimal of the hour.</p><p><br></p><div style="color: #000000;background-color: #fffffe;font-family: Consolas, 'Courier New', monospace;font-weight: normal;font-size: 14px;line-height: 19px;white-space: pre;" id="isPasted"><span style="color: #0000ff;">select</span><span style="color: #000000;"> </span><span style="color: #0000ff;">top</span><span style="color: #000000;"> </span><span style="color: #098658;">10</span><span style="color: #000000;"> dbo.fn_formattime(</span><span style="color: #c700c7;">round</span><span style="color: #000000;">(elapsedhrs,</span><span style="color: #098658;">2</span><span style="color: #000000;">)) </span><span style="color: #0000ff;">as</span><span style="color: #000000;"> [Time Taken] </span><span style="color: #0000ff;">from</span><span style="color: #000000;"> faults<br></span><span style="color: #0000ff;">where</span><span style="color: #000000;"> fdeleted</span><span style="color: #778899;">=</span><span style="color: #098658;">0</span></div><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImRjNGY0ZGZjLTZmNjAtNGZmNS04ODIzLWRkNzFkNDA5MzAxZCJ9.7Z_2z2rJAQTjdj_ojttIPRuZY-AB7XGxY5aA0jRQwAY" class="fr-fic fr-fil fr-dib" width="154" height="96"></p><p><strong><span style="font-size: 10pt;">Fig 2. Report result with dbo.</span></strong></p><p><br></p><p><strong>Some functions, like dbo.GetSLATimeLeft require a certain format. If you are getting wrong results, try this:</strong></p><p><br></p><div style="color: #000000;background-color: #fffffe;font-family: Consolas, 'Courier New', monospace;font-weight: normal;font-size: 14px;line-height: 19px;white-space: pre;" id="isPasted"><span style="color: #0000ff;">select</span><span style="color: #000000;"> </span><span style="color: #0000ff;">top</span><span style="color: #000000;"> </span><span style="color: #098658;">10</span><span style="color: #000000;"> </span><span style="color: #000000;">dbo.GetSLATimeLeft(</span><span style="color: #c700c7;">CONVERT</span><span style="color: #000000;">(</span><span style="color: #0000ff;">VARCHAR</span><span style="color: #000000;">(</span><span style="color: #098658;">16</span><span style="color: #000000;">), </span><span style="color: #c700c7;">CONVERT</span><span style="color: #000000;">(DATETIME, fixbydate, </span><span style="color: #098658;">103</span><span style="color: #000000;">), </span><span style="color: #098658;">120</span><span style="color: #000000;">), slaid)</span><span style="color: #000000;"> </span><span style="color: #0000ff;">as</span><span style="color: #000000;"> [SLA Time Left] </span><span style="color: #0000ff;">from</span><span style="color: #000000;"> faults<br></span><span style="color: #0000ff;">where</span><span style="color: #000000;"> fdeleted</span><span style="color: #778899;">=</span><span style="color: #098658;">0</span></div>
