Returning all rows while matching single row

• SQLEYES

  Old Hand

  Points: 374

  More actions

  June 20, 2018 at 2:24 am

  #370912

  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

• ralu_k_17

  SSCrazy

  Points: 2190

  More actions

  June 20, 2018 at 6:00 am

  #1994599

  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!

• SQLEYES

  Old Hand

  Points: 374

  More actions

  June 20, 2018 at 8:50 am

  #1994655

  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

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  June 20, 2018 at 9:42 am

  #1994666

  nadeem161 - Wednesday, June 20, 2018 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

  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)