Table AD_2019_AGE_YRS_ROUND

Create table AD_2019_AGE_YRS_ROUND for calculations involving Age

Analysis of the Field VAERSDATA.AGE_YRS:

The field, VAERSDATA.AGE_YRS, is not a numeric, but a character field.

—

—

This is odd, considering that many numbers in the field, have decimal places.

—

use VAERS_2019
go

Select AGE_YRS
, count(*)  Count
FROM dbo.[VAERSDATA]
where  AGE_YRS is not NULL
group by AGE_YRS
order by 1
go

—

—

Being characters, instead of numeric, causes issues. In a simple ORDER BY, instead of ordering from 1.99 to 2, the order jumps to 10.0.

—

Decimal Places in the Age:

Partial years in the age, require some judgment calls. If a child is .4 years old, for calculations requiring a whole number, should the child be considered age 0, or age 1?

Round:

The SQL Server Round function creates errors rounding up. To use Round, a CAST is required.

Create View V_AD_2019_AGE_YRS_ROUND:

—

drop view V_AD_2019_AGE_YRS_ROUND
go

Create view V_AD_2019_AGE_YRS_ROUND
as
(
SELECT
VAERS_ID
, AGE_YRS as AGE_YRS_ORIG
, convert ( float, AGE_YRS ) as AGE_YRS_FLOAT
, CAST ( AGE_YRS AS decimal (6, 0) )
AS AGE_YRS_ROUND
FROM dbo.[VAERSDATA]
)
go

select count(*) Count
from dbo.[V_AD_2019_AGE_YRS_ROUND]
go

—-

Count: 759,483

—

Create Table AD_2019_AGE_YRS_ROUND:

—

select *
into AD_2019_AGE_YRS_ROUND
from dbo.[V_AD_2019_AGE_YRS_ROUND]
go

alter table AD_2019_AGE_YRS_ROUND
add primary key (VAERS_ID)
go

select count(*)
from dbo.[AD_2019_AGE_YRS_ROUND]
go

—

Count: 759,483

Table structure for AD_2019_AGE_YRS_ROUND: