vw_timecharges

Overview

This view lists a record for each timesheet entry made by a member of staff.

It can be used to analyse*;-

▪the amount of time booked against a project/task

▪the cost of this time (as a resource cost)

▪how much was charged out (or could have been charged out)

▪how many hours a staff member has booked to chargeable / not chargeable elements of work

▪how much time has been invoiced (or how much is in WIP)

* this list is not exclusive.

 

Column Definitions

Columns

Comments

projectid

The unique project identifier. Join to the Projects table (or other views that have both the projectid and taskuniqueid columns)

taskuniqueid

The unique task identifier. Join to the Tasks table (or other views that have both the projectid and taskuniqueid columns)

projecttaskid

This is a unqiue identifier for a combination of the project and taskuniqueid that can be joined directly to from any other views with the same projecttaskid column.

[Task Code]

The task's task code

[Transaction Date]

The date the timesheet entry relates to

staff_id

The unique staff identifier. Join to the Staff table (or other views that have the staff_id column)

Service

The service of the task that the time was recorded against

[Activity Code]

The activity code the time was recorded against

Activity

The name of the activity the time was recorded against

[Resource Group]

The resource group of the task that the time was recorded against

[Home Group]

The staff member's resource group

[Overtime Type]

The overtime type the time was recorded against

[Charge Method]

The charge method of the task. This can be used to determine which element of work the time was booked against;-

Time based

the time was charged out (refer to Recharge column)

Fee Based

the time was not charged out

(analysis can be used to determine how much could have been charged out if the element of work was Time based - refer to Recharge column)

Nonchargeable

the time was not charged out

(analysis can be used to determine how much could have been charged out if the element of work was Time based - refer to Recharge column)

(analysis can also be used to determine how much this time cost as an overhead without any chargeability to it - refer to Cost column)

Hours

The number of hours recorded

[Timesheet Minutes]

The number of minutes recorded

Cost

The resource cost recorded - This can be used to analyse the total cost of the element of work is.

Recharge

The recharge cost recorded as the systems base currency.

[Currency Charge]

This is the charge value of the element of work booked that is used when invoices are generated.

 

It will be the same as the Recharge column unless a different currency is selected against the task. In this case it will be the Recharge * [Currency Conversion].

 

This can be used to analyse the total charge out value of the element of work (for Time based elements, or the potential charge out value for Fee based elements).

[Currency ID]

The currency ID of the task the time was booked against. i.e GBP, USD etc.

[Currency Conversion]

Used to convert the Recharge into the [Currency Charge].

status

The status of the timesheet line booking.

 

This can be queried to determine what value of time is currently in WIP (Finalised or Costed), how much time is still to be costed (not in WIP), and how much has been invoiced.

In Progress

The staff member has enter the timesheet line but has not yet marked the timesheet as 'complete'

Complete

The staff member has marked the timesheet as 'complete'.

Approved

The timesheet has been 'approved'

Finalised

The timesheet has been 'finalised' but not invoiced

Costed

The timesheet line has been costed but not invoiced

Invoiced

The timesheet line has been invoiced at the Recharge value

Manual Entry

A manual entry entered via the Enquiry in the Windows application.

T/C limit w/off

This occurs when some, or all, of the time is written off due to it there being a Timecharge Limit set on the task.

For example;-

A Timecharge Limit is set on the task for £200.

There has already been £150 invoiced on the task.

The timesheet line is costed at a recharge value of £60 to be invoiced.

So, £50 of the £60 can be invoiced.

This record contains a value of £10 difference and is created during the invoicing process.

Any further timesheet lines relating to this task will have their full recharge value written of in this way.

T/C Reinstate

If the scenario like the one outlined in T/C limit w/off occurs, then the Timecharge Limit is increased to £300, a new 'credit' T/C Reinstate record will be created to offset the £10 recorded as a T/C limit w/off.

Currency Fluctuation

A record with this status is generated if a currency conversion is altered after a timesheet line has been invoiced.

Invoice Adjustment

A timesheet has been re-costed with a different recharge rate after it has been invoiced on the original rate.

For example;-

1 hour at £60 per hour has been invoiced.

The timesheet line has been recosted at 1 hour at £70 per hour.

An invoice adjustment line is created for £10.

System Adjustment

Reserved for any adjustment make by Equisys.

Adjustment

Any other non-write off adjustment line

costed_yn

Identifies if the timesheet line has been costed or not. Costed timesheets have values in costs, recharges and currency charges set.

Invoiced

This can be used to determine if the timesheet line has been invoiced.Any uninvoiced lines that have been costed are defined as being in WIP.

 

 

 

How to use this view

This view can be executed through any third party software you're using to connect to the Timemaster database (i.e. Microsoft Excel, Microsoft PowerBI, Microsoft SQL Management Studio etc.) providing it support the use of T-SQL.

It can also be executed directly from within Timemaster - Please refer to Executing SQL Scripts if you're unsure how to execute scripts from within Timemaster.

Click here for example syntax.

Basic Example;-

select

 projectid,

 taskuniqueid,

 [Task Code],

 [Transaction Date],

 staff_id,

 Service,

 [Activity Code],

 Activity,

 [Resource Group],

 [Home Group],

 [Overtime Type],

 [Charge Method],

 Hours,

 [Timesheet Minutes],

 Cost,

 Recharge,

 [Currency Charge],

 [Curreny ID],

 [Currency Conversion],

 status,

 costed_yn,

 Invoiced,

 projecttaskid

from 

 [vw_timecharges]

where 

 [Transaction Date] >= '20230101' and [Transaction Date] <= '20230331'

 

 

This lists all columns in the view. Columns can be removed as necessary. The last column name should not have a comma separator at the end of it.

Due to the number of timesheet records in your database, it is recommended that this view is used with a 'where' clause as shown in the example above which limits the transaction date between 01/01/2023 and 31/03/2023. You should change these dates to suit your needs.

 

 

Other Examples;-

The following are some examples how the data can be manipulated;-

 

Totals by Project

select

 projectid,

 sum(Hours) as total_hours,

 sum([Timesheet Minutes]) as [total_Timesheet Minutes],

 sum(Cost) as total_Cost,

 sum(Recharge) as total_Recharge,

 sum([Currency Charge]) as [total_Currency Charge]

from 

 [vw_timecharges]

where 

 [Transaction Date] >= '20230101' and [Transaction Date] <= '20230331' 

group by

 projectid

 

 

 

Totals by Project and Task

select

 projectid,

 [Task Code],

 taskuniqueid,

 sum(Hours) as total_hours,

 sum([Timesheet Minutes]) as [total_Timesheet Minutes],

 sum(Cost) as total_Cost,

 sum(Recharge) as total_Recharge,

 sum([Currency Charge]) as [total_Currency Charge]

from 

 [vw_timecharges]

where 

 [Transaction Date] >= '20230101' and [Transaction Date] <= '20230331' 

group by

 projectid,

 [Task Code],

 taskuniqueid

 

 

Totals by Staff Member

select

 staff_id,

 sum(Hours) as total_hours,

 sum([Timesheet Minutes]) as [total_Timesheet Minutes],

 sum(Cost) as total_Cost,

 sum(Recharge) as total_Recharge,

 sum([Currency Charge]) as [total_Currency Charge]

from 

 [vw_timecharges]

where 

 [Transaction Date] >= '20230101' and [Transaction Date] <= '20230331'

group by

 staff_id

 

 

Totals by Staff Member (join to vw_staff)

 

select

 vw_staff.[Staff Name],

 vw_staff.[Linemanager Name],

 sum(Hours) as total_hours,

 sum([Timesheet Minutes]) as [total_Timesheet Minutes],

 sum(Cost) as total_Cost,

 sum(Recharge) as total_Recharge,

 sum([Currency Charge]) as [total_Currency Charge]

from 

 [vw_timecharges]

join

vw_staff on vw_staff.staff_id = [vw_timecharges].staff_id

where 

 [Transaction Date] >= '20230101' and [Transaction Date] <= '20230331'

group by

 vw_staff.[Staff Name],

 vw_staff.[Linemanager Name]

 

 

Any images shown may be representative of the previous version of Timemaster

Report a problem

Please report any errors on this page to timemastersupport@equisys.com