Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need help with query... (97 SR-2)

    Hello All...
    I've been trying to figure this out on my own, but I have too much to get done before year end... <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    Here's what I need to do in my industry lingo...
    "We need to start booking the interest by product type... HOWEVER... If we are both Long and Short on the same bond we must net the interest first... "

    I have a table with over 8000 records... I have set up everything to make the "book by product type" easy......
    My problem is with the netting by condition...

    If DINT (Interest Amount earned) is less than 0, we are Short....
    If DINT is greater than 0, we are Long...
    I need to group by Currency within Security... Ex... F123456, C$... F123456,U$.... etc.... and then Net (Sum) the DINT field... but only where we are Long AND Short on the same security within the same currency....
    There may be 10 or 20 records for the same Security/Currency... I've played with record counts to get the single record securities and the multiple record securities... But I can't figure out how to combine the multiple lines while identifying what one to net...

    Security Currency DINT
    F123456 C$ -100,000.00
    F123456 C$ 25,000.00
    F123456 C$ 42,000.00 <--- Net before combining with the rest of the records

    F234567 U$ 5,000,000.00
    F234567 U$ 700,000.00 <---- Do not net the two amounts... Just leave as two separate records

    I won't confuse the situation by rambling on about what I've tried so far... Let's just say it didn't work! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Any ideas?? <img src=/S/help.gif border=0 alt=help width=23 height=15>

  2. #2
    Star Lounger
    Join Date
    Mar 2001
    Location
    Dudley, Midlands, England
    Posts
    55
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Need help with query... (97 SR-2)

    I think the key is to create two separate queries - one that groups the result and handles the long/short/netting situation and one that doesn't and only combine the results afterwards. I'm assuming that once you have to net the records - because there are both long and short records - you have to net all of them for that security/currency combination. If I've misunderstood then this won't work.

    In order to do that, you'll need some way of deciding which set particular records should fall into. You could try something like this:

    Create a "first level" query grouping on Security and Currency and then containing two calculated fields - ShortInt which Counts the number of records within the group where DINT is short and LongInt which counts the number of records within the group where DINT is long. You could do this a number of ways, but setting ShortInt to be Sum(DINT < 0) and LongInt to be Sum(DINT > 0) may well do the trick. Don't forget to cater for the possibility of a zero value in there too, unless those records are being filtered out already. This will result in both ShortInt and LongInt being either zero or negative, but it's really only a zero/non-zero situation you're looking for, aside from that, the values are immaterial.

    Next, create two "second level" queries. For the Short records join the "first level" query to the original data, but using only records where both ShortInt and LongInt are non zero. Group the result on Security and Currenct and sum the DINT values. For the Long records join the "first level" query to the original data, but using only records where either ShortInt is zero or LongInt is zero. Don't group the resulting data, just return Security, Currency and DINT.

    Create a final, union, query to return a composite recordset based on both "second level" queries and returning Security, Currency and DINT.

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need help with query... (97 SR-2)

    Thanks Simon... I'll give it a try...
    I kept getting sooooo close but not quite there... Wish me luck...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •