USP_REPORT_MARKETINGSURVEY

create procedure dbo.USP_REPORT_MARKETINGSURVEY
(
    @MARKETINGSURVEYQUESTIONID uniqueidentifier = null,
    @MARKETINGSURVEYQUESTIONSELECTIONID uniqueidentifier = null,
    @FROMDATE datetime,
    @TODATE datetime
)
as
    set nocount on;

    set @FROMDATE = dbo.UFN_DATE_GETEARLIESTTIME(@FROMDATE);
    set @TODATE = dbo.UFN_DATE_GETLATESTTIME(@TODATE);

    declare @ANSWERSGIVEN bit
    select top 1 @ANSWERSGIVEN = 1 --We aren't using this in the report. All result scenarios use a single table in the report. Maintaining for backward compatibility.

    from dbo.SALESORDERMARKETINGSURVEYRESPONSE SOMSR
    inner join dbo.SALESORDER SO on SOMSR.SALESORDERID = SO.ID
    inner join dbo.MARKETINGSURVEYANSWER MSA on SOMSR.MARKETINGSURVEYANSWERID = MSA.ID
    where (SO.TRANSACTIONDATE between @FROMDATE and @TODATE);

    declare @DECLINED nvarchar(20) = 'Declined' 

    select 
        count(1) TOTAL,
        MSQ.QUESTIONTEXT QUESTION,
        @ANSWERSGIVEN ANSWERSGIVEN,
        SURVEY.ANSWER,
        ( --We aren't using this in the report. Report has since changed to treat declined like any other row. Maintaining for backward compatibility. :(

            select 
                COUNT(a.ID) 
            from dbo.SALESORDERMARKETINGSURVEYRESPONSE a
            inner join dbo.SALESORDER b on a.SALESORDERID = b.ID
            where a.MARKETINGSURVEYQUESTIONID = SOMSR.MARKETINGSURVEYQUESTIONID 
            and NOTCOLLECTED = 1
            and b.TRANSACTIONDATE between @FROMDATE and @TODATE
        ) DECLINED
    from dbo.SALESORDER SO
    inner join dbo.SALESORDERMARKETINGSURVEYRESPONSE SOMSR on SO.ID = SOMSR.SALESORDERID
    inner join dbo.MARKETINGSURVEYQUESTION MSQ on MSQ.ID = SOMSR.MARKETINGSURVEYQUESTIONID
    left outer join dbo.MARKETINGSURVEYANSWER MSA 
        on 
            MSA.ID = SOMSR.MARKETINGSURVEYANSWERID and
            SOMSR.NOTCOLLECTED = 0
    outer apply(
        select
            case
                when MSA.ANSWERTEXT is null then @DECLINED
                else MSA.ANSWERTEXT 
            end [ANSWER]
    ) as [SURVEY]
    where
        SO.STATUSCODE = 1
        and SO.TRANSACTIONDATE between @FROMDATE and @TODATE
        and (@MARKETINGSURVEYQUESTIONID is null or MSQ.ID = @MARKETINGSURVEYQUESTIONID)
        and (@MARKETINGSURVEYQUESTIONSELECTIONID is null or MSQ.ID in (select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@MARKETINGSURVEYQUESTIONSELECTIONID)))
    group by SOMSR.MARKETINGSURVEYQUESTIONID, MSQ.QUESTIONTEXT, [SURVEY].ANSWER
    order by SOMSR.MARKETINGSURVEYQUESTIONID, MSQ.QUESTIONTEXT, case when SURVEY.ANSWER = @DECLINED then 1 end asc
    option (recompile);

    return 0;