Is my query right?

[parrotgurl]

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?

 

[tlbroadbent]

IS it possible that the termination dates could be NULL? If so, you should test for that condition also.

AND (Isnull(p.term1,0) != 0 OR Isnull(p.term2,0) != 0)
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[parrotgurl]

Hi,

No, the term1 and term2 columns are set in the database not to allow nulls. If there's no term1 or term2 date for a given record, then there's just a 0 in that field (it's actually not a date datatype, just a number like 2002123, a combination of year and julian day of the year).

Thanks.

 

[tlbroadbent]

The query should work. I would recommend moving to ANSI standard syntax for the JOIN.

SELECT p.tan, p.pin, SUM(tt.punit) AS balance
FROM plans p
INNER JOIN transactions_tanrange tt
ON p.pin = tt.pin
WHERE 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

This won't make the query more efficient or change the result. However, it will make the query compliant with the standard. Microsoft has indicated that the old style joins may be eliminated in future versions of SQL Server. In addition, there are known defects in SQL 2000 that cause incorrect results in queries using old style syntax for outer joins. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.