March 7, 2017 at 1:24 pm
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.
March 7, 2017 at 2:39 pm
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
March 8, 2017 at 6:36 am
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply