Sometimes you need to use a query to obtain data for several dates at once from a periodic information register. A typical example is working with exchange rates. Let's consider an algorithm for solving this problem using an example.

Formulation of the problem

A document “Sales of Goods and Services” has been created in the database, in the header of which there is the attribute “Currency”. The request requires for each document to obtain the current exchange rate from the header on the date of the document. Currency rates are stored in the periodic information register “Currency Rates”.
A direct solution to this problem could be a query in a loop: obtaining all documents with their dates and currencies and, in the sample, accessing the virtual table with a slice of the latest “Currency Rates” register. But because a request in a loop is “bad”, let’s try to implement the task with one request.

Solution

To solve the problem, we will use the fact that the tables in the query can be connected not only for equality of fields.

SELECT Sales of Goods and Services. Link, Sales of Goods and Services. Currency, MAXIMUM (Currency Rates. Period) AS Period PLACE IN TPeriods Setting Rates FROM Document. Sales of Goods and Services HOW Sales of Goods and Services LEFT CONNECTION Register of Information. Currency Rates AS Currency Rates ON Sales of Goods servants.Currency = Currency Rates.Currency And Sales of GoodsServices.Date >= Currency Rates.Period GROUP Software Sales of Goods and Services. Link, Sales of Goods and Services. Currency; //////////////////////////////////////////////// ///////////////////////////// SELECT VTPeriodsSetting Rates.Link, VTPeriodsSetting Rates.Currency, RatesCurrency.Rate FROM VTPeriodsSetting Rates AS VTPeriodsSetting Rates LEFT CONNECTION RegisterInformation. Currency Rates AS Currency Rates ON VTPeriodsRate Settings.Period = Currency Rates.Period AND VTPeriodsRate Settings.Currency = Currency Rates.Currency

Procedure for request:

1. Obtaining the period for setting the exchange rate for each document. The documents are connected to the PHYSICAL table “Currency Rates”. Here you should pay attention to the connection conditions. The currencies must be equal, and the document date >= the period of the information register.
   As a result of such a connection, for each document, a set of rows will be obtained that satisfy the condition: all records of exchange rates for the currency of the document, established no later than the date of the document.
   The final step will be to group the rows to obtain the maximum rate period. As a result, for each document the required period for setting the exchange rate for the desired currency will be obtained (the maximum date for setting the exchange rate, but not more than the date of the document). The result is placed in the temporary table VTPeriodsSettingRates.
2. Getting a course. The temporary table VTPeriodsSetting Rates is connected to the PHYSICAL table “Currency Rates”. The connection occurs according to the Document Currency and the rate setting period defined in the second temporary table.

/
Implementation of data processing

Resolving totals for periodic information registers

Scope of application: managed application, mobile app, a common application.

1.1. For periodic information registers, it is recommended to allow totals if all of the following conditions are met:

• a large volume of data is expected in the register (for example, it is justified for a register with item prices; but it does not make sense for a register with exchange rates);
• the configuration provides frequency queries to slices of the latter at the current time and/or to slices of the former to obtain current data (i.e. when the period is not specified in the parameters of virtual tables Slice of the First And Slice of the Last);
• while the remaining conditions for virtual tables Slice of the First And Slice of the Last are set only to measurement values ​​(and separators in the mode Independently and jointly);
• register data access restrictions use only dimensions (and delimiters that are in mode Independently and jointly).

For a complete list of all conditions when queries use information register totals, seedocumentation for the 1C:Enterprise platform.

For example, if the configuration includes frequently executed queries to the register PricesNomenclatures to obtain current item prices:

SELECT Item. Article AS Article, PricesNomenclature. Price AS Price, . . . FROM Directory. Nomenclature AS Nomenclature LEFT CONNECTION Register of Information. PricesNomenclatures. SliceLast(, PriceView = &Type of Prices) HOW PricesNomenclatures Software PricesNomenclatures. Nomenclature = Nomenclature. Link . . .

then, subject to all other conditions listed above, setting the property Allow totals: slice of recent ones will significantly speed up the execution of such queries, due to the fact that the selection will be performed directly from additional tables, which store only the last values ​​(for cutting the last ones) and the first values ​​(for cutting the first ones).

1.2. In addition, you should consider alternative options to revise requests to the register so that these conditions are met.

For example, if in some cases the data in the register PricesNomenclatures are recorded on a future date, and when selecting goods to this register, a query is always executed for the current date (the date is explicitly specified in the virtual table parameter Slice of the Last), then the results will not speed up the execution of such queries. Since the results are built only for the first and latest entries register.

However, if, when opening the product selection form, you analyze whether there are registrars with a future date, and if there are none, you run another query for a slice of the latter without setting the date, then such a query will work faster.

2. In all other cases, totals should not be allowed for periodic information registers. First of all, if

• most often (always) queries are made to the virtual tables of the first/last periodic register of information for a specific period (for example, for the date of the document).
• in conditions for virtual tables Slice of the First And Slice of the Last most often (always) subqueries and joins are used (calls “through a dot” to fields related tables). For example, in this case:

3. There is no need to provide a separate mechanism for recalculating totals in the configuration, since the updating of the totals tables is performed automatically every time a set of records is written to the register.

: Slice of the First And Slice of the Last Let's consider working with these virtual tables using 1C. Much more often used Slice of the Last, so let's start with it.

A slice of the latest allows you to obtain the last record of the information register for a specified date in the context of measurements. For the last (first) slice table, it is possible to specify two parameters in parentheses, separated by commas. The first parameter contains the date on which the slice is made (if the parameter is not specified, the slice is made on the current date). The second parameter is a condition in the 1C query language and allows you to set various selections. As a rule, measurements are used in these selections. All this sounds quite vague, so it’s impossible to do without an example.
So let us have periodic register information Price which stores prices by product and supplier. The frequency of the register is day.

The register contains the following entries

To begin with, we will get a slice of the latter without using parameters by executing the following request:

SELECT PriceSliceLast.Period AS Period, PriceSliceLast.Product AS Product, PriceSliceLast.Supplier AS Supplier, PriceSliceLast.Amount AS Amount FROM Register Information.Price.SliceLast AS PriceSliceLast

Since the parameters are not specified, the slice is performed on the current date - 02/01/2017. As a result, we get the following table

Here we see that the combination of dimensions Product + Supplier is unique, i.e. For each combination of register dimensions, the record with the maximum date was taken, and the record date is less than or equal to the current date.

Let's say we need to do the same thing, but we want to get records with a date less than or equal to 01/15/2017. To do this, you need to change the row with the latest slice table in the request as follows

FROM RegisterInformation.Price.SliceLast(&CutDate,) AS PriceSliceLast

Before executing the request, of course, you need to pass a parameter to it &CutDate. Now the query result will look like this

And finally, imagine that we need to get a snapshot of the latest ones for the same date with the condition that we have the goods Pencil, and the supplier Stationery. To do this, specify the second parameter in the request

FROM RegisterInformation.Price.Last Cut(&CutDate, Product = &Product AND Supplier = &Supplier) AS PriceLast Cut

As a result, we get only one record

To avoid getting lost in all these parentheses and commas, it is better to use a query builder. I'll show you using the last request as an example.

After selecting a table with a slice of the latest ones in the query designer, you must click on the button Virtual Table Options and in the window that opens write

It is not difficult to guess that for the first slice the principle of operation is the same, except that the first record after the cut date is selected.

Let’s assume that we have received a task for which the customer wants to receive a report on the “Sales of goods and services” documents entered into the database, and for each document it is necessary to obtain the price from the information register “Item prices” for the date of the document. The report is written for the "Manufacturing Enterprise Management" configuration version 1.3.

The record from the document table is joined by a record from the virtual table "Nomenclature Prices. SliceLast" according to the corresponding conditions of the period, price type and item. The price type is obtained from the document details of the same name.

The problem has several ways to solve it. Let's consider two of them: a report on the access control system using two sets of data and obtaining all the necessary data in one request. Now, in order.

Using ACS

To implement such a report on the access control system, we will create two data sets in the data composition scheme. The first will receive a list of documents, the second will receive prices for document dates according to the selected item and price type. The connection between two sets of data is carried out by item, period (document date) and price type. From the first set it is necessary to transfer the parameters “Nomenclature”, “Price Type” and “Period” to the second set.

The first data set contains the following query:

The query selects the following fields from the document table and the “Products” tabular part: “Link”, “Date”, “Price Type”, “Nomenclature”. Now let's look at the second data set:

In the second set of data, you should pay attention to the parameters passed to the request. The list of selectable fields contains the parameters "Period, "Item" and "Price Type". The same parameters are used in the parameters of the virtual table "Nomenclature Prices. Slice of Latest". It is necessary to display these parameters in the list of selectable fields so that the values ​​of these parameters can be transferred from the first set to the second set. This is done using the connection settings between data sets in the “Data Set Connections” tab of the data composition scheme designer. Let’s move on to its consideration:

Setting up connections between data sources is carried out using the selection fields in the request. In the second data set, we added query parameters to the selection fields. When establishing relationships between sets, if you select a field in the "Parameter" column for the relationship, the value from the "Source Expression" column will be transferred to the "Destination Expression" and fill the selected parameter with the corresponding value.

Having configured the report structure and generated it, we will get the following result:

The report is working. Let's move on to consider options for obtaining the price for the document date in one request.

In one request

Let's create a new report with similar output fields and report settings. The only difference will be the data sources. This time, the report will contain a single data set in which all document products and their associated prices are selected in one query. The request text looks like this:

"SELECT | DocumentNomenclaturePeriod.Document, | DocumentNomenclaturePeriod.Document.Date AS Date, | DocumentNomenclaturePeriod.Document.PriceType AS TypePrice, | DocumentNomenclaturePeriod.Nomenclature, | PricesNomenclature.Price | FROM | (SELECT | Sales of GoodsServicesGoods. Link AS Document, | Sales of Goods ServicesProducts.Nomenclature AS Nomenclature, | MAXIMUM (Prices of Nomenclature. Period) AS Period | FROM | Document. Sales of Goods and Services. Goods AS Sales of Goods and Services Goods | LEFT CONNECTION Register of Information. Prices Nomenclature AS Prices Nomenclature | Software Sales of Goods and Services Goods. Nomenclature = Prices Nomenclature. Nomenclature | And Re implementation of Goods and Services Goods. Link. Date >= Prices Nomenclature. Period | And Sales of GoodsServicesGoods.Link.PriceType = PricesNomenclature.TypePrices | | GROUP BY | Sales of GoodsServicesGoods.Link, | Sales of GoodsServicesGoods.Nomenclature) AS DocumentNomenclaturePeriod | LEFT CONNECTION RegisterInformation.PricesNomenclature AS PricesNomenclatures | BY DocumentNomenclature tourPeriod.Nomenclature = PricesNomenclature.Nomenclature | And DocumentNomenclaturePeriod.Document.PriceType = PricesNomenclature.PriceType | And DocumentNomenclaturePeriod.Period = PricesNomenclature.Period"

The principle of the request is to obtain the nearest date of price change for each used item in the tabular part of the document, and then attach data from physical table"Item prices" by period, item and price type. At first glance, the request may seem complicated, but in fact its syntax is quite simple.

The execution result is similar to the result of the previous report (see screenshot above).

Draw conclusions

Which option for completing the task is better? It all depends on the specific conditions. In some situations, a report from two data sets will be faster than retrieving all the data for a report in one query. Within the scope of this article, we will not be able to touch upon performance issues for the considered methods of solving the problem.

It is also worth remembering that creating reports based on document tables is not recommended by the development methodology from the 1C company, since it is believed that the information in the documents may not be reliable. All reports must be based on registers.

  43
 NULL – missing values. Not to be confused with zero value! NULL is not a number, does not equal a space, an empty reference, or Undefined. NULL is a type-forming value, i.e. there is a type NULL and a single value of this type. NULL...   26
 To generate and execute queries to database tables in the 1C platform, it is used special object programming language Query. This object is created by calling the New Request construct. Convenient request...   18
 The article provides useful techniques when working with 1C v.8.2 queries, as well as information that is not so well known about the query language. I am not trying to give a complete description of the query language, but want to dwell only on...   13
 LIKE - Operator for checking a string for similarity to a pattern. Analogue of LIKE in SQL. The SIMILAR operator allows you to compare the value of the expression specified to the left of it with the pattern string specified to the right. The meaning of the expression...