Include end date on a history record?

Question

Post reply

Include end date on a history record?

MattieNH

SSCertifiable

Points: 7463
More actions
February 29, 2008 at 7:27 am

#187143

I have to create a new table, StatuteHistory, that looks something like this:
StatuteID int,
WeightEffectiveDate datetime,
Weight int
With a primary key of StatuteID + WeightEffectiveDate.
I'll end up with data that looks like this:
101, '1900-1-1', 3
101, '2008-2-28', 2
102, '1900-1-1', 2
103, '1900-1-1', 1
103, '2008-1-1', 4
When my application creates a second history record for Statute, I could update a column called StopWeightEffectiveDate, which would be nothing more than one day prior to the new row's WeightEffectiveDate. I don't like that idea, because 1) I'm storing what is essentially the same data in two places, and 2) I need to make sure I keep it in sync.
On the other hand, that approach will make it much easier to write a report that looks like this:
StatuteID StartDate EndDate Weight
101 1/1/1900 2/27/2008 3
101 2/28/2008 2
102 1/1/1900 2
103 1/1/1900 12/31/2007 1
104 1/1/2008 4
or write a query that can use criteria like
where @WhateverDate is between WeightEffectiveDate and StopWeightEffectiveDate.
So should I go with the normalized design, and if so, how do I write my query so that each row can use the (WeightEffectiveDate - 1) from the next history row for that statute?
Thanks,
Mattie

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 1

So should I go with the normalized design,...

Heh... what would be the alternative?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 2

... and a trigger would take care of the date-1 thing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

You could also do something like this in SQL Server 2005/2008:

set nocount on

create table dbo.StatuteHistory (

StatuteId int,

WeightEffectiveDate datetime,

Weight int

);

insert into dbo.StatuteHistory (StatuteId, WeightEffectiveDate, Weight)

select 101, '1900-1-1', 3 union

select 101, '2008-2-28', 2 union

select 102, '1900-1-1', 2 union

select 103, '1900-1-1', 1 union

select 103, '2008-1-1', 4;

select * from dbo.StatuteHistory;

with StatuteHistory_cte (

RowNumber,

StatuteId,

WeightEffectiveDate,

Weight

) as (

select

Row_Number () over(order by StatuteId, WeightEffectiveDate) as RowNumber,

StatuteId,

WeightEffectiveDate,

Weight

from

dbo.StatuteHistory

)

select

sh1.*

from

StatuteHistory_cte sh1

left outer join StatuteHistory_cte sh2

on (sh1.StatuteId = sh2.StatuteId

and sh1.RowNumber = sh2.RowNumber -1)

where

sh2.RowNumber is null

drop table dbo.StatuteHistory;

set nocount off

Edit:

Please note, I forgot to include a primary key on StatuteId and WeightEffectiveDate. I am reworking it now.

😎

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Here is my updated code:

set nocount on

create table dbo.StatuteHistory (

StatuteId int,

WeightEffectiveDate datetime,

Weight int,

primary key (StatuteId, WeightEffectiveDate)

);

insert into dbo.StatuteHistory (StatuteId, WeightEffectiveDate, Weight)

select 101, '1900-1-1', 3 union

select 101, '2008-2-28', 2 union

select 102, '1900-1-1', 2 union

select 103, '1900-1-1', 1 union

select 103, '2008-1-1', 4;

--select * from dbo.StatuteHistory;

with StatuteHistory_cte (

RowNumber,

StatuteId,

WeightEffectiveDate,

Weight

) as (

select

Row_Number () over(order by StatuteId, WeightEffectiveDate) as RowNumber,

StatuteId,

WeightEffectiveDate,

Weight

from

dbo.StatuteHistory

)

select

sh1.*

from

StatuteHistory_cte sh1

left outer join StatuteHistory_cte sh2

on (sh1.StatuteId = sh2.StatuteId

and sh1.RowNumber = sh2.RowNumber -1)

where

sh2.RowNumber is null

drop table dbo.StatuteHistory;

set nocount off

😎

MattieNH SSCertifiable Points: 7463 More actions · Answer 5

Thanks Lynn and Jeff for responding.

Jeff, correct me if I've screwed up my terminology, but isn't the normalized design the one without a StopWeightEffectiveDate column, but a trigger would be updating that column?

Lynn, I can't use a CTE, because (and I forgot I wasn't posting in a version-specific forum) this will be written using SQL Server 2000.

So I guess the more precise question is, in SQL Server 2000, how do I write my query so that each row can determine the (WeightEffectiveDate - 1) from the next history row for that statute? I understand the join logic using the CTE with the temp table row ID, allowing you to reference the previous row, but is there a SS2K alternative?

Thanks,

Mattie

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Jeff,

Could you do it in SQL 2000 with a tally table and derived tables?

😎

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Mattie,

In SQL 2000, I'd probably add the WeightEndEffectiveDate field and use an insert trigger to update this field. The query then would select the records where the WeightEndEffectiveDate was null (or some suitable alternative (ie default) date like '1899-12-31')

😎

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Okay, I came up with a solution that should work in SQL 2000:

set nocount on

create table dbo.StatuteHistory (

StatuteId int,

WeightEffectiveDate datetime,

Weight int,

primary key (StatuteId, WeightEffectiveDate)

);

insert into dbo.StatuteHistory (StatuteId, WeightEffectiveDate, Weight)

select 101, '1900-1-1', 3 union

select 101, '2008-2-28', 2 union

select 102, '1900-1-1', 2 union

select 103, '1900-1-1', 1 union

select 103, '2008-1-1', 4;

select * from dbo.StatuteHistory;

select

sh1.*

from

dbo.StatuteHistory sh1

inner join (select sh2.StatuteId, max(sh2.WeightEffectiveDate) as WeightEffectiveDate from dbo.StatuteHistory sh2 group by sh2.StatuteId) sh3

on (sh1.StatuteId = sh3.StatuteId

and sh1.WeightEffectiveDate = sh3.WeightEffectiveDate)

order by

sh1.StatuteId

drop table dbo.StatuteHistory;

set nocount off

😎

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 9

MattieNH (2/29/2008)
Jeff, correct me if I've screwed up my terminology, but isn't the normalized design the one without a StopWeightEffectiveDate column, ...

I'll probably get some argument from others here, but I'd have to say "no"... the reason being is that the effective date of one row is the end date for the previous row. That means the column is serving two functions (start/end date) and is, therefor, not normalized.

Like I said... I'll probably get an argument or two out of that... 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 10

Lynn Pettis (2/29/2008)
Jeff,
Could you do it in SQL 2000 with a tally table and derived tables?
😎

Ummm... I don't think so... a running update would do it though... certainly it would be very fast...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Jeff, I'll give you an "It depends". Yes, it is the start date of the current record, and it with the StatuteId does uniquely identify a single record. How it is used, that's where semantics come into play.

😎

pmohan SSC Eights! Points: 941 More actions · Answer 12

You don't need to create a fourthcolumn to have the end date. I believe when you can be able to write optimised queries to pull records as you want you should not add a column which will occupy more space.

You can use the following queries for the scenario

of reports and query.

select statuteid,startdate,min(enddate)as enddate

,weight

from(

select s.StatuteID,s.WeightEffectiveDate as startdate, (s1.WeightEffectiveDate)-1 as enddate,s.Weight

from sample s left outer join sample s1

on s.StatuteID=s1.StatuteID

and

s.WeightEffectiveDate<s1.WeightEffectiveDate)A

group by statuteid,startdate,weight

declare @whateverdate datetime

set @whateverdate='01/25/2008'

select * from (select statuteid,startdate,min(enddate)as enddate

,weight

from(

select s.StatuteID,s.WeightEffectiveDate as startdate, (s1.WeightEffectiveDate)-1 as enddate,s.Weight

from sample s left outer join sample s1

on s.StatuteID=s1.StatuteID

and

s.WeightEffectiveDate<s1.WeightEffectiveDate)A

group by statuteid,startdate,weight

) B where @whateverdate between startdate and enddate

let me know if it works.

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 13

Lynn Pettis (2/29/2008)
Jeff, I'll give you an "It depends". Yes, it is the start date of the current record, and it with the StatuteId does uniquely identify a single record. How it is used, that's where semantics come into play.
😎

See? Told ya I wasn't "normal"... :alien:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Jeff Moden (2/29/2008)
Lynn Pettis (2/29/2008)
Jeff, I'll give you an "It depends". Yes, it is the start date of the current record, and it with the StatuteId does uniquely identify a single record. How it is used, that's where semantics come into play.
😎
See? Told ya I wasn't "normal"... :alien:

I don't think any of us are really "normal"... :alien:

😎