Age calculation and Age function in SQL Server

AGE CALCULATION

1 Calculating Age as of End of the year

Sometime as business requirement dictate that we should calculate age at the time of end year ( this can be end of financial year, accounting year, measurement year, etc)

Let's say we have a table of employee with a field as Date_of_Birth or DOB.

DECLARE @AgeAsOf DATE = '2015-12-31

Select DOB, AGE = CASE WHEN dateadd(year, datediff (year, E.DOB, @AgeAsOf), E.DOB) > @AgeAsOf
THEN datediff (year, E.DOB, @AgeAsOf) - 1
ELSE datediff (year, E.DOB, @AgeAsOf)
END

FROM dbo.Employee


Say further that you have to select data based on certain age range and you have to passed this in your WHERE condition. Here we are looking for those employee who live in US
and are in age range between 35 and 50 only

DECLARE @AgeAsOf DATE = '2015-12-31

Select * FROM dbo.EMPLOYEE E
WHERE E.Employee_location = 'US'
AND (CASE WHEN dateadd(year, datediff (year, E.DOB, @AgeAsOf), E.DOB) > @AgeAsOf
THEN datediff (year, E.DOB, @AgeAsOf) - 1
ELSE datediff (year, E.DOB, @AgeAsOf)
END ) BETWEEN 35 AND 50

2. Calculating Age as of today (getdate())

You can use above query to get age as of today by replacing @AgeAsOf variable with getdate() function

Select DOB, AGE = AGE =CASE WHEN dateadd(year, datediff (year, E.DOB, getdate()), E.DOB) >getdate()
THEN datediff (year, E.DOB, getdate()) - 1
ELSE datediff (year, E.DOB, getdate())
END

FROM dbo.Employee


Select * FROM dbo.EMPLOYEE E
WHERE E.Employee_location = 'US'
AND (CASE WHEN dateadd(year, datediff (year, E.DOB, getdate()), E.DOB) > getdate()
THEN datediff (year, E.DOB, getdate() - 1
ELSE datediff (year, E.DOB, getdate())
END ) BETWEEN 35 AND 50


3. Creating a function to get AGE.
create function [dbo].[AgeAtGivenDate](
    @DOB    DATE,
    @PassedDate DATE
)

returns INT
with SCHEMABINDING
as
begin

DECLARE @iMonthDayDob INT
DECLARE @iMonthDayPassedDate INT


SELECT @iMonthDayDob = CAST(datepart (MM,@DOB) * 100 + datepart  (DD,@DOB) AS INT)
SELECT @iMonthDayPassedDate = CAST(datepart (MM,@PassedDate) * 100 + datepart  (DD,@PassedDate) AS INT)

RETURN DateDiff(YY,@DOB, @PassedDate)
- CASE WHEN @iMonthDayDob <= @iMonthDayPassedDate
  THEN 0
  ELSE 1
  END

END


--How to execute your function
DECLARE @ret INT;
EXEC @ret = [dbo].[AgeAtGivenDate] @DOB ='1923-08-28', @PassedDate = '2015-12-31'
PRINT @ret




Select  [dbo].[AgeAtGivenDate] ('1923-08-28',  '2015-12-31')

Select dbo.AgeAtGivenDate, DOB
FROM EMPLOYEE

SQL Table with Primary Key and Indexes

When we create a sql table with a primary key, we need to understand what happens behind the scene and how indexes are affected by it.

Let's go through a simple example and touch each topic as we go through it.

Let's create a table called country with following structure.

Create table dbo.Country 
(Country_ID int Identity (1,1) NOT NULL Primary Key
, Name Varchar(40)  NOT NULL
, Alpha_2_Code_Name Varchar(2)
, Alpha_3_Code_Name Varchar(3)
, Numeric_Code int NOT NULL
, ISO_3166_2_Code Varchar(20)
);

And insert following test data in this table just created.

Insert into dbo.Country (Name, Alpha_2_Code_Name, Alpha_3_Code_Name, Numeric_Code,ISO_3166_2_Code) VALUES  ('Afghanistan','AF','AFG',004,'ISO 3166-2:AF')
Insert into dbo.Country (Name, Alpha_2_Code_Name, Alpha_3_Code_Name, Numeric_Code,ISO_3166_2_Code) VALUES  ('Åland Islands','AX','ALA',248,'ISO 3166-2:AX')
Insert into dbo.Country (Name, Alpha_2_Code_Name, Alpha_3_Code_Name, Numeric_Code,ISO_3166_2_Code) VALUES  ('Albania','AL' ,'ALB',008,'ISO 3166-2:AL')
Insert into dbo.Country (Name, Alpha_2_Code_Name, Alpha_3_Code_Name, Numeric_Code,ISO_3166_2_Code) VALUES  ('Algeria','DZ' ,'DZA',012,'ISO 3166-2:DZ')
Insert into dbo.Country (Name, Alpha_2_Code_Name, Alpha_3_Code_Name, Numeric_Code,ISO_3166_2_Code) VALUES  ('American Samoa','AS','ASM',016 ,'ISO 3166-2:AS')
Insert into dbo.Country (Name, Alpha_2_Code_Name, Alpha_3_Code_Name, Numeric_Code,ISO_3166_2_Code) VALUES  ('Andorra','AD' ,'AND',020,'ISO 3166-2:AD')
Insert into dbo.Country (Name, Alpha_2_Code_Name, Alpha_3_Code_Name, Numeric_Code,ISO_3166_2_Code) VALUES  ('Angola','AO' ,'AGO',024,'ISO 3166-2:AO')
Insert into dbo.Country (Name, Alpha_2_Code_Name, Alpha_3_Code_Name, Numeric_Code,ISO_3166_2_Code) VALUES  ('Anguilla','AI' ,'AIA',660,'ISO 3166-2:AI')
Insert into dbo.Country (Name, Alpha_2_Code_Name, Alpha_3_Code_Name, Numeric_Code,ISO_3166_2_Code) VALUES  ('Antarctica','AQ','ATA',010,'ISO 3166-2:AQ')
Insert into dbo.Country (Name, Alpha_2_Code_Name, Alpha_3_Code_Name, Numeric_Code,ISO_3166_2_Code) VALUES  ('Antigua and Barbuda','AG' ,'ATG',028 ,'ISO 3166-2:AG')
Insert into dbo.Country (Name, Alpha_2_Code_Name, Alpha_3_Code_Name, Numeric_Code,ISO_3166_2_Code) VALUES  ('Argentina','AR','ARG',032,'ISO 3166-2:AR')

Now if we look at the table structure, this is how it looks like:

Now we look at index physical state by querying this function, we see following

SELECT page_count, index_level, record_count, index_depth
FROM sys.dm_db_index_physical_stats(db_id(N'Data_Base_Name'),object_id(N'dbo.country'),NULL, NULL, 'Detailed');

Let's see how this changes when we delete data from our table and how the index page reflects in that change.


1. DELETE FROM dbo.Country

Now re-run your above query

SELECT page_count, index_level, record_count, index_depth
FROM sys.dm_db_index_physical_stats(db_id(N'Data_Base_Name'),object_id(N'dbo.country'),NULL, NULL, 'Detailed');

So here basically we see that record count are deleted but page_count, and index_depth  remain same.

2. TRUNCATE table dbo.Country

After we truncate table, re-run your script.



SELECT page_count, index_level, record_count, index_depth
FROM sys.dm_db_index_physical_stats(db_id(N'Data_Base_Name'),object_id(N'dbo.country'),NULL, NULL, 'Detailed');

Here we see that everything has been truncated.

So when we truncate table, indexes is automatically dropped but structure definition remain same.