increasing granularity - SQL Server Forums

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)

increasing granularity

Author

Topic

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2001-09-06 : 10:53:15

I have two tables and I want to return a set of data from both the tables that look like this:
Account table

Account char(10)
Account_date_time datetime
account_set char(2)
account_status char

Customer table

Account char(10)
Account_date_time datetime
account_type char (2)

The data looks like this


Account table

account            account_date_time     account set  account status

1111111111         2001-08-15              1           A
1111111111         2001-07-15              1           A
1111111111         2001-09-15              2           A
1111111111         2001-06-15              1           C
1111111111         2001-05-15              1           A

Customer table

account            account_date_time     account type

1111111111         2001-08-15              T           
1111111111         2001-07-15              T           
1111111111         2001-09-15              T           
1111111111         2001-06-15              T           
1111111111         2001-05-15              S

Account and account_date_time are unique keys and have a one to one relationship between the two tables. I want to get all fields from both tables for only the records with the highest account_data_time and an account status of A for each distinct account set. Grouping by account set is a new condition. I am currently using the following slelct statement to fill a cursor.

select A.ACCOUNT_NUMBER, A.ACCOUNT_DATE_TIME, C.ACCOUNT_TYPE, A.ACCOUNT_SET
from ACCOUNT A
join CUSTOMER C on A.ACCOUNT_NUMBER = C.ACCOUNT_NUMBER and
C.ACCOUNT_DATE_TIME = A.ACCOUNT_DATE_TIME
where
A.ACCOUNT_DATE_TIME = (select max(ACCOUNT_DATE_TIME) from ACCOUNT where ACCOUNT_NUMBER = C.ACCOUNT_NUMBER)
and C.ACCOUNT_DATE_TIME is not null

The data I'm getting back now is


account            account_date_time     account type  account set

1111111111         2001-09-15              T             2

What I want is this


account            account_date_time     account type   account set

1111111111         2001-08-15              T                1
1111111111         2001-09-15              T                2

I'm just not getting it today, any thoughts?

Cat

Edited by - cat_jesus on 09/06/2001 10:55:45

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources