Update: Adam Machanic's "T-SQL Tuesday #001 (Date/Time Tricks): The Roundup"
My contribution to Adam Machanic's T-SQL Tuesday Blog Party Inaugural Edition.
No. This is not about going out with a pretty girl or cute guy for dinner and a movie. But rather something a bit less exciting - a simple intro to a database concept known as effective dating.
First Example: Simple Example - 50 States and Dates of Statehood
This following introductory example about effective dating may be stretching things a bit. The data topic is the 50 States of the United States and the Date of Statehood or the Date the State became a State. This example is a stretch because the "effective" date is the core piece of data that we are concerned with. But I believe is represents a good intro to the concept of effective dating. The second example is a probably a better example of the effective dating concept.
Rather than bore you with a bunch of narrative I am going to jump straight into the T-SQL code.
I have tested this code on SQL Server 2008 Dev Ed.
USE master;
GO
IF EXISTS (SELECT * FROM sys.databases WHERE Name = 'DBEffDateStates')
DROP DATABASE DBEffDateStates;
GO
CREATE DATABASE DBEffDateStates;
GO
USE DBEffDateStates;
GO
CREATE TABLE dbo.States(
Num int PRIMARY KEY,
Name varchar(50) NOT NULL,
StateDate date NOT NULL
);
GO
-- Populate the States table
SET NOCOUNT ON;
GO
INSERT States (Num, Name, StateDate) VALUES (1, 'Delaware', '1787-12-07');
INSERT States (Num, Name, StateDate) VALUES (2, 'Pennsylvania', '1787-12-12');
INSERT States (Num, Name, StateDate) VALUES (3, 'New Jersey', '1787-12-18');
INSERT States (Num, Name, StateDate) VALUES (4, 'Georgia', '1788-01-02');
INSERT States (Num, Name, StateDate) VALUES (5, 'Connecticut', '1788-01-09');
INSERT States (Num, Name, StateDate) VALUES (6, 'Massachusetts', '1788-02-06');
INSERT States (Num, Name, StateDate) VALUES (7, 'Maryland', '1788-04-28');
INSERT States (Num, Name, StateDate) VALUES (8, 'South Carolina', '1788-05-23');
INSERT States (Num, Name, StateDate) VALUES (9, 'New Hampshire', '1788-06-21');
INSERT States (Num, Name, StateDate) VALUES (10, 'Virginia', '1788-06-25');
INSERT States (Num, Name, StateDate) VALUES (11, 'New York', '1788-07-26');
INSERT States (Num, Name, StateDate) VALUES (12, 'North Carolina', '1789-11-21');
INSERT States (Num, Name, StateDate) VALUES (13, 'Rhode Island', '1790-05-29');
INSERT States (Num, Name, StateDate) VALUES (14, 'Vermont', '1791-03-04');
INSERT States (Num, Name, StateDate) VALUES (15, 'Kentucky', '1792-06-01');
INSERT States (Num, Name, StateDate) VALUES (16, 'Tennessee', '1796-06-01');
INSERT States (Num, Name, StateDate) VALUES (17, 'Ohio', '1803-03-01');
INSERT States (Num, Name, StateDate) VALUES (18, 'Louisiana', '1812-04-30');
INSERT States (Num, Name, StateDate) VALUES (19, 'Indiana', '1816-12-11');
INSERT States (Num, Name, StateDate) VALUES (20, 'Mississippi', '1817-12-10');
INSERT States (Num, Name, StateDate) VALUES (21, 'Illinois', '1818-12-03');
INSERT States (Num, Name, StateDate) VALUES (22, 'Alabama', '1819-12-14');
INSERT States (Num, Name, StateDate) VALUES (23, 'Maine', '1820-03-15');
INSERT States (Num, Name, StateDate) VALUES (24, 'Missouri', '1821-08-10');
INSERT States (Num, Name, StateDate) VALUES (25, 'Arkansas', '1836-06-15');
INSERT States (Num, Name, StateDate) VALUES (26, 'Michigan', '1837-01-26');
INSERT States (Num, Name, StateDate) VALUES (27, 'Florida', '1845-03-03');
INSERT States (Num, Name, StateDate) VALUES (28, 'Texas', '1845-12-29');
INSERT States (Num, Name, StateDate) VALUES (29, 'Iowa', '1846-12-28');
INSERT States (Num, Name, StateDate) VALUES (30, 'Wisconsin', '1848-05-29');
INSERT States (Num, Name, StateDate) VALUES (31, 'California', '1850-09-09');
INSERT States (Num, Name, StateDate) VALUES (32, 'Minnesota', '1858-05-11');
INSERT States (Num, Name, StateDate) VALUES (33, 'Oregon', '1859-02-14');
INSERT States (Num, Name, StateDate) VALUES (34, 'Kansas', '1861-01-29');
INSERT States (Num, Name, StateDate) VALUES (35, 'West Virginia', '1863-06-20');
INSERT States (Num, Name, StateDate) VALUES (36, 'Nevada', '1864-10-31');
INSERT States (Num, Name, StateDate) VALUES (37, 'Nebraska', '1867-03-01');
INSERT States (Num, Name, StateDate) VALUES (38, 'Colorado', '1876-08-01');
INSERT States (Num, Name, StateDate) VALUES (39, 'North Dakota', '1889-11-02');
INSERT States (Num, Name, StateDate) VALUES (40, 'South Dakota', '1889-11-02');
INSERT States (Num, Name, StateDate) VALUES (41, 'Montana', '1889-11-08');
INSERT States (Num, Name, StateDate) VALUES (42, 'Washington', '1889-11-11');
INSERT States (Num, Name, StateDate) VALUES (43, 'Idaho', '1890-07-03');
INSERT States (Num, Name, StateDate) VALUES (44, 'Wyoming', '1890-07-10');
INSERT States (Num, Name, StateDate) VALUES (45, 'Utah', '1896-01-04');
INSERT States (Num, Name, StateDate) VALUES (46, 'Oklahoma', '1907-11-16');
INSERT States (Num, Name, StateDate) VALUES (47, 'New Mexico', '1912-01-06');
INSERT States (Num, Name, StateDate) VALUES (48, 'Arizona', '1912-02-14');
INSERT States (Num, Name, StateDate) VALUES (49, 'Alaska', '1959-01-03');
INSERT States (Num, Name, StateDate) VALUES (50, 'Hawaii', '1959-08-21');
GO
SET NOCOUNT OFF;
GO
-- Create a Scalar function to return the number of States
-- based upon the input date (@CurrentDate).
CREATE FUNCTION udf_GetStateCount (@CurrentDate date)
RETURNS int
AS
BEGIN
DECLARE @Result int;
SELECT @Result = COUNT(*)
FROM dbo.States s
WHERE s.StateDate <= @CurrentDate;
RETURN @Result;
END
GO
-- Create a stored procedure that returns a listing of
-- States based upon an input date (@CurrentDate).
CREATE PROCEDURE usp_GetStates (@CurrentDate date)
AS
BEGIN
SELECT s.Num StateNumber,
s.Name StateName,
s.StateDate
FROM dbo.States s
WHERE s.StateDate <= @CurrentDate;
END
GO
-- Modify the @TestDate value to see both the number of
-- States on a given date and the listing of the States.
DECLARE @TestDate DATE;
SET @TestDate = '12/08/1809'
SELECT dbo.udf_GetStateCount(@TestDate);
EXEC usp_GetStates @CurrentDate = @TestDate;
Wikipedia has a really cool
animated gif showing the 50 states of the US coming to life.
Second Example: The Better Example - Dams, Reservoirs, and Water Levels
The second example is a more concrete example of effective dating and will require more narrative.
A while back I built some software for water treatment plants. Water treatment plants generally get their water from two main sources - either underground from an aquifer or from what is called surface water. Surface water includes lakes, rivers, and streams. A customer got its water from a stream that only ran during the wet season. During the rainy season excessive amounts of water flowed - far more than was needed or could be used by the town. During the dry season there was little or no water for the town - the water treatment plant sat idle or seriously underused. This posed a significant problem to the town which they solved by building a reservoir to capture some of the excess water available during the wet season. This trapped water could be used during the dry season to supply the town with water (plus give people a place to fish and swim).
The reservoir was the town's lifeline and was hugely important. One of the parts that I built for the water treatment plant was a system that helped them track the reservoir water level. Now what does that mean? Let's start by saying that reservoirs, dams, and water are very interesting things and have some very interesting features and characteristics. When we decide to build a dam we have 2 main design criteria in mind that are seemingly contradictory - we want to trap the most amount of water possible with the smallest dam that we can possibly build. The ideal place to build a dam that fits this criteria is to find a really narrow gap that the river or stream flows through with really steep walls where upstream from this narrow gap the river or stream opens up and the walls spread out and have a long gentle slope (lots of inlets, side streams, and nooks & crannies helps too). If you can find this you have a good place for a dam.
When you have built the dam you now have a structure that captures water. At zero feet the dam is empty. Once you close off the release valves the dam starts to fill up. How much water has been captured when the water level is at 10 feet? At 20 feet? Assume the dam height is 50ft high. How much water is captured by the dam when it is full and overflowing? How do you determine how much water is trapped behind the dam at various heights? Well, the surveyors and civil engineers and map guys all get together with their surveys and topo maps and equipment and slide rules and do some analysis and crunch a bunch of numbers and ultimately provide a data table that converts the height of the water at the dam into water volume in gallons. The table does not list water height down to the inch, but rather may be some reasonable interval such as every 5 feet in dam height for example. Given a recorded value that falls between the engineers' data table levels some form of interpolation must be done to obtain the water volume.
Another thing that is interesting about the water level of a dam it is typically not measured from the bottom but rather from the top when the dam is full. A measurement of zero (0) feet means that the reservoir is full of water. Assuming a dam is 50 feet tall, you can say that if the water level is 50 foot then the reservoir is completely empty.
The water trapped behind a dam is in the shape of a trough or has the cross section of a V. In other words when a reservoir is close to being empty, a one foot level change translates into a small water volume. Conversely when a reservoir is full or near full, a one foot level change can represent a HUGE amount of water.
Another characteristic of reservoirs, is that over time they fill up with silt, mud, rock, and debris. The engineers' data table that translates dam water level to water valume becomes dated. When this occurs generally one of two things happens - either a new survey is done and a new data table that accounts for the reduced capacity of the reservoir is generated or the bottom of the reservoir is cleaned out. The first option is kinda boring and could be used to as an example to show this concept of effective dating, but the second option is much more drastic and has a much more profound result, especially if the dam in question is an earthen dam. I am going with the second option <grin>. In the dry season when a dam is near empty or empty and the town is buying water temporarily from a neighoring town, an army of heavy machinery descends on the reservoir and dam and cleans it out. The silt, mud, rock stuck in the bottom of the reservoir can be used to build up the dam even higher (again assuming an earthen dam). Oftentime extra soil and rock is taken from the bottom, sides, and walls of the reservoir to both increase the capacity of the reservoir plus build up the dam so that it is taller and stronger. In the end we have a harder better faster stronger dam that can hold much more water. Plus we need a new level to volume data table from the engineers.
Enough narrative. Let's jump into T-SQL
USE master;
GO
IF EXISTS (SELECT * FROM sys.databases WHERE Name = 'DBEffDateWater')
DROP DATABASE DBEffDateWater;
GO
CREATE DATABASE DBEffDateWater;
GO
USE DBEffDateWater;
GO
-- The Survey and Level2Volume tables store the
-- engineers survey information. The Survey table
-- stores the effective date of the survey in the
-- SurveyDate column. The actual level to volume
-- value pairs are stored in the Level2Volume table.
CREATE TABLE dbo.Survey (
SurveyID int PRIMARY KEY,
SurveyDate date NOT NULL
);
GO
CREATE TABLE dbo.Level2Volume (
SurveyID int NOT NULL,
WaterLevel numeric(4, 1) NOT NULL, -- in feet measured from top of dam
WaterVolume numeric(7, 3) NOT NULL -- in millions of gallons
);
ALTER TABLE dbo.Level2Volume
ADD PRIMARY KEY (SurveyID, WaterLevel);
GO
-- Assume dam was first created 01/01/1970. The
-- following INSERT statements represent the first
-- engineer survey and first level to volume values
-- for the town's new reservoir.
SET NOCOUNT ON;
GO
INSERT dbo.Survey (SurveyID, SurveyDate) VALUES (1, '01/01/1970');
INSERT dbo.Level2Volume (SurveyID, WaterLevel, WaterVolume) VALUES (1, 0.0, 100.0);
INSERT dbo.Level2Volume (SurveyID, WaterLevel, WaterVolume) VALUES (1, 5.0, 70.0);
INSERT dbo.Level2Volume (SurveyID, WaterLevel, WaterVolume) VALUES (1, 10.0, 50.0);
INSERT dbo.Level2Volume (SurveyID, WaterLevel, WaterVolume) VALUES (1, 20.0, 20.0);
INSERT dbo.Level2Volume (SurveyID, WaterLevel, WaterVolume) VALUES (1, 30.0, 7.0);
INSERT dbo.Level2Volume (SurveyID, WaterLevel, WaterVolume) VALUES (1, 40.0, 3.0);
INSERT dbo.Level2Volume (SurveyID, WaterLevel, WaterVolume) VALUES (1, 50.0, 0.0);
SET NOCOUNT OFF;
GO
-- Reservoir Cleanout and Reconstruction.
-- In 1990 the town decided to clean out the bottom of the reservoir
-- and increase the height of the earthen dam by 3 feet. A new
-- survey was completed and the resulting data table was added
-- to the database. This work was complete 07/15/1990. Total
-- capacity has increased significantly.
SET NOCOUNT ON;
GO
INSERT dbo.Survey (SurveyID, SurveyDate) VALUES (2, '07/15/1990');
INSERT dbo.Level2Volume (SurveyID, WaterLevel, WaterVolume) VALUES (2, 0.0, 250.0);
INSERT dbo.Level2Volume (SurveyID, WaterLevel, WaterVolume) VALUES (2, 5.0, 90.0);
INSERT dbo.Level2Volume (SurveyID, WaterLevel, WaterVolume) VALUES (2, 10.0, 60.0);
INSERT dbo.Level2Volume (SurveyID, WaterLevel, WaterVolume) VALUES (2, 20.0, 35.0);
INSERT dbo.Level2Volume (SurveyID, WaterLevel, WaterVolume) VALUES (2, 30.0, 13.0);
INSERT dbo.Level2Volume (SurveyID, WaterLevel, WaterVolume) VALUES (2, 40.0, 5.0);
INSERT dbo.Level2Volume (SurveyID, WaterLevel, WaterVolume) VALUES (2, 50.0, 1.5);
INSERT dbo.Level2Volume (SurveyID, WaterLevel, WaterVolume) VALUES (2, 53.0, 0.0);
SET NOCOUNT OFF;
GO
-- Create a Scalar function that takes into account
-- the effective dating of the Surveys to translate
-- a dam water level reading in feet to the correct
-- water volume using Linear Interpolation.
CREATE FUNCTION dbo.udf_GetWaterVolume(@ReadingDate date, @WaterLevel numeric(4, 1))
RETURNS numeric(7, 3)
BEGIN
DECLARE @Result numeric(7, 3);
DECLARE @EffectiveSurveyID int;
SELECT TOP 1 @EffectiveSurveyID = SurveyID
FROM Survey
WHERE SurveyDate <= @ReadingDate
ORDER BY SurveyDate DESC;
DECLARE @WL1 numeric(4, 1);
DECLARE @V1 numeric(7, 3);
DECLARE @WL2 numeric(4, 1);
DECLARE @V2 numeric(7, 3);
SELECT TOP 1 @WL1 = WaterLevel, @V1 = WaterVolume
FROM Level2Volume L2V
WHERE L2V.SurveyID = @EffectiveSurveyID
AND L2V.WaterLevel <= @WaterLevel
ORDER BY L2V.WaterLevel DESC;
SELECT TOP 1 @WL2 = WaterLevel, @V2 = WaterVolume
FROM Level2Volume L2V
WHERE L2V.SurveyID = @EffectiveSurveyID
AND L2V.WaterLevel >= @WaterLevel
ORDER BY L2V.WaterLevel ASC;
-- CAUTION! Linear Interpolation Work Zone Follows:
SET @Result = @V1 + ((@WaterLevel - @WL1) * ((@V2 - @V1) / (@WL2 - @WL1)))
RETURN @Result;
END;
GO
-- Tests to see changes in translation of a given water level to
-- volume based upon Reading Date. This example shows a good
-- example of the database concept known as effective dating.
SELECT dbo.udf_GetWaterVolume('07/01/1990', 15.0) WaterVolume; -- before reconstruction
SELECT dbo.udf_GetWaterVolume('08/01/1990', 15.0) WaterVolume; -- after reconstruction
In closing, I hope this simple introduction helped your understanding of the effective dating database topic. Please share any feedback or ideas that you may have. Thanks!