How to Calculate the Validity Duration of Each Row in SQL

Querying the Duration of Each Row's Validity

Write a SQL query to calculate the duration each row was valid in a temporal table.

Solution:

-- Calculate the duration each row was valid.
SELECT EmployeeID, Name, Position,
       SysStartTime, SysEndTime,
       DATEDIFF(DAY, SysStartTime, SysEndTime) AS ValidityDurationInDays
FROM Employees
FOR SYSTEM_TIME ALL
ORDER BY SysStartTime;

Explanation:

• Purpose of the Query :
• The goal is to demonstrate how to calculate the duration each row was valid in a temporal table.
• Key Components :
• DATEDIFF: Calculates the difference between SysStartTime and SysEndTime.
• FOR SYSTEM_TIME ALL: Retrieves all versions of rows from both the main table and history table.
• ValidityDurationInDays: Displays the duration in days for which each version of the row was valid.
• Why Query Validity Duration? :
• Understanding the validity duration helps identify how long specific data states persisted.
• It supports trend analysis and auditing.
• Real-World Application :
• In project management systems, this tracks how long employees held specific roles.

Additional Notes:

• Use DATEDIFF with appropriate units (e.g., days, hours) based on the granularity needed.
• Combine with filters to focus on specific subsets of data.
• Important Considerations:
• Ensure that SysStartTime and SysEndTime are correctly populated.

For more Practice: Solve these Related Problems:

• Write a SQL query to calculate the validity duration of each row in a temporal table named "Contracts".
• Write a SQL query to determine how long each version of a row was valid in a temporal table named "Projects".
• Write a SQL query to compute the time span for which each record remained unchanged in a temporal table named "CustomerDetails".
• Write a SQL query to measure the duration of validity for each version of rows in a temporal table named "ProductCatalog".

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Querying Changes Within a Specific Time Range.
Next SQL Exercise: Identifying Rows with No Changes Over Time.