D7 Pro DBISAM 4.25

A doctor refers a patient for special services. The value of this referral
is easy to calculate as I just sum the treatment costs but I just want the
referral number to be 1 per unique client. For example, Dr. Smith refers
Mary Adams and David Jones for treatment. The number of referrals from Dr.
Smith should read 2 and the value of these referrals should be the total
value of every treatment they received. What I currently use follows:

"SELECT ReferredBy, sum(TreatmentCost) as TotalCost, count(TreatmentCost) as
NumberReferrals FROM
FINANCIAL group by ReferredBy order by ReferredBy"

A unique client ID is available, but not used above. In pseudo code it would
be something like:

"SELECT ReferredBy, sum(TreatmentCost) as TotalCost, count(unique(clientid))
as NumberReferrals FROM
FINANCIAL group by ReferredBy order by ReferredBy"

Any help appreciated, thanks, Sean

"Sean McDermott" <Sean@HorizonCanada.com> wrote in message
news:20665E8A-F373-47FF-9A9D-8FC6679AB654@news.elevatesoft.com...
>
> "SELECT ReferredBy, sum(TreatmentCost) as TotalCost, count(TreatmentCost)
> as NumberReferrals
Into Memory\temp3
FROM
> FINANCIAL group by ReferredBy order by ReferredBy"
>
SELECT DISTINCT ClientID, ReferredBy
INTO MEMORY\TEMP1
FROM FINANCIAL;
SELECT COUNT(*) NumberReferrals, ReferredBy
INTO MEMORY\TEMP2
FROM MEMORY\TEMP1
GROUP BY ReferredBy;

If tables can be big, add an index by ReferredBy on temp2 and temp3 (see
question for Tim below). Then do your final select joining temp2 and temp3
on ReferredBy.

QUESTION FOR TIM. Something came up re a question a few days ago. In DBISAM
V4, an "Order by" generates an index. Will that index be used to optimize a
subsequent query? IOW, in the example above, would doing an order by
ReferredBy for temp2 and temp3 automatically optimize the last JOIN?

Robert

Thanks Robert...
"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message
news:2E58B1EC-5325-48FE-80B3-88FC0CCAEA91@news.elevatesoft.com...
>
> "Sean McDermott" <Sean@HorizonCanada.com> wrote in message
> news:20665E8A-F373-47FF-9A9D-8FC6679AB654@news.elevatesoft.com...
>>
>> "SELECT ReferredBy, sum(TreatmentCost) as TotalCost, count(TreatmentCost)
>> as NumberReferrals
> Into Memory\temp3
> FROM
>> FINANCIAL group by ReferredBy order by ReferredBy"
>>
> SELECT DISTINCT ClientID, ReferredBy
> INTO MEMORY\TEMP1
> FROM FINANCIAL;
> SELECT COUNT(*) NumberReferrals, ReferredBy
> INTO MEMORY\TEMP2
> FROM MEMORY\TEMP1
> GROUP BY ReferredBy;
>
> If tables can be big, add an index by ReferredBy on temp2 and temp3 (see
> question for Tim below). Then do your final select joining temp2 and temp3
> on ReferredBy.
>
> QUESTION FOR TIM. Something came up re a question a few days ago. In
> DBISAM V4, an "Order by" generates an index. Will that index be used to
> optimize a subsequent query? IOW, in the example above, would doing an
> order by ReferredBy for temp2 and temp3 automatically optimize the last
> JOIN?
>
> Robert
>
>

Sean


Someone may come up with a more elegant way but try this script


SELECT ReferredBy, CLIENTID, sum(TreatmentCost) as TotalCost, 1 as NumberReferrals
into "memory\temp"
FROM
new group by clientid, ReferredBy order by ReferredBy;
SELECT ReferredBy, sum(totalCost) as TotalCost, sum(NumberReferrals) as
NumberReferrals FROM
"memory\temp" group by ReferredBy order by ReferredBy;

Roy Lambert [Team Elevate]

Robert

What happens to the costs? I thought initially you'd done something really nifty, but as I try to work it though I can't figure out the treatment costs are taken account of.


Roy Lambert

Robert,

<< QUESTION FOR TIM. Something came up re a question a few days ago. In
DBISAM V4, an "Order by" generates an index. Will that index be used to
optimize a subsequent query? IOW, in the example above, would doing an order
by ReferredBy for temp2 and temp3 automatically optimize the last JOIN? >>

Yes, any available index is used for optimization purposes, even if it's
internal.  The same holds true for ElevateDB.

--
Tim Young
Elevate Software
www.elevatesoft.com

That's what I like, a complete solution, thanks all, Sean

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:B8918232-22B1-43A4-B31D-C8989EA1AA27@news.elevatesoft.com...
> Sean
>
>
> Someone may come up with a more elegant way but try this script
>
>
> SELECT ReferredBy, CLIENTID, sum(TreatmentCost) as TotalCost, 1 as
> NumberReferrals
> into "memory\temp"
> FROM
> new group by clientid, ReferredBy order by ReferredBy;
> SELECT ReferredBy, sum(totalCost) as TotalCost, sum(NumberReferrals) as
> NumberReferrals FROM
> "memory\temp" group by ReferredBy order by ReferredBy;
>
> Roy Lambert [Team Elevate]
>

Tim

>Yes, any available index is used for optimization purposes, even if it's
>internal. The same holds true for ElevateDB.

How long do they last for? Are they available between sessions/processes or what?

Roy Lambert

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:5F7E4D0F-90CA-497E-85D9-34E3990D063F@news.elevatesoft.com...
> Robert
>
> What happens to the costs? I thought initially you'd done something really
> nifty, but as I try to work it though I can't figure out the treatment
> costs are taken account of.
>

You keep the original query, except that you post it to a temporary table
(temp3). Then you figure the number of clients using the other script
(temp2), and then you JOIN both tables I did not write the last query, but
it would be something like

SELECT * FROM MEMORY\TEMP3 M3
JOIN MEMORY\TEMP2 M2 ON M2.REFERREDBY = M3.REFERREDBY

Robert

Robert

Ahh - makes sense now. Thanks.


Roy Lambert