Am I doing this query correctly? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Am I doing this query correctly?

Author

Topic

parrotgirl
Starting Member

1 Post

Posted - 2002-11-18 : 10:52:17

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

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

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 falls into the range I'm interested in.

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?

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-18 : 11:33:35

Looks okay to me. You might want to consider re-writing it using ANSI style joins, as the style you're using there is being phased out.

SELECT p.tan, p.pin, SUM(tt.punit) AS balance
FROM plans p
INNER JOIN
transactions_tanrange tt
on tt.pin = p.pin
and tt.tan = p.tan
where ((p.term1 != 0) OR (p.term2 != 0))
GROUP BY p.tan, p.pin
HAVING SUM(tt.punit) > 0
ORDER BY p.tan, p.pin

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-11-18 : 11:54:27

using

WHERE p.pin = tt.pin

is ANSI sql compliant and will not become obsolete.

It is queries such as

WHERE p.pin =* tt.pin

that will not be supported idefinetly.

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-18 : 11:57:55

Oh. I didn't know that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources