SQL Text for Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm hoping to perform the following with one SQL query

Group by ExpDate and Sum PdNet (sum in this example is 425.00

ExpDate PdNe
200404 100.0
200404 175.0
200404 150.0

And then allocate that sum to various CoCode based on percentages in tblAllo

For example, in tblAlloc I have

CoCode Allo
39 .6
52 .4

The final result should be to allocate the 425.00 sum 60% to CoCode 39 and 40% to CoCode 52. The first step (group and sum) is simple enough with the following SQL - I just can't get the allocation part to work. Thanks

PARAMETERS ExpDate Long
SELECT ExpDate, Sum(PdNet) AS PdNe
FROM tblHistGrantEx
GROUP BY ExpDat
HAVING (((ExpDate)=[ExpDate]))
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe this will work (untested):

PARAMETERS ExpDate Long;
SELECT H.ExpDate, Sum(H.PdNet) AS PdNet,
A.CoCode, Sum(H.PdNet) * A.Alloc As Allocation
FROM tblHistGrantExp As H, tblAlloc As A
WHERE H.ExpDate=[ExpDate]
GROUP BY H.ExpDate, A.CoCode

The result will be, what's know as, a Cartesian join: all values from
both tables in all possible combinations, that fit the criteria. E.g.:

Table1 Table2
1 A
2 B
3 C

Cartesian join result set:

1 A
1 B
1 C
2 A
2 B
2 C
3 A
3 B
3 C

HTH,

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)


-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIbc5oechKqOuFEgEQIKzACfVuFOXs4enjb65l+sT8DcxAVP7DMAoIk/
ljlT1bmMWzJihpJBZikYh7f0
=Faga
-----END PGP SIGNATURE-----
 
Back
Top