SCD2 and RowStartDate, RowEndDate stamping?

View previous topic View next topic Go down

SCD2 and RowStartDate, RowEndDate stamping?

Post  pzajkowski on Mon Mar 29, 2010 2:43 pm

When maintaining a SCD2, Kimball recommends that the expiration (expiry) date (aka RowEndDate) of a given row should equal the RowStartDate of the newly inserted row.

I'm confused by this idea. For example, if someone's region has changed from "North" to "South" , effective April 1, why would the RowEndDate for "North" be April 1 (per Kimball) rather than March 31?

If the RowEndDate for North is equal to the RowStartDate for South, won't the results be incorrect when I execute a query that counts people per region on April 1, especially if the search criteria is @DateParameter Between RowStartDate and RowEndDate?

--Pete

pzajkowski

Posts : 31
Join date : 2009-08-10

View user profile

Back to top Go down

Re: SCD2 and RowStartDate, RowEndDate stamping?

Post  ngalemmo on Mon Mar 29, 2010 3:37 pm

You can do it either way... obviously, if you use BETWEEN, the end date needs to be the day prior to the new effective date.

My guess is it is a holdover from the first edition. At the time, BETWEEN was not fully implemented in a lot of SQLs. In some DBs it wasn't supported, in others the from and to values could not be columns and so on.

But it also depends if you are using a full timestamp. In that case, you would not have a problem unless your date parameter happens to match the exact time in the database.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: SCD2 and RowStartDate, RowEndDate stamping?

Post  pzajkowski on Tue Mar 30, 2010 11:05 am

Thanks for the feedback. I feel better having some confirmation that it's OK to have End & Start dates off by 1 rather than the same. Querying will be much simpler.

pzajkowski

Posts : 31
Join date : 2009-08-10

View user profile

Back to top Go down

Re: SCD2 and RowStartDate, RowEndDate stamping?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum