Complementing query result by days belonging to a specified period

Sometimes one needs a report where totals are calculated for each date in a specified time range with a given periodicity. For example, one might need to get turnovers by weeks irrespective of the actual presence of turnovers in specific weeks. The query language has a PERIODS keyword for this purpose. It is added to the TOTALS clause, after the Period field. The PERIODS keyword is followed by the period type in brackets (one of the following: Second, Minute, Hour, Day, TenDays, Week, Month, Quarter, HalfYear, and Year), and the start and end dates of the required period. If the start and end dates are not specified, the first and last dates of the result are used.

Example:

SELECT 
    ItemAccountingTurnovers.Period AS Period,
    ItemAccountingTurnovers.QuantityTurnover AS QuantityTurnover
FROM
    AccumulationRegister.ItemAccounting.Turnovers(, , Week, ) AS ItemAccountingTurnovers
ORDER BY 
    Period 
TOTALS SUM(QuantityTurnover) BY
    Period PERIODS(WEEK,,)

The result of this query is supplemented with records for the beginning of each week.

Note that since detailed records do not exist for supplemented periods in the query result, the supplemented records are only acquired from the selection if all the periods that participate in the query are retrieved during the iteration through the selection. This is done by specifying the third parameter of the Select() function of the query result.

Example:

PeriodSelection = Result.Select(QueryResultIteration.ByGroups, "Period", "All");
While PeriodSelection.Next() Do
    PeriodArea.Parameters.Fill(PeriodSelection);
    SpreadsheetDoc.Put(PeriodArea, PeriodSelection.Level());
EndDo;

Next page: Details on how to use the group inclusion operator