parrotgurl
Programmer
I want to make sure I'm doing this query correctly.
There are two tables of interest, let's call them plans and transactions.
In the plans table, there are two columns that serve as a combined key - tan and pin.
Tan is an employer number and pin is an employee number. There can be employees that
are in more than one tan and obviously there can be employers that have more than one pin.
There are two other columns of interest, term1 and term2, both are termination dates.
The transactions table has a record for each account transaction for each tan/pin combo.
There is a column, punit, that can be tallied to show the account balance at any given time.
For example, if there were two records with the tan/pin combo of 1 2 - one with a punit of 100,
and the another with a punit of -100, the account balance for that combo would be zero.
Also, the transactions table has so many millions of records that it times out if I try to
do even a slightly complex query on it. So, I created a view called transactions_tanrange
that shows only columns of interest from records whose tan fall into the range I'm interested in.
Sooo... I want to find accounts that have a tan range from 11000 to 16000 AND who have a termination date (i.e. either term1 or term2 or both is filled in) AND who have a balance in their account
This is my query:
SELECT p.tan, p.pin, SUM(tt.punit) AS balance
FROM plans p, transactions_tanrange tt
WHERE p.pin = tt.pin
AND p.tan = tt.tan
AND ((p.term1 != 0) OR (p.term2 != 0))
GROUP BY p.tan, p.pin
HAVING SUM(tt.punit) > 0
ORDER BY p.tan, p.pin
It *seems* to return the right data, or at least not stunningly wrong.
I'm just paranoid that I'm missing something here.
Am I getting all the data I need back? Is there something I'm overlooking?
There are two tables of interest, let's call them plans and transactions.
In the plans table, there are two columns that serve as a combined key - tan and pin.
Tan is an employer number and pin is an employee number. There can be employees that
are in more than one tan and obviously there can be employers that have more than one pin.
There are two other columns of interest, term1 and term2, both are termination dates.
The transactions table has a record for each account transaction for each tan/pin combo.
There is a column, punit, that can be tallied to show the account balance at any given time.
For example, if there were two records with the tan/pin combo of 1 2 - one with a punit of 100,
and the another with a punit of -100, the account balance for that combo would be zero.
Also, the transactions table has so many millions of records that it times out if I try to
do even a slightly complex query on it. So, I created a view called transactions_tanrange
that shows only columns of interest from records whose tan fall into the range I'm interested in.
Sooo... I want to find accounts that have a tan range from 11000 to 16000 AND who have a termination date (i.e. either term1 or term2 or both is filled in) AND who have a balance in their account
This is my query:
SELECT p.tan, p.pin, SUM(tt.punit) AS balance
FROM plans p, transactions_tanrange tt
WHERE p.pin = tt.pin
AND p.tan = tt.tan
AND ((p.term1 != 0) OR (p.term2 != 0))
GROUP BY p.tan, p.pin
HAVING SUM(tt.punit) > 0
ORDER BY p.tan, p.pin
It *seems* to return the right data, or at least not stunningly wrong.
I'm just paranoid that I'm missing something here.
Am I getting all the data I need back? Is there something I'm overlooking?