SQL Select : Trouble returning correct recordset

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

General SQL Server Forums

Database Design and Application Architecture

SQL Select : Trouble returning correct recordset

Author

Topic

md57780
Starting Member

3 Posts

Posted - 2010-11-29 : 11:01:43

I have two tables that I need to run a query against. Table 1 holds a list of account. Table 2 holds the historical status for the accounts.

TABLE 1
AcctID : AutoNumber
AcctNumber : String

TABLE 2
StatusID : AutoNumber
AcctID : Long
DateEntered : Date
EnteredBy : String
Status : String

I need to return a recordset that shows all accouts from Table 1 that have a "CLOSED" status within a certain time frame and who closed the account from Table 2.

As an account can be reopened after it is closed, I need to make sure that I am not returning accounts that were closed and reopened. In the event an account is closed twice within the given time frame, only the most recent should be returned.

I have tried, but cannot get what it is that I need out.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-29 : 11:17:43

select t2.*
from Table2 t2
join
(select AcctID, DateEntered = max(DateEntered) from table2 group by accitID) t2a
on t2a.AcctID = t2.AcctID
and t2a.DateEntered = t2.DateEntered
where t2.statusID = @closedid
and t2.DateEntered between @d1 and @d2

This uses the last status - assuming that if there is any status entry after the date period then you don't want it.
i.e. if it is closed during the period then opened and closed afterwards then it is excluded.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

md57780
Starting Member

3 Posts

Posted - 2010-11-29 : 12:27:19

Thanks for the quick reply, nigel.

I used your query, and was able to generate a recordset. You were correct in that I do not need status changes outside the date range. I filtered specifically for a few records that I knew had been reopened or closed twice.

Using the date range #11/1/2010# - #12/1/2010#... Accounts that were closed once and reopened are appearing in the list as closed accounts. One specifically was closed on 11/12 and reopened on 11/17. This account should not be in the recordset.

Using the date range #9/1/2010# - #10/1/2010#... Accounts that were closed more than once are appearing multiple times in the recordset. One specifically was closed on 9/28, reopened later that same day, and closed again on 9/29. This account should be in the recordset once for 9/29 only.

Any thoughts?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-29 : 12:38:30

didn't realise that statusid was an id - should be status

select t2.*
from Table2 t2
join
(select AcctID, DateEntered = max(DateEntered) from table2 group by accitID) t2a
on t2a.AcctID = t2.AcctID
and t2a.DateEntered = t2.DateEntered
where t2.status = 'CLOSED'
and t2.DateEntered between @d1 and @d2

Don't see how accounts can appear multiple times.
t2a has single accounts with the dateentered of the last status change.
IS it possible to have multiple status changes for the same dateentered and account?
If so can you use the statusID to order the status changes?

select t2.*
from Table2 t2
join
(select AcctID, StatusID = max(STatusID) from table2 group by accitID) t2a
on t2a.AcctID = t2.AcctID
and t2a.StatusID = t2.StatusID
where t2.status = 'CLOSED'
and t2.DateEntered between @d1 and @d2

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

md57780
Starting Member

3 Posts

Posted - 2010-11-29 : 13:22:16

Again, your quick response is greatly appreciated.

I managed to get it to work. I ended up clearing it and re-entering the command. Perhaps an error on my part on the initial entry, but it is working as intended, with much thanks to you!

The DateEntered is a Time / Date stamp. While possible, it is highly unlikely that with the small number of people accessing this table (less than 10) there will ever be an occurance where two entries are entered by two people for the same account at exactly the same moment. And if in that moment the two users are entering two different status updates for the same account, I have bigger problems than a SELECT statement. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources