Need help with query… (97 SR-2)

Topic

New Reply

Hello All…
I’ve been trying to figure this out on my own, but I have too much to get done before year end…

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!
Any ideas??

Reply | Quote

Replies

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) 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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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) 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.

Reply | Quote