[wellylug] [OT] PHP - Oracle - Query Help Desperatly Needed ;-)

[Geraint Jones]

I can think of nowhere else in NZ where I can pose this sort of question -
so here goes

I am aware this is not really the place for this - however its all running
on Linux/Oracle box ;-)

If you know of any better place to post this then please let me know -
thanks

I have the following query :

SELECT 
	TTCMCS045099.T\$DSCA,
      SUM(TCISLI245099.T\$DQUA),
      SUM(TCISLI245099.T\$AMTI),
      FLOOR(SUM(TCISLI245099.T\$AMTI)) -
FLOOR(SUM(TCISLI245099.T\$COPR\$1)),
      FLOOR(((FLOOR(SUM(TCISLI245099.T\$AMTI)) -
FLOOR(SUM(TCISLI245099.T\$COPR\$1))) / FLOOR(SUM(TCISLI245099.T\$AMTI))) *
100),
      TTCCOM110099.T\$CREG
      FROM TCISLI240099
      INNER JOIN TCISLI245099 ON TCISLI240099.T\$SLSO = TCISLI245099.T\$SLSO
      INNER JOIN TTCCOM100099 ON TCISLI240099.T\$OFBP = TTCCOM100099.T\$BPID
      INNER JOIN TTCCOM110099 ON TTCCOM110099.T\$OFBP = TTCCOM100099.T\$BPID
      INNER JOIN TTCMCS045099 ON TTCCOM110099.T\$CREG = TTCMCS045099.T\$CREG
      WHERE TCISLI240099.T\$CREP = '". $rep ."'
      GROUP BY TTCMCS045099.T\$DSCA,
               TTCCOM110099.T\$CREG
      ORDER BY FLOOR(SUM(TCISLI245099.T\$AMTI)) DESC

The tables in this query contain the following info

TTCMCS045099
T$DSCA   = Description (EG Auckland)
T$CREG   = Region

TCISLI245099
T$DQUA   = Delivered QTY
T$AMTI   = Sales Value
T$COPR$1 = Cost
T$ITEM   = Item

TCISLI240099
T$SLSO   = Sales Order Number
T$CREP   = Sales Rep
T$OFBP   = Customer

TTCCOM110099
T$BPID   = Customer
T$CREG   = Region

So running the above query I get the following sort of result

Region     |  Units  |  Value  |  Profit  | Profit %
----------------------------------------------------
Auckland   |  4578   |  $3480  |  $1280   | 25% 
Northland  |   578   |  $ 480  |  $ 280   | 25%
Wellington |   478   |  $ 380  |  $ 180   | 25%


Which is fantastic however and this is where the question really begins - I
need to add in last years figures

The last year figures are in a table with the following format

SALES_HISTORY
T$ITEM = Item
T$DATE = Sale Date
T$CREP = Sales Rep
T$DQUA = Delivered QTY
T$AMTI = Sales Value
T$BPID = Customer

I have tried joining this table in every fashion I can think of and every
time it distors all my figures because I believe oracle is looping on the
SALES_HISTORY table. So that all the above figures which are correct are
multiplied many many times

Im sure I have been nice and unclear

Cheers

GJ
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/x-pkcs7-signature
Size: 3642 bytes
Desc: not available
Url : http://lists.wellylug.org.nz/pipermail/wellylug/attachments/20050927/79455f5e/attachment.bin