June 20, 2018 at 2:24 am
Hi Experts ,
I have a fact table which contains a single row for each question which could have number of answers with start employment status and end employment status keys.
Fact Table
Dimension Table
I need to create a view for each question (i.e. 1939) and return all 7 rows from dimension with one actual value for start/end status keys being one (1) if it matches and (0) for all non matching rows.
Finally output something like below
Hope it make sense
Kind Regards
June 20, 2018 at 6:00 am
Hello!
If I understood correctly, in the last image above (with the results), in the column [START_EMP_STATUS_KEY], the position of the 1 is wrong. It shouldn't be at "At work and coping well" (which has the [EMP_STATUS_KEY] = 1), but at the [EMP_STATUS_KEY] = 6, which is "Retired".
So in the first line, the column [START_EMP_STATUS_KEY] should be 0.
And in the second to last line, the column [START_EMP_STATUS_KEY] should be 1.
If it's like above, you could try something like this:
1. A function that will create that ouput for one QuestionId:
CREATE FUNCTION [dbo].[fn_getTablePerQuestion](@QuestionId int)
RETURNS TABLE
AS
RETURN
(
SELECT QUESTIONID, EMP_STATUS_DESC, 1 AS START_EMP_STATUS_KEY , 0 AS END_EMP_STATUS_KEY
FROM FACT_TABLE F
INNER JOIN DIM_TABLE D
ON START_EMP_STATUS_KEY = EMP_STATUS_KEY
WHERE QUESTIONID = @QUESTIONID
UNION ALL
SELECT QUESTIONID, EMP_STATUS_DESC,0 AS START_EMP_STATUS_KEY , 1 AS END_EMP_STATUS_KEY
FROM FACT_TABLE F
INNER JOIN DIM_TABLE D
ON END_EMP_STATUS_KEY = EMP_STATUS_KEY
WHERE QUESTIONID = @QUESTIONID
UNION ALL
SELECT @QUESTIONID AS QUESTIONID, EMP_STATUS_DESC,0 AS START_EMP_STATUS_KEY , 0 AS END_EMP_STATUS_KEY
FROM FACT_TABLE F
FULL OUTER JOIN DIM_TABLE D
ON (END_EMP_STATUS_KEY = EMP_STATUS_KEY OR START_EMP_STATUS_KEY = EMP_STATUS_KEY)
WHERE ISNULL(QUESTIONID,0) = 0
)
2. A view that will apply that function to your entire table:
CREATE VIEW questions_view
AS
SELECT T1.*
FROM FACT_TABLE F
CROSS APPLY [FN_GETTABLEPERQUESTION] (F.QUESTIONID) AS T1;
GO
Let me know if it's what you are looking for!
June 20, 2018 at 8:50 am
Hi ,
first off all thanks for taking time to look into it and respond and you are quite right in the output bit sorry that's oversight from my end.
Two issues I have
1. It takes forever to workout with function as takes long time to populate the view; is there alternative approach to achieve it
it gives below output with the above function/view
While output I need looking to return all rows similar to below
Hope it make sense and you would be able to help
Kind Regards
June 20, 2018 at 9:42 am
nadeem161 - Wednesday, June 20, 2018 8:50 AMHi ,first off all thanks for taking time to look into it and respond and you are quite right in the output bit sorry that's oversight from my end.
Two issues I have
1. It takes forever to workout with function as takes long time to populate the view; is there alternative approach to achieve it
it gives below output with the above function/viewWhile output I need looking to return all rows similar to below
Hope it make sense and you would be able to help
Kind Regards
Try this on for size:DECLARE @QID AS int = 1939;
WITH DIMENSION_TABLE AS (
SELECT 1 AS EMP_STATUS_KEY, 'At work and coping well' AS EMP_STATUS_DESC
UNION ALL
SELECT 2, 'At work and struggling or reduced duties due to ...'
UNION ALL
SELECT 3, 'Off work due to complaint'
UNION ALL
SELECT 4, 'Off work for other reason'
UNION ALL
SELECT 5, 'Not working'
UNION ALL
SELECT 6, 'Retired'
UNION ALL
SELECT -1, NULL
),
FACT_QUESTIONS AS (
SELECT
1939 AS QuestionID,
6 AS START_EMP_STATUS_KEY,
-1 AS END_EMP_STATUS_KEY
)
SELECT
@QID AS QuestionID,
DT.EMP_STATUS_DESC,
CASE
WHEN FQ1.START_EMP_STATUS_KEY IS NOT NULL THEN 1
ELSE 0
END AS START_EMP_STATUS_KEY,
CASE
WHEN FQ2.END_EMP_STATUS_KEY IS NOT NULL THEN 1
ELSE 0
END AS END_EMP_STATUS_KEY
FROM DIMENSION_TABLE AS DT
LEFT OUTER JOIN FACT_QUESTIONS AS FQ1
ON DT.EMP_STATUS_KEY = FQ1.START_EMP_STATUS_KEY
AND FQ1.QuestionID = @QID
LEFT OUTER JOIN FACT_QUESTIONS AS FQ2
ON DT.EMP_STATUS_KEY = FQ2.END_EMP_STATUS_KEY
AND FQ2.QuestionID = @QID
ORDER BY 3 DESC, 4 ASC, DT.EMP_STATUS_KEY;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply