| Type | Read/write | Author | Availability |
| Data provider | Write | Finbourne | Provided with LUSID |
The Lusid.Calendar.Date.Writer provider enables you to write a Luminesce SQL query that creates, updates, or deletes one or more holiday dates in a calendar.
Note: The LUSID user running the query must have sufficient access control permissions to both use the provider and interact with calendars in LUSID. This should automatically be the case if you are the domain owner.
You must construct a valid table of data to write, one date per record. Lusid.Calendar.Date.Writer lists the fields (columns) available to populate with values for each record, and has a set of parameters to help you construct a valid table.
Your query can use the WriteAction field to perform one of the following operations:
- Upsert a calendar date; that is, create a calendar date if it does not exist, and update certain fields if it does. This is the default operation if you omit
WriteAction. - Delete a calendar date.
See also: Lusid.Calendar.Date, Lusid.Calendar.Writer
Basic usage
@table_of_data = <select-statement>;
select * from Lusid.Calendar.Date.Writer where toWrite = @table_of_data;
Query parameters
Lusid.Calendar.Date.Writer has parameters that help you construct a valid table of data to write.
Note: The toWrite parameter is mandatory and used to actually write the table of data into LUSID.
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.Calendar.Date.Writer' and FieldType = 'Parameter';
Data fields
Lusid.Calendar.Date.Writer lists the fields you can populate in your table of data to write.
Depending on the operation you want to perform, the following fields are mandatory to include in the table of data:
| Operation | Mandatory fields |
| Upsert | CalendarScope |
| Delete | CalendarScope |
To list all available fields, 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.Calendar.Date.Writer' and FieldType = 'Column';
Write errors
We recommend examining the results of every write query using one or more of the WriteError, WriteErrorCode and WriteErrorDetail fields.
For each record in the table of data to write, Lusid.Calendar.Date.Writer returns an error code. If the operation is successful, the error code is 0. If unsuccessful, a positive error code and explanation help you discover why LUSID considers the operation invalid.
For example, the query:
@table_of_data = select 'HolidayCalendars' as CalendarScope, 'MyNewHolidayCalendar' as CalendarCode, 'New Year Public Holiday' as DateIdentifier,
#2024-01-01T00:00:00# as FromUtc, #2024-01-01T00:00:00# as ToUtc, 'upsert' as WriteAction;
select WriteErrorCode, WriteError, WriteErrorDetail from Lusid.Calendar.Date.Writer where toWrite = @table_of_data;
... fails because a TimeZone and Description are not specified.
Examples
Note: For more example Luminesce SQL queries, visit our Github repo.
Example 1: Add a holiday date to a calendar
You can add a date to a calendar in your LUSID domain by supplying all mandatory fields and using 'upsert' as WriteAction.
@table_of_data = select 'HolidayCalendars' as CalendarScope, 'MyNewHolidayCalendar' as CalendarCode, 'NewYearsDay' as DateIdentifier,
'UTC' as TimeZone, #2024-01-01T00:00:00# as FromUtc, #2024-01-02T00:00:00# as ToUtc, 'New Year Public Holiday' as Description,
'upsert' as WriteAction;
select WriteError, WriteErrorCode, WriteErrorDetail from Lusid.Calendar.Date.Writer where ToWrite = @table_of_data;
Example 2: Update a holiday date in a calendar
You can update an existing date, such as updating the Description, by supplying the mandatory fields, the fields you want to update, and by using 'upsert' as WriteAction.
@table_of_data = select 'HolidayCalendars' as CalendarScope, 'MyNewHolidayCalendar' as CalendarCode, 'NewYearsDay' as DateIdentifier,
'UTC' as TimeZone, #2024-01-01T00:00:00# as FromUtc, #2024-01-02T00:00:00# as ToUtc, 'Public Holiday for the New Year' as Description,
'upsert' as WriteAction;
select WriteError, WriteErrorCode, WriteErrorDetail from Lusid.Calendar.Date.Writer where ToWrite = @table_of_data;
Example 3: Delete an existing holiday date from a calendar
You can delete an existing date from a calendar by supplying the mandatory fields and using 'delete' as WriteAction.
@table_of_data = select 'HolidayCalendars' as CalendarScope, 'MyNewHolidayCalendar' as CalendarCode, 'NewYearsDay' as DateIdentifier, 'delete' as WriteAction;
select * from Lusid.Calendar.Date.Writer where ToWrite = @table_of_data;
Example 4: Add multiple holiday dates to multiple calendars at the same time
You can add all your dates to your calendars at the same time in one Luminesce query. In this example, the query defaults to an upsert operation as no explicit WriteAction is specified.
@vals = values
('HolidayCalendars', 'MyHolidayCalendar', 'NewYearsDay', #2024-01-01T00:00:00#, #2024-01-02T00:00:00#, 'UTC', 'New Year Public Holiday'),
('HolidayCalendars', 'MyHolidayCalendar', 'ChristmasDay', #2024-12-25T00:00:00#, #2024-12-26T00:00:00#, 'UTC', 'Christmas Day Public Holiday'),
('HolidayCalendars', 'MyOtherHolidayCalendar', 'IndependenceDay', #2024-07-04T00:00:00#, #2024-07-05T00:00:00#, 'UTC', 'Independence Day USA Public Holiday');
@table_of_data = select column1 as CalendarScope, column2 as CalendarCode, column3 as DateIdentifier, column4 as FromUtc,
column5 as ToUtc, column6 as TimeZone, column7 as Description from @vals;
select * from Lusid.Calendar.Date.Writer where ToWrite = @table_of_data;