QPR ProcessAnalyzer Script Library

From QPR ProcessAnalyzer Wiki
Jump to: navigation, search

You can create and run ETL scripts in QPR ProcessAnalyzer for various analysis purposes. In this Script Library there are ready-made sample scripts that you can utilize with your own models.

Below you will find more information about these scripts, use cases and instructions for using them. For basic information about managing and running scripts, see Manage Scripts.

Event type + attribute

Purpose

This script adds a selected event attribute to the event name. Events with different names will be created as separate events.

Use Case

If you want to split the event to several events and your model contains this information as event attributes, you can run this script to do so.

How to Use the Script

1. Before running the script:

  1. Open Flowchart Analysis.
  2. In the Settings pane on the right-hand side, select the Event radio button in the Attribute section and from the drop-down menu below, choose the event attribute you want to appear on the Flowchart.
  3. In the Flowchart, you can see different event attribute values below the event type name in each blue box. The default is that three first event attributes are visible but you can change the number in the Settings pane, next to the selected event attribute. As a result you get a new event type box for as many event attributes as you had chosen.

2. Download the script contents by opening the attached file.
3. Copy all the text in the file and in QPR ProcessAnalyzer Excel Client, open the Manage Scripts dialog and select New...
4. Give a name to the script in the Name field (e.g. Event type + attribute).
5. Paste the script contents to the Script Code field and click Save.
6. Click Run to run the script.

Note: This script works only with event attributes, not with case attributes.

Result

As a result of running the script, a new model is created with a new name ("E2EA for FilterID <id>"). This model is created below the same project as the original in Project Workspace. The original model remains unchanged.
The new model is opened and the Flowchart looks similar to this example:

Result event type and attribute.png


Event attribute to Event type

Purpose

This script copies events and case data (from current view), and writes a new model where the selected event attribute and selected activity name (event type) are swapped. Only non-blank attributes are swapped.

Use Case

You can use this script to look at the process by event attribute.

How to Use the Script

1. Before running the script:

  1. Open Flowchart Analysis.
  2. In the Settings pane on the right-hand side, select the Event radio button as the attribute and from the drop-down menu below, choose the event attribute you want to appear on the Flowchart.
OR
In Flowchart Analysis, click the event type(s) to select them.

2. Download the script contents by opening the attached file.
3. Copy all the text in the file and in QPR ProcessAnalyzer Excel Client, open the Manage Scripts dialog and select New...
4. Give a name to the script in the Name field (e.g. Event attribute to event type).
5. Paste the script contents to the Script Code field and click Save.
6. Click Run to run the script.

Note: This script works only with event attributes, not with case attributes.

Result

As a result of running the script, the selected event attribute and selected activity name (event type) are swapped and the original event type name appears as "activity_" in the list of event attributes in the Settings pane of the Flowchart. A new model is created with a new name ("E2A for FilterID <id>"). This model is created below the same project as the original in Project Workspace. The original model remains unchanged.
The Flowchart looks similar to this example after running the script:


Result event attribute to event type.png

Path durations

Purpose

This script reports durations for paths. That is, how many cases go through a particular path.

Use Case

You can use this script to compare the durations different paths take. It provides you with detailed quantitative comparisons that you can use as data for further analysis.

How to Use the Script

  1. Download the script contents by opening the attached file.
  2. Copy all the text in the file and in QPR ProcessAnalyzer Excel Client, open the Manage Scripts dialog and select New...
  3. Give a name to the script in the Name field (e.g. Path durations).
  4. Paste the script contents to the Script Code field and click Save.
  5. Click Run to run the script.

Result

As a result of running the script, you will get a similar table as in the example below:

Result path duration.png


The column "Start" shows the starting event type and "End" the ending event type of the path. The script computes the number, average duration, and percentiles 0 (minimum), 10, 25, 50 (median), 75, 90, and 100 (maximum) duration of paths starting from "Start" and ending to "End". Paths consist of all possible subsequences of the variations.

Note: If there are several instances of the same event type in the case, there will be several paths for them.

Sequence string attribute

Purpose

This script adds a variation label to the case attribute called "sequence string". Sequence string presents the sequence of events in the current model using the current filter. The difference to the Variation string (in Case Analysis) is that the sequence consists only of those event types that are in the scope of the current filter or selection of event types. The Variation string in Case Analysis always consists of all event types.
By default the string is computed from all event types (without any selection of event types). If you select event types from Flowchart or Event Types Analysis, only those will appear in the string. The delimiter between the event type labels is |.

Note:

  • The string will be blank if there are no event types available for the case with the current filter.
  • If a case attribute "sequence string" already exists, it will be overwritten without warning.
  • If you need to create several labels for variations (e.g. using different filters) you have to save the case attribute with a different name: use Case Analysis tool, load the "sequence string" attribute for all cases, change the name of the columns and import the case attribute with its new column name back to the model.

Use Case

How to Use the Script

  1. Download the script contents by opening the attached file.
  2. Copy all the text in the file and in QPR ProcessAnalyzer Excel Client, open the Manage Scripts dialog and select New...
  3. Give a name to the script in the Name field (e.g. Sequence string).
  4. Paste the script contents to the Script Code field and click Save.
  5. Click Run to run the script.
  6. In the Settings pane of Flowchart Analysis on the right-hand side, scroll down to the Benchmark section and from the drop-down menu in Case Attribute, select "sequence string".

Result

As a result of running the script, a case attribute, "sequence string", is added to the model that is currently open.
After benchmarking the current Flowchart Analysis view with the new case attribute (as described in the previous section), Flowchart Analysis looks similar to this example:

Result sequence string.png


Duplicate check

Purpose

This script checks if there are duplicate records for combination of [Case], [Event Type], [Start Time].

Use Case

You can use this script to check if there are duplicate rows of events in the data of your model.

How to Use the Script

  1. Download the script contents by opening the attached file.
  2. Copy all the text in the file and in QPR ProcessAnalyzer Excel Client, open the Manage Scripts dialog and select New...
  3. Give a name to the script in the Name field (e.g. Duplicate check).
  4. Paste the script contents to the Script Code field and click Save.
  5. Click Run to run the script.

Result

As a result of running the script, duplicate events will be shown on their own sheet (with the title "Duplicate events") in Excel Client. If there aren't any duplicate events, an empty sheet with the same title will be shown.

BPMN 2.0 Export

Purpose

This script creates a BPMN 2.0 export file.

Use Case

Export file can be opened in any process modeling or enterprise architect tool supporting BPMN 2.0.

How to Deploy the Script

1. Download the script contents by opening the attached file.
2. Copy all the text in the file and in QPR ProcessAnalyzer Excel Client, open the Manage Scripts dialog and select New...
3. Give a name to the script in the Name field (e.g. Event type + attribute).
4. Paste the script contents to the Script Code field and click Save.

How to Use the Script

1. Before running the script:

  1. Open Flowchart Analysis.
  2. In the Settings pane on the right-hand side, define the desired 'Minimum Flow Volume'. Only the visible flows will be included. Use 0% to include all event types and flows.

2. Start script using Run or Manage Scripts buttons.

Result

As a result of running the script, the BPMN 2.0 export file content is visible in the generated Excel sheet named 'BPMN 2_0'. Copy the content to a text file and import to your modeling tool.

Note: Use QPR ProcessAnalyzer Script Launcher to automatically create a BPMN bpmn export file with this script.

QPR Metrics IntegrationPlatform

QPR Metrics IntegrationPlatform is an ETL tool (Extract, Transform and Load) that includes a set of ready-made connectors to third-party systems. There are four related scripts (Metrics Integration Example, Set up Metrics Connection, Create Metrics Elements, and Update Metrics Values) which are explained below.

Use Case

With these scripts, you can connect to various data sources (e.g. SAP) and get business measurements directly to QPR Metrics models.

Requirements

You need to have QPR Metrics installed and either QPR IntegrationPlatform or QPR ProcessAnalyzer (2017.1 or newer) installed with RunScripts rights.

The first of these four scripts, Metrics Integration Example, is an example script that you can use for doing the necessary configuration, whereas the others are generic scripts. You need to run Set up Metrics Connection script before you can call the scripts Create Metrics Elements or Update Metrics Values.

Metrics Integration Example

Purpose

This is an example script for demonstration purposes in which the needed configuration is done. The demonstration script exemplifies three use cases:

  1. Updating Metrics scorecards with UpdateMetricsValues script,
  2. Creating new Metrics scorecards with CreateMetricsElements script, and
  3. Setting and applying base scorecards to selected Metrics scorecards using SetBaseScorecards script.

Note: Before downloading this script, you need to download the worker scripts (SetupMetricsConnection 2017.1, CreateMetricsElements 2017.1, UpdateMetricsValues 2017.1, and SetBaseScorecards 2017.1) so their ID's are available for use.

How to Use the Script

  1. Download the script contents by opening the attached file.
  2. Copy all the text in the file and in the QPR tab of Excel Client, open the Manage Scripts dialog and select New...
  3. Give a name to the script in the Name field (e.g. Metrics Integration Example).
  4. Paste the script contents to the Script Code field.
  5. Make the following configuration changes:
  • Define the URL (#BaseUrl), your credentials (#LogOnName and #Password) and the model (#MetricsModelName). In this example, Dentorex Group Scorecard is used as the example model.
-- MetricsIntegrationExample
SELECT 'http://localhost/QPR2017-1/Portal/QPR.Isapi.dll/wsforward/MainService.svc/webHttp' AS VAL INTO #BaseUrl;
SELECT 'qpr' AS VAL INTO #LogOnName;
SELECT 'demo' AS VAL INTO #Password;
SELECT 'Dentorex Group Scorecard' AS VAL INTO #MetricsModelName
  • The worker scripts (UpdateMetricsValues, CreateMetricsElements and SetBaseScorecards) expect different table structures to be passed as input in form of temporary tables. See corresponding scripts for correct table schemas.
  • Next, update Integration Platform script identifiers of the worker scripts to correspond to your configuration. Make sure that the #SubScriptId corresponds to the id of CreateMetricsElements, UpdateMetricsValue or SetBaseScorecards script (depending on which operation you want to do) and the 'ScriptId' corresponds to the id of the SetupMetricsConnection script. Worker scripts can be run also directly, without passing through SetupMetricsConnection, if Metricsconnection has already been initialized properly, i.e. appropriate temporary tables contain correct values for e.g. Metrics web service endpoint and session identifier.
  • The places where script identifiers have to be updated are marked with comments, such as below:
-------------------------------------------------------------
-- Set script ID for UpdateMetricsValues
-------------------------------------------------------------
IF OBJECT_ID('tempdb..#SubScriptId') IS NOT NULL
DROP TABLE #SubScriptId;
SELECT '4' AS VAL INTO #SubScriptId;    -- Set script ID for UpdateMetricsValues
(SELECT 'ScriptId', '3')    -- Set script ID for SetupMetricsConnection
--#Run
6. When you have done the configuration changes, click Save.
7. Click Run to run the script.

Result

The needed configuration is done and the created scorecards are shown.

Set up Metrics Connection

Purpose

This script sets up the connection to QPR Metrics. This script is responsible for reading QPR Metrics data from Excel data table, authenticating to QPR Metrics server, and calling a work script that puts the actual data to QPR Metrics.

Before running the script, appropriate values for the following parameters have to be set in order to configure the temporary tables:

  • #BaseUrl: URL of the MEA web service interface, e.g. 'http://localhost/QPR2017-1/Portal/QPR.Isapi.dll/wsforward/MainService.svc/webHttp'
  • #LogOnName: user name for MEA login
  • #Password: password for MEA login
  • #DataTableName: name for the input data table
  • #MetricsModelName: name of the Metrics model where data is to be loaded
  • #SubScriptId: script ID for the Create Metrics Elements or Update Metrics Values script

How to Use the Script

  1. Download the script contents by opening the attached file.
  2. Copy all the text in the file and in the QPR tab of Excel Client, open the Manage Scripts dialog and select New...
  3. Give a name to the script in the Name field (e.g. Set up Metrics Connection).
  4. Paste the script contents to the Script Code field and click Save.
  5. Click Run to run the script.

Result

After a successful script execution, the following text is displayed:

Metrics load succeeded!

Create Metrics Elements

Purpose

This script creates elements according to #MetricsData temporary table. Elements are created row-by-row. After each row, the script is called recursively using the --#Run command.

The following configuration is needed as input:

  • #_Arg1: Contains [CurrentDataRow] column that maintains the number of rows processed so far. Note that you only need to configure this parameter if some data has already been uploaded to Metrics and you want to give this value as the number of processed rows where to start from.
  • #SessionData: MEA web service session identifier.
  • #ModelId: Id of the target metrics model (SC.12345)
  • #SubScriptId: Id of this script (to allow recursive calls)
  • #NumberOfFailures: Contains the number of failures contained so far. If this temporary table doesn't exist, it is created at the beginning. The value is increased when encountering an error.
  • #MetricsData: A temporary table containing the data to be loaded.
The table must contain the following columns (with types):
  • Id (Int, Not null, Identity(1,1)): contains a unique identity for the data row.
  • Element (VARCHAR(80)): The name of the element to be created.
  • Typename (VARCHAR(30)): Type name of the element, e.g. "Scorecard" or "Measure".
  • Parent (VARCHAR(80)): Name of the parent element; must exist in the #MetricsData table.

In addition to these mandatory columns, the #MetricsData table may optionally contain any number of other columns, such as 'Symbol' or 'Description' used for setting the Metrics identifier and element description, correspondingly. The values for these columns are given as name-value parameter pairs for CreateObject command of Metrics.

How to Use the Script

  1. Download the script contents by opening the attached file.
  2. Copy all the text in the file and in the QPR tab of Excel Client, open the Manage Scripts dialog and select New...
  3. Give a name to the script in the Name field (e.g. Create Metrics Elements).
  4. Paste the script contents to the Script Code field and click Save.
  5. Click Run to run the script.

Result

As a result of running the script, elements have been created into QPR Metrics and they have been divided by project.

Update Metrics Values

Purpose

This script loads values to QPR Metrics from #MetricsData temporary table. Values are loaded to Metrics in batches. The number of lines contained in each batch is configurable by script user; the acceptable values are between one (1) and one thousand (1000).

After each batch, this script is called recursively using the --#Run command.

Configuration of batch size: set the batch size (1-1000) at line 103 of the script. Default value for batch size is 1.

The following configuration is needed as input:

  • #_Arg1: Contains [CurrentDataRow] column that maintains the number of rows processed so far. Note that you only need to configure this parameter if some data has already been uploaded to Metrics and you want to give this value as the number of processed rows where to start from.
  • #SessionData: MEA web service session identifier.
  • #ModelId: Id of the target metrics model (SC.12345)
  • #SubScriptId: Id of this script (to allow recursive calls)
  • #NumberOfFailures: Contains the number of failures contained so far.

If this temporary table doesn't exist, it is created at the beginning. The value is increased when encountering an error. #MetricsData: A temporary table containing the data to be loaded.

The table must contain the following columns (with types)
  • Id (Int, Not null, Identity(1,1)): contains a unique identity for the data row.
  • Scorecard (VARCHAR(80)): Identifier of the scorecard holding the value.
  • Element (VARCHAR(80)): Identifier of the element holding the value.
  • Series (VARCHAR(3)): Identifier of the series holding the value.
  • Pediod (VARCHAR(20)): Period declaration in form e.g. XML-formatted date or quarter declaration.
  • Value (VARCHAR(20)): The value to be set.

How to Use the Script

  1. Download the script contents by opening the attached file.
  2. Copy all the text in the file and in the QPR tab of Excel Client, open the Manage Scripts dialog and select New...
  3. Give a name to the script in the Name field (e.g. Update Metrics Values).
  4. Paste the script contents to the Script Code field and click Save.
  5. Click Run to run the script.

Result

As a result of running the script, the values have been updated in QPR Metrics.

Set Base Scorecards

Purpose

This script sets base scorecards for Metrics scorecards and applies inheritance options to each base scorecard with current inheritance options defined in the target model.
Before running the script, appropriate values for the following parameters have to be set in order to configure the temporary tables:

  • #BaseUrl: Base URL for the QPR web service endpoint
  • #ModelId: Model identifier
  • #SessionData: Session identifier
  • #SubScriptId: Id of this script (to allow recursive calls)
  • #SCData: A temporary table consisting of ElementId NVARCHAR(80) and BaseSCSymbol NVARCHAR(80) columns. The rows in the table will be deleted during the execution of this script!

How to Use the Script

  1. Download the script contents by opening the attached file.
  2. Copy all the text in the file and in the QPR tab of Excel Client, open the Manage Scripts dialog and select New...
  3. Give a name to the script in the Name field (e.g. Update Metrics Values).
  4. Paste the script contents to the Script Code field and click Save.
  5. Click Run to run the script.

Result

After a successful script execution, the base scorecards have been applied to selected scorecards in the target Metrics model.

Business Hour Calculations for lead times

Purpose

This script shows how the lead time for any activity can be calculated based on two timestamps: started and completed, as well as taking into use the business hour calendar.

Use Case

Analyze how much time has been spent for the execution using the business hours calendar, ie. effective time.

How to Use the Script


/* Table containing two timestamps for each event. Column names: [ActionStartTime] and [ActionEndTime] */

(SELECT 'ProjectId', 'xxx') UNION ALL 
(SELECT 'MaximumCount', '0') UNION ALL 
(SELECT 'DataTableName', 'EventSourceData') UNION ALL 
(SELECT 'TargetTable', '#CycleTime') 
--#GetAnalysis

/* Replace empty values for the [ActionEndTime] with current date */

UPDATE #CycleTime  
SET [ActionEndTime]=getdate()  
WHERE [ActionEndTime] is null or convert(nvarchar,[ActionEndTime]) = ''; 

/* Calculate duration between start time and end time in business days in minutes
   and map the action details to statuses */

Select  convert(nvarchar(500),a.[PO_Code]) as CaseId,
	convert(datetime,a.[ActionStartTime]) as [StartTime], 
	convert(datetime,a.[ActionEndTime]) as [EndTime],
	Datediff(mi, convert(datetime,a.[ActionStartTime]), convert(datetime,a.[ActionEndTime])) 
       + CASE WHEN Datepart(dw, convert(datetime,a.[ActionStartTime])) = 7 THEN 24*60 ELSE 0 END 
       - (Datediff(wk, convert(datetime,a.[ActionStartTime]), convert(datetime,a.[ActionEndTime])) * (2*24*60) ) 
       - CASE WHEN Datepart(dw, convert(datetime,a.[ActionStartTime])) = 1 THEN 24*60 ELSE 0 END + 
       - CASE WHEN Datepart(dw, convert(datetime,a.[ActionEndTime])) = 1 THEN 24*60 ELSE 0 END
as [Cycle time]
into #times
from #CycleTime a;

/* Sum minutes if more than one time in same status */

Select CaseId, Status, sum([Cycle time]) as [Cycle time]
into #times_sum
from #times
group by CaseId, Status;


/* Define cycle times for specific statuses and convert minutes to hours (business) */

Select distinct a.[CaseId],
convert(decimal,b.[Cycle time])/60 as [Business time: Creation],
convert(decimal,c.[Cycle time])/60 as [Business time: Waiting for PO Approval],
convert(decimal,d.[Cycle time])/60 as [Business time: Approval],
convert(decimal,e.[Cycle time])/60 as [Business time: Waiting for Invoice],
convert(decimal,f.[Cycle time])/60 as [Business time: Invoice Approval],
(convert(decimal,b.[Cycle time])+convert(decimal,c.[Cycle time])+convert(decimal,d.[Cycle time])+
convert(decimal,e.[Cycle time])+convert(decimal,f.[Cycle time]))/60 as [Business time: Total]
into #cycle_time_total
from #times_sum a
left outer join #times_sum b
on a.[CaseId]=b.[CaseId] and b.[Status]='Creation'
left outer join #times_sum c
on a.[CaseId]=c.[CaseId] and c.[Status]='Waiting for PO Approval'
left outer join #times_sum d
on a.[CaseId]=d.[CaseId] and d.[Status]='Approval'
left outer join #times_sum e
on a.[CaseId]=e.[CaseId] and e.[Status]='Waiting For Invoice'
left outer join #times_sum f
on a.[CaseId]=f.[CaseId] and f.[Status]='Invoice Approval'
;

/* Import Case Attributes */

(SELECT 'ProjectId', 'xxx') UNION ALL 
(SELECT 'Append', 'TRUE');
SELECT * FROM #cycle_time_total;
--#ImportCaseAttributes

Result

As a result of running the script, business hour lead times for specified event types will be added as Case Attributes to the model.

Extension - calculate working time using Holiday calendar


Create Function GetWorkingMin(@StartDate DateTime, @EndDate DateTime, @Country Varchar(2)) Returns Int
AS
Begin
    Declare @WorkMin int = 0   -- Initialize counter
    Declare @Reverse bit       -- Flag to hold if direction is reverse
    Declare @StartHour int = 9   -- Start of business hours (can be supplied as an argument if needed)
    Declare @EndHour int = 17    -- End of business hours (can be supplied as an argument if needed)
    Declare @Holidays Table (HDate DateTime)   --  Table variable to hold holidayes

    -- If dates are in reverse order, switch them and set flag
    If @StartDate>@EndDate 
    Begin
        Declare @TempDate DateTime=@StartDate
        Set @StartDate=@EndDate
        Set @EndDate=@TempDate
        Set @Reverse=1
    End
    Else Set @Reverse = 0

    -- Get country holidays from table based on the country code (Feel free to remove this or modify as per your DB schema)
    Insert Into @Holidays (HDate) Select HDate from HOLIDAY Where COUNTRYCODE=@Country and HDATE>=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)

    If DatePart(HH, @StartDate)<@StartHour Set @StartDate = DateAdd(hour, @StartHour, DateDiff(DAY, 0, @StartDate))  -- If Start time is less than start hour, set it to start hour
    If DatePart(HH, @StartDate)>=@EndHour+1 Set @StartDate = DateAdd(hour, @StartHour+24, DateDiff(DAY, 0, @StartDate)) -- If Start time is after end hour, set it to start hour of next day
    If DatePart(HH, @EndDate)>=@EndHour+1 Set @EndDate = DateAdd(hour, @EndHour, DateDiff(DAY, 0, @EndDate)) -- If End time is after end hour, set it to end hour
    If DatePart(HH, @EndDate)<@StartHour Set @EndDate = DateAdd(hour, @EndHour-24, DateDiff(DAY, 0, @EndDate)) -- If End time is before start hour, set it to end hour of previous day

    If @StartDate>@EndDate Return 0

    -- If Start and End is on same day
    If DateDiff(Day,@StartDate,@EndDate) <= 0
    Begin
        If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7  -- If day is between sunday and saturday
            If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0  -- If day is not a holiday
                If @EndDate<@StartDate Return 0 Else Set @WorkMin=DATEDIFF(MI, @StartDate, @EndDate) -- Calculate difference
            Else Return 0
        Else Return 0
    End
    Else Begin
        Declare @Partial int=1   -- Set partial day flag
        While DateDiff(Day,@StartDate,@EndDate) > 0   -- While start and end days are different
        Begin
            If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7    --  If this is a weekday
            Begin
                If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0  -- If this is not a holiday
                Begin
                    If @Partial=1  -- If this is the first iteration, calculate partial time
                    Begin 
                        Set @WorkMin=@WorkMin + DATEDIFF(MI, @StartDate, DateAdd(hour, @EndHour, DateDiff(DAY, 0, @StartDate)))
                        Set @StartDate=DateAdd(hour, @StartHour+24, DateDiff(DAY, 0, @StartDate)) 
                        Set @Partial=0 
                    End
                    Else Begin      -- If this is a full day, add full minutes
                        Set @WorkMin=@WorkMin + (@EndHour-@StartHour)*60        
                        Set @StartDate = DATEADD(DD,1,@StartDate)
                    End
                End
                Else Set @StartDate = DATEADD(DD,1,@StartDate)  
            End
            Else Set @StartDate = DATEADD(DD,1,@StartDate)
        End
        If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7  -- If last day is a weekday
            If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0   -- And it is not a holiday
                If @Partial=0 Set @WorkMin=@WorkMin + DATEDIFF(MI, @StartDate, @EndDate) Else Set @WorkMin=@WorkMin + DATEDIFF(MI, DateAdd(hour, @StartHour, DateDiff(DAY, 0, @StartDate)), @EndDate)
    End 
    If @Reverse=1 Set @WorkMin=-@WorkMin
    Return @WorkMin
End