Bdtyktl

I will be up front and say I'm aware this is a messy query. The database is not managed by me so some of the relations are long and complex. Then there is the fact that SQL is just something I know a tiny bit about so these reports get pushed on me and I'm not SQL expert.

That said I'm trying to pull in 3 seprate queries for answer to questions we have on a form. I'm not sure how to bind these with the correct join and I have no doubt there is a better way to do it.

Here is my existing Query:

Select Distinct C.client_id,

  C.first_name,

  C.last_name,

  CV.rev_timein,

  Q1.answer,

  Q2.answer,

  Q3.answer



From Clients C,



(

    Select  C2.client_id as client_id,

      Answer.answer as answer

    From Clients C2

      Inner Join ClientVisit On C2.client_id = ClientVisit.client_id

      Left Join SavedVisitAnswer On SavedVisitAnswer.clientvisit_id =

        ClientVisit.clientvisit_id

      Left Join Question On Question.question_id = SavedVisitAnswer.question_id

      Left Join Category On Question.category_id = Category.category_id

      Left Join FormVersion On Category.form_ver_id = FormVersion.form_ver_id

      Left Join Forms On Forms.form_id = FormVersion.form_id

      Inner Join Answer On SavedVisitAnswer.answer_id = Answer.answer_id

    Where SavedVisitAnswer.question_id = '518722' And Forms.form_id = '336' And

      FormVersion.is_active = '1' And ClientVisit.rev_timein >= @param1 And

      ClientVisit.rev_timein < DateAdd(d, 1, @param2)

    GROUP BY  C2.client_id, Answer.answer

) Q1,



(

    Select  C3.client_id as client_id,

      Answer.answer as answer

    From Clients C3

      Inner Join ClientVisit On C3.client_id = ClientVisit.client_id

      Left Join SavedVisitAnswer On SavedVisitAnswer.clientvisit_id =

        ClientVisit.clientvisit_id

      Left Join Question On Question.question_id = SavedVisitAnswer.question_id

      Left Join Category On Question.category_id = Category.category_id

      Left Join FormVersion On Category.form_ver_id = FormVersion.form_ver_id

      Left Join Forms On Forms.form_id = FormVersion.form_id

      Inner Join Answer On SavedVisitAnswer.answer_id = Answer.answer_id

    Where SavedVisitAnswer.question_id = '518722' And Forms.form_id = '336' And

      FormVersion.is_active = '1' And ClientVisit.rev_timein >= @param1 And

      ClientVisit.rev_timein < DateAdd(d, 1, @param2)

    GROUP BY  C3.client_id, Answer.answer

) Q2,



(

    Select  C4.client_id as client_id,

      Answer.answer as answer

    From Clients C4

      Inner Join ClientVisit On C4.client_id = ClientVisit.client_id

      Left Join SavedVisitAnswer On SavedVisitAnswer.clientvisit_id =

        ClientVisit.clientvisit_id

      Left Join Question On Question.question_id = SavedVisitAnswer.question_id

      Left Join Category On Question.category_id = Category.category_id

      Left Join FormVersion On Category.form_ver_id = FormVersion.form_ver_id

      Left Join Forms On Forms.form_id = FormVersion.form_id

      Inner Join Answer On SavedVisitAnswer.answer_id = Answer.answer_id

    Where SavedVisitAnswer.question_id = '518722' And Forms.form_id = '336' And

      FormVersion.is_active = '1' And ClientVisit.rev_timein >= @param1 And

      ClientVisit.rev_timein < DateAdd(d, 1, @param2)

    GROUP BY  C4.client_id, Answer.answer

) Q3





  Inner Join ClientVisit CV On C.client_id = ClientVisit.client_id

  Left Join SavedVisitAnswer On SavedVisitAnswer.clientvisit_id =

    ClientVisit.clientvisit_id

  Left Join Question On Question.question_id = SavedVisitAnswer.question_id

  Left Join Category On Question.category_id = Category.category_id

  Left Join FormVersion On Category.form_ver_id = FormVersion.form_ver_id

  Left Join Forms On Forms.form_id = FormVersion.form_id

  Inner Join Answer On SavedVisitAnswer.answer_id = Answer.answer_id



Where Forms.form_id = '336' And

  FormVersion.is_active = '1' And CV.rev_timein >= @param1 And

  CV.rev_timein < DateAdd(d, 1, @param2)

Order By C.last_name

The goal is to have the output for each client in the date range formated as such.

Client_ID, First Name, Last Name, Date-Time, Answer1, Answer2, Answer3