Adding dates

If a Date type field is used to calculate totals, then you can supplement results with dates in a given period. This is implemented using the PERIODS keyword, followed by the period type in parentheses (one of SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, TENDAYS, HALFYEAR) and the beginning and the end dates of the period in question. If the beginning and ending dates are not specified, then the first and the last dates in the result will be used.

Example.

// We want to obtain sales quantity per customer per month
SELECT Recipient, BEGINOFPERIOD(Date, MONTH) AS Period, QUANTITY(Reference) AS QuantityPurchased
FROM Document.ExpInvoice
GROUP BY Recipient, BEGINOFPERIOD(Date, MONTH)
TOTALS SUM(QuantityPurchased) BY Period PERIODS(Month, DATETIME(2000, 07, 01), DATETIME(2000, 12, 01))

Query result:

Recipient	Period	QuantityPurchased
	07/01/2000 0:00:00	3
“Furniture” Store	07/01/2000 0:00:00	1
Urupinsk products	07/01/2000 0:00:00	1
“Master” Store	07/01/2000 0:00:00	1
	08/01/2000 0:00:00	1
“Giant” Store	08/01/2000 0:00:00	1
	09/01/2000 0:00:00	1
Urupinsk products	09/01/2000 0:00:00	1
	10/01/2000 0:00:00	0
	11/01/2000 0:00:00	0
	12/01/2000 0:00:00	0

This kind of result presentation is possible only if all the records of the Period used as dimension source at result tabbing by Period group.