Форум программистов [Powered by Invision Power Board]

Код

  INSERT INTO @tblSurveysByRunDate 
  SELECT  
   p.RunDate, 
   coalesce(cf.SurveyTypeName, ma.ActionAbbr), cp.QuestCode, cp.GroupCode, Sum(1), 
   AVG(Case wrn.QuestionValue
        WHEN 1 THEN cs.Scale1
        WHEN 2 THEN cs.Scale2
        WHEN 3 THEN cs.Scale3
        WHEN 4 THEN cs.Scale4
        WHEN 5 THEN cs.Scale5
        WHEN 6 THEN cs.Scale6
        WHEN 7 THEN cs.Scale7
        WHEN 8 THEN cs.Scale8
        WHEN 9 THEN cs.Scale9
        WHEN 10 THEN cs.Scale10
    END) AS Rate, 
    COALESCE((SELECT SurveyTypeID FROM MeetingScope..SurveyType (nolock) WHERE SurveyTypeName=cf.SurveyTypeName) , ma.ActionOrder )
    FROM SQSDB.dbo.PacketID p
        INNER JOIN DAYScope.dbo.DailyPacketID dp  (nolock) ON
            (p.pid = dp.sqsdb_pid)
        INNER JOIN DAYScope.dbo.PointInTimeDPIDs pdp  (nolock) ON
            (dp.dpid = pdp.dpid)
        INNER JOIN surveyfrontend.dbo.WebResult wr  (nolock) ON
            (pdp.WebResultID = wr.WebResultID)
        INNER JOIN MEETINGScope.dbo.MeetingHistory mh  (nolock)  ON 
            mh.WebResultID = wr.WebResultID
        INNER JOIN MEETINGScope.dbo.MeetingActions ma  (nolock)  ON                   -- We have to join out to MeetingActions to see if IsSurvey = 1, or else we get dupes because WebResult is also stored for flash reports
            mh.MeetingActionID = ma.MeetingActionID AND
            ma.IsSurvey = 1
        INNER JOIN SQSDB.dbo.CardFormat cf  (nolock) ON 
            (wr.PodProjectID = cf.PodProjectID and wr.Cardset = cf.Cardset)
        INNER JOIN SQSDB.dbo.PodProject pp  (nolock) ON (
            wr.PodProjectID = pp.PodProjectID)
        INNER JOIN surveyfrontend.dbo.WebResultN wrn  (nolock) ON 
            (wr.WebResultID = wrn.WebResultID)
        INNER JOIN SQSDB.dbo.CardPositions cp  (nolock) ON 
            (pp.TypeCode = cp.TypeCode and wr.Cardset = cp.Cardset and wrn.Position = cp.Position)
        INNER JOIN SQSDB.dbo.ClientSettings cs  (nolock) ON 
            (cs.ClientCode = @CC and cs.ProjectCode = @PJ)
    WHERE    p.ClientCode    = @CC AND
    p.PropCode         = @PC AND
    p.FacilityCode         = @FC AND
    p.ProjectCode         = @PJ AND
    p.RunDate              >= DateAdd(yy, -2, @RD) AND
    p.RunDate              <= @RD AND
    wr.IsComplete       = 1 AND
    wr.AutoGenerated    = 0 AND
    wrn.QuestionValue !=cs.NA and cp.demographic=0 -- removes the demographic questions
  GROUP BY p.ClientCode, p.PropCode, p.FacilityCode, p.ProjectCode, p.RunDate, cf.SurveyTypeName, ma.ActionAbbr,ma.ActionOrder ,
    cp.GroupCode, cp.QuestCode