Answered step by step
Verified Expert Solution
Question
1 Approved Answer
PERSON _ DIMENSION Column Name Data Type PERSON _ DIM _ SID NUMBER ( 3 8 ) PARTICIPANT _ IDENTIFIER NUMBER ( 3 8 )
PERSONDIMENSION
Column Name Data Type
PERSONDIMSID NUMBER
PARTICIPANTIDENTIFIER NUMBER
SSN VARCHAR
FILENUMBER VARCHAR
FIRSTNAME VARCHAR
LASTNAME VARCHAR
GENDER VARCHAR
BRANCHOFSERVICE VARCHAR
NUMBEROFDEPENDENTS INTEGER
EDUCATIONLEVEL VARCHAR
ENTERONDUTYDATE DATE
DISCHARGEDATE DATE
DATEOFBIRTH DATE
DATEOFDEATH DATE
BEGINEFFECTIVEDATE DATE
ENDEFFECTIVEDATE DATE
CHANGEREASON VARCHAR
CURRENTINDICATOR VARCHAR
CASESTATUSDIMENSION
Column Name Data Type
CASESTATUSCODEDIMSID NUMBER
CASESTATUSCODE VARCHAR
CASESTATUSCODEDESC VARCHAR
DATEDIMENSION
Column Name Data Type
DATEDIMSID NUMBER
ACTUALDATE DATE
FISCALYEAR VARCHAR
CASESTATUSFACT
Column Name Data Type
CASEDIMSID NUMBER
CASESTATUSFACTSID NUMBER
CASESTATUSCODEDIMSID NUMBER
VETERANPERSONSID NUMBER
CLMANTPERSONSID NUMBER
CASESTATUSDATESID NUMBER
CASEDIM
CASEDIMSID NUMBER
CASEID NUMBER
BNFTCLAIMID NUMBER
Scenario #
Within this scenario, a disable veteran is assigned a Case file which is uniquely identified by a case identifier. Each Case for a VeteranClaimant progresses from status to status. Most times the Veteran is the Claimant but there are situations where a Veterans Dependent could be the Claimant.
Write SQL Query: Using the above provided Fact and Dimensions, write a SQL Query to return the following fields for Veteran's where a CASEID has more than 'Applicant' status
Veterans First Name,
Veterans Last Name,
Veterans SSN
Veterans File Number,
Claimants First Name,
Claimants Last Name,
Claimants SSN
Claimants File Number,
the VeteranClaimants Case ID
the VeteranClaimants Case Status Code,
the VeteranClaimants Case Status Description,
the Veteran Claimants Case Status Date,
the Fiscal Year that the VeteranClaimants Case Status Date is in
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started