finding Deceased year

  • Stephen crocker

    SSCrazy

    Points: 2452

    Sorry I'm new to DW querying.  I'm trying to figure out what the best approach would be to determine the year the person died.  

    I created a script here that will create a single table that has the info i'm needing.  


    use Master;
    --Drop Database if exists
    if DB_ID('DW_test1') is not null Drop Database DW_test1
    --Error if can't drop
    if @@ERROR = 3702
      RAISERROR('Database is in use and can not be dropped',127,127) with NOWAIT, LOG;
        
    --Create DATABASE
    Create Database DW_test1
    go

    --Use DATABASE
    use DW_test1

    -- Create Table
    create table dm_ind
    (
     id int NOT NULL,
     is_deceased NVARCHAR(2) NOT NULL,
     first_name nvarchar(20) not null,
     last_name nvarchar(20) not null,
     memnum NVARCHAR(10) not null,
     age int not null,
     row_is_current NVARCHAR(2) NOT NULL,
     row_start_date date not NULL,
     row_end_date date not null
    )

    --insert date
    insert into dm_ind (id, is_deceased, first_name, last_name, memnum, age, row_is_current, row_start_date, row_end_date)
    Values('49457','N','Vincent','Lubsey','0000019','71','N','1901-01-01','2014-12-30')
    insert into dm_ind (id, is_deceased, first_name, last_name, memnum, age, row_is_current, row_start_date, row_end_date)
    Values('252635','N','Marlon','Weiss','0000012','56','N','1901-01-01','2014-12-30')
    insert into dm_ind (id, is_deceased, first_name, last_name, memnum, age, row_is_current, row_start_date, row_end_date)
    Values('268232','N','Mark','Carter','0000006','59','N','1901-01-01','2014-12-30')
    insert into dm_ind (id, is_deceased, first_name, last_name, memnum, age, row_is_current, row_start_date, row_end_date)
    Values('155643','N','Karen','Beard','0000002','55','N','1901-01-01','2014-12-30')
    insert into dm_ind (id, is_deceased, first_name, last_name, memnum, age, row_is_current, row_start_date, row_end_date)
    Values('449886','N','Vincent','Lubsey','0000019','71','N','2014-12-31','2015-12-30')
    insert into dm_ind (id, is_deceased, first_name, last_name, memnum, age, row_is_current, row_start_date, row_end_date)
    Values('564413','N','Karen','Beard','0000002','55','N','2014-12-31','2015-12-30')
    insert into dm_ind (id, is_deceased, first_name, last_name, memnum, age, row_is_current, row_start_date, row_end_date)
    Values('686418','N','Mark','Carter','0000006','59','N','2014-12-31','2015-12-30')
    insert into dm_ind (id, is_deceased, first_name, last_name, memnum, age, row_is_current, row_start_date, row_end_date)
    Values('668379','N','Marlon','Weiss','0000012','56','N','2014-12-31','2015-12-30')
    insert into dm_ind (id, is_deceased, first_name, last_name, memnum, age, row_is_current, row_start_date, row_end_date)
    Values('957048','N','Mark','Carter','0000006','60','N','2015-12-31','2016-05-30')
    insert into dm_ind (id, is_deceased, first_name, last_name, memnum, age, row_is_current, row_start_date, row_end_date)
    Values('900983','N','Karen','Beard','0000002','56','N','2015-12-31','2016-05-30')
    insert into dm_ind (id, is_deceased, first_name, last_name, memnum, age, row_is_current, row_start_date, row_end_date)
    Values('848259','Y','Vincent','Lubsey','0000019','73','Y','2015-12-31','9999-12-31')
    insert into dm_ind (id, is_deceased, first_name, last_name, memnum, age, row_is_current, row_start_date, row_end_date)
    Values('949047','N','Marlon','Weiss','0000012','57','N','2015-12-31','2016-05-30')
    insert into dm_ind (id, is_deceased, first_name, last_name, memnum, age, row_is_current, row_start_date, row_end_date)
    Values('1116971','N','Mark','Carter','0000006','60','N','2016-05-31','2016-12-30')
    insert into dm_ind (id, is_deceased, first_name, last_name, memnum, age, row_is_current, row_start_date, row_end_date)
    Values('1111249','N','Marlon','Weiss','0000012','57','N','2016-05-31','2016-12-30')
    insert into dm_ind (id, is_deceased, first_name, last_name, memnum, age, row_is_current, row_start_date, row_end_date)
    Values('1076165','N','Karen','Beard','0000002','56','N','2016-05-31','2016-12-30')
    insert into dm_ind (id, is_deceased, first_name, last_name, memnum, age, row_is_current, row_start_date, row_end_date)
    Values('1268566','N','Marlon','Weiss','0000012','58','Y','2016-12-31','9999-12-31')
    insert into dm_ind (id, is_deceased, first_name, last_name, memnum, age, row_is_current, row_start_date, row_end_date)
    Values('1275226','N','Mark','Carter','0000006','61','Y','2016-12-31','9999-12-31')
    insert into dm_ind (id, is_deceased, first_name, last_name, memnum, age, row_is_current, row_start_date, row_end_date)
    Values('1227885','N','Karen','Beard','0000002','57','Y','2016-12-31','9999-12-31')


    If this needs to be posted somewhere else please let me know.

  • Henrico Bekker

    One Orange Chip

    Points: 27652

    consider changing is_deceased NVARCHAR(2) NOT NULL to bit, and use a 1 and 0 instead.
    where do you mention deceased date?
    what's the purpose of "row is current", row start date and row end date?

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • Stephen crocker

    SSCrazy

    Points: 2452

    I could change that, the row is current is for records that have changed (slowly changing dimensions).  I'm wanting to know what year did they pass away by what records we have in the Data warehouse.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply