Updating Table
-
I need assistance updating a table that tracks Entries. The Table schema is as follows:
Create Table EntryVal
ID Field Char(9),
Begindate Datetime,
Enddate Datetime,
HType Char(2)
Insert into EntryVal
Values('111111111', '05/15/03',Null,'BS')
Insert into EntryVal
Values('111111111', '05/30/03',Null,'BO')
Insert into EntryVal
Values('222222222','02/15/03', Null,'BA')
Insert into EntryVal
Values('222222222', '06/18/03',Null,'BS')
Insert into EntryVal
Values('333333333', '07/01/03',Null, 'BS')
Insert into EntryVal
Values('333333333', '07/15/03',Null, 'BJ')
Insert into EntryVal
Values('333333333', '07/18/03',Null, 'BO')
Insert into EntryVal
Values('444444444', '08/01/03',Null, 'B0')
Insert into EntryVal
Values('444444444', '08/05/03',Null, 'BS')
My task is to identify (With records grouped by ID And ordered by begindate Ascending) those records where the Htype Changed From X to 'BO'. X Being any other HType e.g. 'BA','BS' or BJ. Also Only Identify The changes where the Last entry- Max(begindate)Htype is 'BO'. E.G. The Entries i would want to identify above would be the entries for ID '111111111' and '333333333'. However the First Entry For ID '333333333',
Insert into EntryVal
Values('333333333', '07/01/03',Null, 'BS')
does not need to be identified, due to the fact That the following entry;
Insert into EntryVal
Values('333333333', '07/15/03',Null, 'BJ')does not have HType being 'BO' And the entry also is not the final Entry For ID '111111111' in the tracking table.
Final Entry is:
Insert into EntryVal
Values('333333333', '07/18/03',Null, 'BO')
I hope this is not to confusing, I tried to explain the scenario to the fullest.
Anthony Malone
Anthony Malone -
How about...
Select LatestNonBOs.ID_Field, LatestNonBOs.MaxBeginDate
From (Select ID_Field, Max(BeginDate) as MaxBeginDate
from EntryVal
Where HType <> 'BO'
Group by ID_Field) LatestNonBOs
Where Exists (
Select * From EntryVal ChangedToBOs
Where ChangedToBOs.ID_Field = LatestNonBOs.ID_Field
and ChangedToBOs.BeginDate >= LatestNonBOs.MaxBeginDate
and HType = 'BO')You may want to change the "<=" and also add an ORDER BY to your liking.
Once you understand the BITs, all the pieces come together
-
Is this more or less what you had in mind?
select EntryVal.*
from ( select ID_Field, max(Begindate) MaxBegin,
max(case when HType = 'BO' then Begindate else NULL end) MaxBeginBO
from EntryVal
group by ID_Field
having max(Begindate) =
max(case when HType = 'BO' then Begindate else NULL end)
) as MaxBO
join EntryVal
on (MaxBO.ID_Field = EntryVal.ID_Field)
order by EntryVal.Begindate
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply