Frequency distribution for documents

Common questions about 1C:Query language, Query builder tool and Data composition schema

#1
People who like this:0Yes/0No
Active user
Rating: 2
Joined: Jul 18, 2012
Company: Bristol IT Solutions, LLC

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:

Download chart.png (32.38 KB)
 
#2
People who like this:0Yes/0No
Active user
Rating: 2
Joined: Jul 18, 2012
Company: Bristol IT Solutions, LLC

I made it myself:

Code
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

Edited: Stephanie Elliott - Nov 14, 2012 04:23 PM
 
#3
People who like this:0Yes/0No
Active user
Rating: 7
Joined: Sep 26, 2012
Company: individual

I try it, and get the next (add attachments). And there are not second problem. I attach report.

> Data composition schema does not allow to select from all documents
Yes, it so. In such cases I make query for schema programmatically

 
#4
People who like this:0Yes/0No
Active user
Rating: 2
Joined: Jul 18, 2012
Company: Bristol IT Solutions, LLC

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'

 
#5
People who like this:0Yes/0No
Active user
Rating: 7
Joined: Sep 26, 2012
Company: individual

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)

 
#6
People who like this:0Yes/0No
Active user
Rating: 2
Joined: Jul 18, 2012
Company: Bristol IT Solutions, LLC

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?

 
#7
People who like this:1Yes/0No
Active user
Rating: 7
Joined: Sep 26, 2012
Company: individual

I made an example. See "Procedure OnComposeResult" in "Object module"
And also changed a query in "MainDataCompositionSchema"

Edited: ivan avdonin - Nov 15, 2012 11:58 AM
 
#8
People who like this:0Yes/0No
Active user
Rating: 2
Joined: Jul 18, 2012
Company: Bristol IT Solutions, LLC

Thank you, Ivan. I couldn't understand the comments, but it works like a charm!

By the way, I've got where's the problem: I have to click Download instead of clicking a file name.

 
#9
People who like this:0Yes/0No
Active user
Rating: 7
Joined: Sep 26, 2012
Company: individual

> it works like a charm!
Great! i'm happy) There is nothing in the comments, I forgot delete them.

 
Subscribe
Users browsing this topic (guests: 2, registered: 0, hidden: 0)
Be the first to know tips & tricks on business application development!

A confirmation e-mail has been sent to the e-mail address you provided .

Click the link in the e-mail to confirm and activate the subscription.