Date Range help

Question

Date Range help

mcieslinski

Old Hand

Points: 374
More actions
October 3, 2007 at 1:16 pm

#182308

I have a table of drug prices that only has an effective date rather than a effective and end date.
I have a client which supplied data in the following format
New TABLE Layout
NDC Char(11) - National Drug Code
Eff_date datetime - Date price became effective
Package_Price money - price for package
Unit_Price money - price for each unit
(the primary key is NDC, Eff_Date)
My current DB has a similar table and we use an effective and end dates. We match up pharmacy claims data and use the fill date for the prescription between the eff_date and end_date to pull correct
price for each row.
Existing TABLE Layout
NDC Char(11) - National Drug Code
Eff_date datetime - Date price became effective
End_date datetime - Date price is no longer effective
Package_Price money - price for package
Unit_Price money - price for each unit
(the primary key is NDC, Eff_Date, End_Date)
My question is how can I convert the new table data into the existing format or to put it another way, how can I determine the end date for each row.
Alternatively, how would I query using just the effective date
Thanks,
MC

Viewing 4 posts - 1 through 3 (of 3 total)

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

mcieslinski Old Hand Points: 374 More actions · Answer 1

I think I figured out a way to determine the end date for each corresponding effective date. See below. I would appreciate feedback from any experts out there.

select A.NDC,

A.Eff_date,

Min(B.Eff_Date)-1 "End_Date",

A. UnitPrice

from pricetest A

joinpricetest B on

A.NDC = B.NDC and

A.Eff_Date < B.Eff_date

Group by A.NDC,

A.Eff_date,

A. UnitPrice

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 2

Wouldn't the EndDate be a "+" 1 to make it a day larger than the EffectiveDate?

Also, I think that you may want to use the DATEADD() function because MS is changing the way they handle dates in 2008. It'll make upgrades easier.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

mcieslinski Old Hand Points: 374 More actions · Answer 3

Thanks Grant for the data add comment. The test data was in a text format so I didn't use this.

The reason I am subtracting a date is so we don't have overlap in the data; See below

Here is the original data for one drug:

NDC Eff_date UnitPrice

00002035102 1999110 20.3725

00002035102 2000051 20.3911

Here is the result of my query

NDC Eff_date End_Date UnitPrice

00002035102 19991102 20000511 0.3725

00002035102 20000512 20001120 0.3911

If I added a day to the end date it would over lap with the next row