Hi Borislav,

It doesn't look like it's going to work. If you look closely in my original message you'll notice, that in first case I'm using cCommission_Owner_UsGrLink_fk and in the second case I use cUsGrLink_fk [the name is historical and not really a good one, since in this case it should be cTemporary_Owner_UsGrLink_fk).

In other words, account may have a permanent owner (our employee that works this account) or it may have a temporary owner (hence I check the expiration date). Usually we assign accounts on a permanent basis, so there could be 0 cases with temp owner.

I'm trying to exclude even more rare possibility of having commission owner and temp owner in the same time (should not really happen, but may happen).

Now with this better explanation do you see a way of combining two selects in one and grouping on the fly?

Thanks in advance.


>If I read both selects correctly they are same with oine difference, in second one you have additional filtering in WHERE clause. If this is it, you can try:
>

>text TO lcSQLStr NOSHOW TEXTMERGE PRETEXT 7
>	SELECT COUNT(Trans.cTrans_pk) as iAccounts_Count, ;
>	       Trans_Employees_Queues.cCommission_Owner_UsGrLink_fk as cUsGrlink_pk, ;
>               SUM(Trans.yCurrent_balance_amount) as yCurrent_Balance_Amount ;
>
>               SUM(IIF((Trans_employees_queues.iTemporary_Assignment_Flag = 1 AND ;
>                        Trans_employees_queues.tStart_Date <= <<m.lcDateTime>> ;
>                        AND Trans_employees_queues.tExpire_Date >= <<m.lcDateTime>>), 1, 0) AS xxxxxx,;
>
>	FROM TRANS INNER JOIN Trans_employees_queues ;
>	ON Trans.cTrans_pk = Trans_employees_queues.cTrans_fk ;
>	INNER JOIN Employee_queue_schedules ;
>	ON Trans_employees_queues.cTrans_employees_queues_pk = ;
>	Employee_queue_schedules.cTrans_employees_queues_fk ;
>	WHERE <<m.lcFilter>> ;
>	GROUP BY 2
>ENDTEXT
>
>

>
>
>>Hi everybody,
>>
>>Bellow are two selects:
>>

>>text TO lcSQLStr NOSHOW TEXTMERGE PRETEXT 7
>>	SELECT COUNT(Trans.cTrans_pk) as iAccounts_Count, ;
>>	   Trans_Employees_Queues.cCommission_Owner_UsGrLink_fk as cUsGrlink_pk, ;
>>		SUM(Trans.yCurrent_balance_amount) as yCurrent_Balance_Amount ;
>>	FROM TRANS INNER JOIN Trans_employees_queues ;
>>	ON Trans.cTrans_pk = Trans_employees_queues.cTrans_fk ;
>>	INNER JOIN Employee_queue_schedules ;
>>	ON Trans_employees_queues.cTrans_employees_queues_pk = ;
>>	Employee_queue_schedules.cTrans_employees_queues_fk ;
>>	WHERE <<m.lcFilter>> ;
>>	GROUP BY 2
>>ENDTEXT
>>
>>text TO lcSQLStrTemp NOSHOW TEXTMERGE PRETEXT 7
>>	SELECT COUNT(Trans.cTrans_pk) as iAccount_Count, ;
>>	 Trans_Employees_Queues.cUsgrlink_fk as cUsGrlink_pk, ;
>>		SUM(Trans.yCurrent_balance_amount) as yCurrent_Balance_Amount ;
>>	FROM TRANS INNER JOIN Trans_employees_queues ;
>>	ON Trans.cTrans_pk = Trans_employees_queues.cTrans_fk ;
>>	INNER JOIN Employee_queue_schedules ;
>>	ON Trans_employees_queues.cTrans_employees_queues_pk = ;
>>	Employee_queue_schedules.cTrans_employees_queues_fk ;
>>	WHERE <<m.lcFilter>> AND ;
>>	(Trans_employees_queues.iTemporary_Assignment_Flag = 1 AND ;
>>   	 Trans_employees_queues.tStart_Date <= <<m.lcDateTime>> ;
>>	 AND Trans_employees_queues.tExpire_Date >= <<m.lcDateTime>>)
>>	 group by 2
>>ENDTEXT

>>
>>I'd like to combine them into one. In other words, if, for some reason, account has cCommission_Owner_UsGrLink_fk and also has cUsGrLink_fk filled it, I'd like to count it only once.
>>
>>
>>I'm not sure how to write this as one select. Thanks in advance for your suggestions.