PERSON$\_$DIMENSION

Column Name Data Type

PERSON$\_$DIM$\_$SID NUMBER$(38)$

PARTICIPANT$\_$IDENTIFIER NUMBER$(38)$

SSN VARCHAR$2(9)$

FILE$\_$NUMBER VARCHAR$2(9)$

FIRST$\_$NAME VARCHAR$2(30)$

LAST$\_$NAME VARCHAR$2(30)$

GENDER VARCHAR$2(1)$

BRANCH$\_$OF$\_$SERVICE VARCHAR$2(50)$

NUMBER$\_$OF$\_$DEPENDENTS INTEGER

EDUCATION$\_$LEVEL VARCHAR$2(8)$

ENTER$\_$ON$\_$DUTY$\_$DATE DATE

DISCHARGE$\_$DATE DATE

DATE$\_$OF$\_$BIRTH DATE

DATE$\_$OF$\_$DEATH DATE

BEGIN$\_$EFFECTIVE$\_$DATE DATE

END$\_$EFFECTIVE$\_$DATE DATE

CHANGE$\_$REASON VARCHAR$2(50)$

CURRENT$\_$INDICATOR VARCHAR$2(1)$

CASE$\_$STATUS$\_$DIMENSION

Column Name Data Type

CASE$\_$STATUS$\_$CODE$\_$DIM$\_$SID NUMBER$(38)$

CASE$\_$STATUS$\_$CODE VARCHAR$2(30)$

CASE$\_$STATUS$\_$CODE$\_$DESC VARCHAR$2(30)$

DATE$\_$DIMENSION

Column Name Data Type

DATE$\_$DIM$\_$SID NUMBER$(38)$

ACTUAL$\_$DATE DATE

FISCAL$\_$YEAR VARCHAR$2(4)$

CASE$\_$STATUS$\_$FACT

Column Name Data Type

CASE$\_$DIM$\_$SID NUMBER

CASE$\_$STATUS$\_$FACT$\_$SID NUMBER$(38)$

CASE$\_$STATUS$\_$CODE$\_$DIM$\_$SID NUMBER$(38)$

VETERAN$\_$PERSON$\_$SID NUMBER$(38)$

CLMANT$\_$PERSON$\_$SID NUMBER$(38)$

CASE$\_$STATUS$\_$DATE$\_$SID NUMBER$(38)$

CASE$\_$DIM

CASE$\_$DIM$\_$SID NUMBER

CASE$\_$ID NUMBER$(38)$

BNFT$\_$CLAIM$\_$ID NUMBER

Scenario #$1$

Within this scenario, a disable veteran is assigned a Case file which is uniquely identified by a case identifier. Each Case for a Veteran$/$Claimant progresses from status to status.$$ Most times the Veteran is the Claimant but there are situations where a Veteran$$s Dependent could be the Claimant.

Write $1$ SQL Query: Using the above provided Fact and Dimensions, write a SQL Query to return the following fields for Veteran's where a CASE$\_$ID has more than $1$ 'Applicant' status

Veteran$$s First Name,

Veteran$$s Last Name,

Veteran$$s SSN$,$

Veteran$$s File Number,

Claimant$$s First Name,

Claimant$$s Last Name,

Claimant$$s SSN$,$

Claimant$$s File Number,

the Veteran$/$Claimant$$s Case ID$,$

the Veteran$/$Claimant$$s Case Status Code,

the Veteran$/$Claimant$$s Case Status Description,

the Veteran $/$Claimant$$s Case Status Date,

the Fiscal Year that the Veteran$/$Claimant$$s Case Status Date is in$.$