IBO is used on the server side..

But on the client side, I use middle ware client components..
This is a remote app..

So if thats the case.. would the stored proc be the best?

I dont understand why the stored proc needs to be in loop?

Do you have time today, if I could pay you to write the code?

----- Original Message -----
From: Svein Erling Tysvær
To: IBObjects@yahoogroups.com
Sent: Thursday, April 08, 2010 8:41 AM
Subject: RE: [IBO] Stuck on a query..



In a stored procedure, this is simple. In a query, well, with Firebird 2.1 it is not difficult:

With SumA(Rec_ID, FirstSum) as
(select stores_rec_id, Sum(OHA.Amount) from Ord_Head OHA
where OHA.delivdate between :startdateA and :EnddateA
and OHA.void <> 'Y'
group by 1),
SumB(Rec_ID, SecondSum) as
(select stores_rec_id, Sum(OHB.Amount) from Ord_Head OHB
where OHB.delivdate between :startdateB and :EnddateB
and OHB.void <> 'Y'
group by 1)

Select S.name,S.Deptid,S.storeid,S.city,S.state,S.zone,S.County, SA.FirstSum, SB.SecondSum, (SB.SecondSum - SA.FirstSum) * 100.00 / SA.FirstSum) as DiffAmt
From Stores S
join customers c on S.cust_rec_id=C.rec_id
Left join SumA SA on s.Rec_ID = SA.Rec_ID
Left join SumB SB on s.Rec_ID = SB.Rec_ID

You may have to twiggle a bit to cater for situations where OHA sums up to 0. I changed the multiplication so that it will create a bit less precision errors - add more zeroes if it is still too approximate.

With earlier versions (I don't know about Firebird 2.0), you would have to repeat your subselects - it looks ugly, but isn't all too much more time consuming:

Select S.name,S.Deptid,S.storeid,S.city,S.state,S.zone,S.County,
(select Sum(OHA.Amount) from Ord_Head OHA where
OHA.stores_rec_id=S.Rec_Id
and OHA.delivdate between :startdateA and :EnddateA
and OHA.void <> 'Y' As FirstSum,

(select Sum(OHB.Amount) from Ord_Head OHB where
OHB.stores_rec_id=S.Rec_Id
and OHB.delivdate between :startdateB and :EnddateB
and OHB.void <> 'Y'
As SecondSum,

((select Sum(OHA.Amount) from Ord_Head OHA where
OHA.stores_rec_id=S.Rec_Id
and OHA.delivdate between :startdateA and:EnddateA
and OHA.void <> 'Y') -
(select Sum(OHB.Amount) from Ord_Head OHB where
OHB.stores_rec_id=S.Rec_Id
and OHB.delivdate between :startdateB and :EnddateB
and OHB.void <> 'Y'))*100.00/
(select Sum(OHA.Amount) from Ord_Head OHA where
OHA.stores_rec_id=S.Rec_Id
and OHA.delivdate between :startdateA and:EnddateA
and OHA.void <> 'Y') as DiffAmt
From Stores S
join customers c on S.cust_rec_id=C.rec_id

By the way, the correct place to ask this question is firebird-support.

HTH,
Set

-----Original Message-----
From: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com] On Behalf Of Gordon Hamm
Sent: 8. april 2010 16:14
To: IBObjects@yahoogroups.com
Subject: [IBO] Stuck on a query..

Hi,
I know this isnt the right group, but Ive asked other places and am really in a bind.. Im desperate.

Select S.name,S.Deptid,S.storeid,S.city,S.state,S.zone,S.County,
(select Sum(OHA.Amount) from Ord_Head OHA where
OHA.stores_rec_id=S.Rec_Id
and (OHA.delivdate >= :startdateA)
and (OHA.Delivdate <=:EnddateA)
and OHA.void <> 'Y' As FirstSum,

(select Sum(OHB.Amount) from Ord_Head OHB where
OHB.stores_rec_id=S.Rec_Id
and (OHB.delivdate >= :startdateB)
and (OHB.Delivdate <= :EnddateB)
and OHB.void <> 'Y'
As SecondSum

From Stores S,customers c
Where S.cust_rec_id=C.rec_id

This code works fine..
But I want to add this code to get a difference of the 2 amounts as percent of the difference ...

// I want a new column as Diff Amount
(( SecondSum - FirstSum ) / FirstSum) * 100 as DiffAmt

It complains that "FirstSum" and "SecondSum" are unknown..

This giving me the sales percent difference between the 2 subqueries.. I
need this becuase I need to sort the result set by percent difference..
This query is used to give Year to Year sales differences..

Is there some way of doing this?




[Non-text portions of this message have been removed]