Take Sum from Group 2 and put Maximum in Group 1

[-cush-]

I have a Top N sort of Group 2 that is a summation. I would like to take that top value and do a Top N Sort of Group 1. I would at least like to filter group 1 based off of the maximum sum from group 2.

I have the following function that will not work:

Maximum(Sum ({connect1.TalkTimeSeconds}, {Group2Field}),{Group1Filed})

It won't function because the sum has to be calculated at runtime. Does someone have a workaround?

 

[lbass]

For the purpose of the group 1 sort, you can create a SQL expression {%grp2sum}:

(
Select sum(a.“talktimeseconds”)
From “connect1” a
Where a.”Group2Field”=“connect1”.”Group2Field”
)

This is just a guess, since you haven’t used actual table names, so I don’t know whether you are using different tables for the fields you are grouping on. Whether this syntax is correct also depends upon your version of CR. Try it with or without the “a” within the sum(). Note that the punctuation will be specific to your database, and you can add the fields from your field list within the SQL expression editor to get it right. Don’t just copy my example, since the quotes are from an iPad character set that CR doesn’t recognize.

Once you have successfully compiled, the SQL expression can be treated like a field, and you can insert a maximum on it and then do a group sort on group 1.

For more specific help, please provide more specific information.

-LB

 

[-cush-]

Thank you for the response lbass. The report Groups individuals phones first and shows names of the phone owner for group 1. Then Group 2 groups by external phone numbers and sums the total time for the top 5 external numbers. The report filters for the last full month. What I am looking to do is sort Top N for group 1 based off of the top sum in group 2.

Here is my original SQL Query that gets sent from Crystal 2013 to mySQL via ODBC 5.3 ANSI Driver.

SELECT `connect2`.`PartyID`, `connect1`.`ConnectTime`, `connect1`.`TalkTimeSeconds`, `connect2`.`ConnectReason`, `connect1`.`PortName`, `connect1`.`PartyID`, `connectreason1`.`Name`
FROM (`shorewarecdr`.`connect` `connect1` INNER JOIN `shorewarecdr`.`connect` `connect2` ON `connect1`.`CallTableID`=`connect2`.`CallTableID`) INNER JOIN `shorewarecdr`.`connectreason` `connectreason1` ON `connect1`.`ConnectReason`=`connectreason1`.`ConnectReason`
WHERE (`connect1`.`PartyID`='3210' OR `connect1`.`PartyID`='3242' OR `connect1`.`PartyID`='3253' OR `connect1`.`PartyID`='3275' OR `connect1`.`PartyID`='3284' OR `connect1`.`PartyID`='3302' OR `connect1`.`PartyID`='3354' OR `connect1`.`PartyID`='3385' OR `connect1`.`PartyID`='3422' OR `connect1`.`PartyID`='3488' OR `connect1`.`PartyID`='3495' OR `connect1`.`PartyID`='3508' OR `connect1`.`PartyID`='3574') AND `connect2`.`ConnectReason`<>27 AND (`connect1`.`ConnectTime`>={ts '2018-03-01 00:00:00'} AND `connect1`.`ConnectTime`<{ts '2018-04-01 00:00:00'})
ORDER BY `connect1`.`PortName`

Group 1 is `connect1`.`PortName` and Group 2 is a function that takes the right 10 characters of a phone number and inserts hyphens where appropriate.

Other than a Top N for Group 1 the report looks great. See Attached.

 

[lbass]

Please show the content of the Group2 formula. Also identify your CR version.

-LB

 

[-cush-]

I am using Crystal 2008. (I erroneously said 2013 before, but I use that elsewhere)

The definition of @LastTen is:

stringVar s := right({connect2.PartyID},10);
left(s,3) + "-" + mid(s,4,3) + "-" + right(s,4)

I believe the equivalent in mySQL is

left(right(C2.PartyID,10),3) + "-" + SUBSTR(right(C2.PartyID,10),4,3) + "-" + right(C2.PartyID,4)

 

[lbass]

Can you show examples of PartyID before manipulated in the formula? I’m wondering whether the leading digits are irrelevant for the purposes of the group that needs to be created within the SQL expression. Can there be instances where the same last 10 digits have different leading digits? For example, is the following possible?

7891235551212
3451235551212

If the leading digits are irrelevant, try the following:

I think later versions of CR might support using the alias table within the sum(), so try this:

(
Select sum(a.`TalkTimeSeconds`)
From `sharewarecdr`.`connect` a, `sharewarecdr`.`connect` b
Where a.`CallTableID`=b.`CallTableID` and
b.`PartyID`=`sharewarecdr`.`connect`.`PartyID`
)

I’m not sure this is the right syntax, so you will have to try it to see if it compiles. Not sure whether you can use the table names connect without the `sharewarecdr` or not, so that is one variation to try. If you want more help, please show examples of what you try that did not work.

-LB

 

[-cush-]

They leading digits can be different. Primarily based on whether it is an incoming our outgoing call. Outgoing calls record the 9 required to dial out. But I will try the code when I get back to my desk Monday.