I need to understand the load of database to see bottlenecks. For that I want to create a frequency distribution by hours for all documents. Can someone help with that? The problem is that the Data composition schema does not allow to sel ect from all documents, but from a specific one only. Here is my query:
Code
SEL ECT
HOUR(CustomerOrder.Date) AS Hour,
COUNT(CustomerOrder.Ref) AS Count
FR OM
Document.CustomerOrder AS CustomerOrder
GROUP BY
HOUR(CustomerOrder.Date)
And the second problem is that hours are grouped, but not distributed fr om 1 to 24:
SEL ECT
0 AS Hour
INTO TTHours
UNI ON
SEL ECT
1
UNI ON
SEL ECT
2
UNI ON
SEL ECT
3
UNI ON
SEL ECT
4
UNI ON
SEL ECT
5
UNI ON
SEL ECT
6
UNI ON
SEL ECT
7
UNI ON
SEL ECT
8
UNI ON
SEL ECT
9
UNI ON
SEL ECT
10
UNI ON
SEL ECT
11
UNI ON
SEL ECT
12
UNI ON
SEL ECT
13
UNI ON
SEL ECT
14
UNI ON
SEL ECT
15
UNI ON
SEL ECT
16
UNI ON
SEL ECT
17
UNI ON
SEL ECT
18
UNI ON
SEL ECT
19
UNI ON
SEL ECT
20
UNI ON
SEL ECT
21
UNI ON
SEL ECT
22
UNI ON
SEL ECT
23
;
///
SEL ECT
Hours.Hour,
SUM(ISNULL(Documents.Count, 0)) AS Count
FROM
TTHours AS Hours
LEFT JOIN (SELECT
AllDocuments.Hour AS Hour,
AllDocuments.Count AS Count
FR OM
(SELECT
HOUR(CustomerOrder.Date) AS Hour,
COUNT(CustomerOrder.Ref) AS Count
FR OM
Document.CustomerOrder AS CustomerOrder
GROUP BY
HOUR(CustomerOrder.Date)
UNION
SEL ECT
HOUR(Invoice.Date),
COUNT(Invoice.Ref)
FR OM
Document.Invoice AS Invoice
GROUP BY
HOUR(Invoice.Date)
UNION
...
) AS AllDocuments) AS Documents
ON (Documents.Hour = Hours.Hour)
GROUP BY
Hours.Hour
Hi, Ivan! I tried to open your external report, but got the following error: Error performing file operation 'C:\Users\Stephanie\Desktop\ExternalReport1.erf' because: Invalid data storage format 'file://C:/Users/Stephanie/Desktop/ExternalReport1.erf'
Stephanie, Are you sure that file download correctly? Try open file in Designer. May be we have different versions of platform. I check in version: 1C:Enterprise 8.2 (8.2.16.368)
Yes, redownloaded it using FF, it works. And I see no difference from what I did. The second problem I have solved myself using the TTHours with 0-23 values for hours. You said you can solve the first problem: to generate a query programmatically for Data composition schema. Can you show an example?