Check records from a history table

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)

Check records from a history table

Author

Topic

brettvasek
Starting Member

2 Posts

Posted - 2003-03-18 : 11:37:30

This query should be an easy one, but I can't seem to get it straight for the life of me.

I have a customer history table named "CustHistory" and it has quite a few columns, but this query only concerns 3 of them . Let's say it is defined as follows:

CREATE TABLE [CustHistory] (
[CustID] [int] NOT NULL ,
[EffectiveDate] [datetime] NOT NULL ,
[UpdateSource] [int] NOT NULL ,
CONSTRAINT [PK_CustHistory] PRIMARY KEY CLUSTERED
(
[CustID], [EffectiveDate]
)
) ON [PRIMARY]
GO

As modifications are made to our live Customer table, a history record is inserted to this CustHistory to store a copy of the record as it existed prior to the change. So it's very common to have multiple records for one CustID.

CustID is a counter-like integer value. It's unique in the Customer table, but is combined with EffectiveDate to enforce uniqueness in the CustHistory table.

The EffectiveDate column is the date and time the record was originally created in the Customer table.

The UpdateSource column is an integer value that ranges between 0, 1, and 2. Each of these values has a different meaning for my company, but in this query we are focused mostly on values of 1.

Let's say the above CustHistory table has 2 rows of data with the following values:

insert into CustHistory values (1518, '2003-01-23 09:21:24.357', 2)
insert into CustHistory values (1518, '2003-01-23 09:22:58.217', 1)

First the easy part: I need to be able to select the oldest row of data from the CustHistory table for a specific CustID. That's easy, right?

Now the tough part (for me anyways): I need to return the oldest row of data for the specific CustID only if it has an "UpdateSource" value = 1.

As you can see in the two rows of data above, the older of the two rows has an UpdateSource = 2. I don't want that record. And I don't want the other record with an UpdateSource = 1 because it's not the oldest. Both of these conditions have to be met: It has to be the oldest record for a given CustID and it has to have UpdateSource = 1.

Again, I don't think this should be that difficult, but I seem to have made it that way. Thanks in advance for any help.

Brett

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-18 : 11:56:23

select * from
custHistory CH
where
effectivedate = (SELECT Max(effectivedate) from CustHistory CH2 where CH.custid = ch2.custID and ch2.updatesource = 1)
and
updatesource = 1

- Jeff

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-03-18 : 12:58:07

How about

SELECT CustomerID, MIN(EffectiveDate) FROM CustomerHistory
WHERE UpdateSource = 1
GROUP BY CustomerID

OS

brettvasek
Starting Member

2 Posts

Posted - 2003-03-18 : 14:06:13

Thanks for the ideas guys. Here's what I ended up coding that seems to give me what I want:

select * from
CustHistory ch
where effectivedate = (SELECT MIN(effectivedate)
from CustHistory ch2
where ch.CustID = ch2.CustID)
and updatesource = 1

Just a few minor tweaks and everything seems to work fine. Thanks again for your help!

Brett

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources