Date From and To Calculations – SQL

Hi,

This is another quick post on some of my past challenges faced with “From” and “To” date queries.
Many times in the business BI modelling there are instances where computed measures are on daily basis, but the storage of the entity in the questions is on a From-To basis.
(Even consider Slowly Changing Dimension Type 3, with ValidFrom and ValidTo dates)

Some examples from the real world would be like for:

HR: Employee, EmployedFrom — EmployedTo
Retail: Order, OrderDate — ShippedDate
Insurance: Policy, StartDate — ExpiryDate

and many more…

Now to deal with such situations, involving expansion of From-To rows to daily data within From-To bounds. The key is to have a global date table in your data base so that a join with the same can achieve you desired.

The intention of this post is to channel the minds of developers who as myself come from a scripting background and navigate to our ever handy while-for loops for achieving the same.

But moral of the story is for any database system the further you are away from custom loops/cursors the better you are on your design and performance.

SELECT * FROM dbo.Employee;

Note: Look at the null value in the record 3 , indicating the employee is on going, hence in the query below the null is replaced by the GETDATE() function

SELECT * 
FROM dbo.Employee EMP 
INNER JOIN dbo.Dates DTE ON DTE.CalenderDateValue BETWEEN 
							EMP.EmployedFrom 
							AND 
							CASE WHEN EMP.EmployedTo IS NULL THEN GETDATE() ELSE EMP.EmployedTo END
 GO

And there you go, now the single row of employee is expanded on daily baisis, and if you now have daily rates or some thing similar you can then aggregate by dumping this above query as sub-query and you have it…!!!

And also we get to know the other possibilities of join mechanism , in addition to the quick linking of multiple table on exact match.

Note: The date table mentioned above is generated in my previous blog post : Dates Dimension Data Generation – MS SQL

Hope it helps…