Lusid.Abor.TrialBalance

The Lusid.Abor.TrialBalance provider enables you to write a Luminesce SQL query that generates trial balances for one or more ABORs between two dates.

Lusid.Abor.TrialBalance returns one record per account with all the debits and credits aggregated for that account unless you specify a GeneralLedgerProfileCode to break down account activity by levels (or categories). More about general ledger profiles.

See also: Lusid.Abor

Basic usage

select * from Lusid.Abor.TrialBalance
where AborScope = <scope> 
and AborCode = <code>
and StartDate = #<date>#
and EndDate = #<date>#
;

Query parameters

Lusid.Abor.TrialBalance has parameters that enable you to filter or refine a query.

To list available parameters, their data types, default values, and an explanation for each, run the following query using a suitable tool:

select FieldName, DataType, ParamDefaultValue, Description from Sys.Field where TableName = 'Lusid.Abor.TrialBalance' and FieldType = 'Parameter';

Data fields

By default, Lusid.Abor.TrialBalance returns a table of data populated with particular fields (columns). You can return a subset of these fields.

To list fields available to return, their data types, whether fields are considered 'main', and an explanation for each, run the following query using a suitable tool:

select FieldName, DataType, IsMain, IsPrimaryKey, SampleValues, Description from Sys.Field where TableName = 'Lusid.Abor.TrialBalance' and FieldType = 'Column';

Note: Fields marked 'main' are returned by queries that select a ^ character, for example select ^ from Lusid.Abor.TrialBalance.

Examples

Note: For more example Luminesce SQL queries, visit our Github repo.

Example 1: Generate a trial balance for a single ABOR and apply a general ledger profile

select * from Lusid.Abor.TrialBalance
where AborScope = 'Abor'
and AborCode = 'Standard'
and StartDate = #2023-08-01#
and EndDate = #2023-08-07#
and GeneralLedgerProfileCode = 'Currency';

Example 2: Generate trial balances for multiple ABORs between the same dates

@abors = values
('Growth', 'UK-Equities'),
('Income', 'UK-Bonds')
;

select * from @abors a
inner join Lusid.Abor.TrialBalance t
  on t.AborScope = a.column1
  and t.AborCode = a.column2
where StartDate = #2023-08-01#
and EndDate = #2023-08-07#;