February 24, 2014 at 1:52 pm
Ok, something slightly about SQL for this one, I guess :-P. Just wondering what sorts of truly terrifying SQL coding other forumgoers might have seen to date.
I'm supporting a vendor software system right now that's coded... Sub-optimally... To put it one way. Today, I heard one of the end-users I share an office with saying "Geez, what's taking this search so long?". Naturally, I used a quick shot of sp_whoisactive, and promptly fell out of my chair.
DDL and sample data, go!
CREATE TABLE #Response
(RpsRespondent varchar(10),RpsQuestion varchar(200),RpsMatrix int, RpsMention int, RpsContent varchar(max),
RpsOpened ntext,RpsType int,projectId int, collectMode int, modifiedBy int, modified datetime)
INSERT INTO #Response
SELECT '0000000101','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000102','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000103','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000104','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000105','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000106','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000107','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000108','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000109','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000110','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000111','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000112','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000113','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000114','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000115','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000116','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000117','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000118','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000119','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000120','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000121','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000122','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000123','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000124','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000125','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000126','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000127','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000128','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000129','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000130','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000131','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000132','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000133','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000134','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000135','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000136','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000137','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000138','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000139','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000140','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000141','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000142','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000143','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000144','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000145','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000146','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000147','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000148','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000149','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000150','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000151','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000152','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000153','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000154','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000155','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000156','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000157','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000158','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000159','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000160','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000161','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000162','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000163','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000164','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000165','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000166','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000167','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000168','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000169','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000170','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000171','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000172','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000173','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000174','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000175','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000176','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000177','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000178','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000179','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000180','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000181','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000182','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000183','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000184','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000185','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000186','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000187','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000188','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000189','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000190','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000191','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000192','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000193','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000194','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000195','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000196','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000197','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000198','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000199','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000200','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000201','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000202','Q5','1','1','27','','0','210577','1','614','Feb 11 2014 1:10AM' UNION ALL
SELECT '0000000202','Q6','1','1','27','SIRUS','0','210577','1','614','Feb 11 2014 1:10AM' UNION ALL
SELECT '0000000202','Q7','1','1','28','','0','210577','1','614','Feb 11 2014 1:10AM' UNION ALL
SELECT '0000000202','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000202','X5','1','1','28','','0','210577','1','614','Feb 11 2014 1:09AM' UNION ALL
SELECT '0000000203','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000204','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000205','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000206','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000207','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000208','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000209','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000210','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000211','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000212','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000213','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000214','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000215','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000216','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000217','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000218','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000219','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000220','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000221','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000222','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000223','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000224','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000225','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000226','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000227','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000228','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000229','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000230','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000231','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000232','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000233','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000234','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000235','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000236','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000237','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000238','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000239','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000240','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000241','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000242','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000243','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000244','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000245','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000246','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000247','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000248','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000249','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000250','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000251','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000252','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000253','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000254','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000255','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000256','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000257','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000258','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000259','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000260','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000261','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000262','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000263','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000264','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000265','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000266','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000267','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000268','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000269','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000270','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000271','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000272','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000273','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000274','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000275','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000276','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000277','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000278','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000279','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000280','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000281','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000282','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000283','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000284','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000285','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000286','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000287','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000288','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000289','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000290','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000291','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000292','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000293','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000294','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000295','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM' UNION ALL
SELECT '0000000296','SGRP','1','1','126','','0','210576','0','91','Feb 10 2014 9:35PM'
Now then, what the user was wanting was: Find the respondents that answered questions X5, Q5, X8, Q8, Q7, or Q6, and the count of how many of those questions they answered. Simple, right?
SELECT RpsRespondent,COUNT(*)
FROM #Response
WHERE RpsQuestion IN('X5','Q5','X8','Q8','Q7','Q6')
GROUP BY RpsRespondent
Done! Certainly the vendor couldn't have strayed that far off for such a simple request, right? Well...
Disclaimer: I have no idea if the forum can even hold this. Here goes...
SELECT * FROM ( SELECT ROW_NUMBER() OVER( ORDER BY ResSeed Asc ) as RowNum, *
FROM ( SELECT distinct ResRespondent,
( select rsppin from respondentpin where rsprespondent=[ResRespondent] ) as BaseRspPin,
ResPhone, ResCodeResult, ResBlocked, ResSeed,
CASE WHEN [ResCallbackDate] > 0 THEN CASE WHEN YEAR([ResCallbackDate]) > 1950 THEN 1 ELSE 0 END ELSE 0 END as CB,
CASE WHEN (SELECT phone FROM DoNotCallList where Phone = Resphone) is null then 0 else 1 end as DNC,
ResCompleted,
IsNull(CASE WHEN (ISNUMERIC(ResIntervCall) = 1) THEN (SELECT [Name] FROM tblObjects_View tblobj
WHERE k_id = ResIntervCall) ELSE '' END,'') as ResIntervCall,
case when ResCallbackDate > 0 then [VXSystem].[dbo].[VX_fn_FromUTC](ResCallbackDate, 13) else null end
AS ResCallbackDate, Location, ResActive,
CASE WHEN (SELECT COUNT(*) FROM [VXSystem].[dbo].[IntWeb_ActiveSessions] iwas
WHERE iwas.CurrentRecord = [ResRespondent] AND iwas.ProjectId = 210575) > 0 then 1 else 0 end as
IsRespondentOnline ,
( ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND
RpsQuestion='X5' AND RpsMatrix = 1 AND RpsMention = 1 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X5' AND
RpsMatrix = 1 AND RpsMention = 2 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response
where RpsRespondent = ResRespondent AND RpsQuestion='X5'
AND RpsMatrix = 1 AND RpsMention = 3 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent
AND RpsQuestion='X5' AND RpsMatrix = 1 AND RpsMention = 4 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X5'
AND RpsMatrix = 1 AND RpsMention = 5 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X5'
AND RpsMatrix = 1 AND RpsMention = 6 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X5'
AND RpsMatrix = 1 AND RpsMention = 7 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X5'
AND RpsMatrix = 1 AND RpsMention = 8 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X5'
AND RpsMatrix = 1 AND RpsMention = 9 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X5'
AND RpsMatrix = 1 AND RpsMention = 10 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X5'
AND RpsMatrix = 1 AND RpsMention = 11 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X5'
AND RpsMatrix = 1 AND RpsMention = 12 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X5'
AND RpsMatrix = 1 AND RpsMention = 13 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X5'
AND RpsMatrix = 1 AND RpsMention = 14 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X5'
AND RpsMatrix = 1 AND RpsMention = 15 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X5'
AND RpsMatrix = 1 AND RpsMention = 16 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X5'
AND RpsMatrix = 1 AND RpsMention = 17 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X5'
AND RpsMatrix = 1 AND RpsMention = 18 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X5'
AND RpsMatrix = 1 AND RpsMention = 19 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X5'
AND RpsMatrix = 1 AND RpsMention = 20 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X5'
AND RpsMatrix = 1 AND RpsMention = 21 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X5'
AND RpsMatrix = 1 AND RpsMention = 22 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X5'
AND RpsMatrix = 1 AND RpsMention = 23 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X5'
AND RpsMatrix = 1 AND RpsMention = 24 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X5'
AND RpsMatrix = 1 AND RpsMention = 25 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X5'
AND RpsMatrix = 1 AND RpsMention = 26 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X5'
AND RpsMatrix = 1 AND RpsMention = 27 AND RpsType=0 ),'') + ' ' ) as [X5] ,
( ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent
AND RpsQuestion='Q5' AND RpsMatrix = 1 AND RpsMention = 1 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent
AND RpsQuestion='Q5' AND RpsMatrix = 1 AND RpsMention = 2 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent
AND RpsQuestion='Q5' AND RpsMatrix = 1 AND RpsMention = 3 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent
AND RpsQuestion='Q5' AND RpsMatrix = 1 AND RpsMention = 4 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent
AND RpsQuestion='Q5' AND RpsMatrix = 1 AND RpsMention = 5 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent
AND RpsQuestion='Q5' AND RpsMatrix = 1 AND RpsMention = 6 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent
AND RpsQuestion='Q5' AND RpsMatrix = 1 AND RpsMention = 7 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent
AND RpsQuestion='Q5' AND RpsMatrix = 1 AND RpsMention = 8 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent
AND RpsQuestion='Q5' AND RpsMatrix = 1 AND RpsMention = 9 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent
AND RpsQuestion='Q5' AND RpsMatrix = 1 AND RpsMention = 10 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent
AND RpsQuestion='Q5' AND RpsMatrix = 1 AND RpsMention = 11 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent
AND RpsQuestion='Q5' AND RpsMatrix = 1 AND RpsMention = 12 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent
AND RpsQuestion='Q5' AND RpsMatrix = 1 AND RpsMention = 13 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent
AND RpsQuestion='Q5' AND RpsMatrix = 1 AND RpsMention = 14 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent
AND RpsQuestion='Q5' AND RpsMatrix = 1 AND RpsMention = 15 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent
AND RpsQuestion='Q5' AND RpsMatrix = 1 AND RpsMention = 16 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent
AND RpsQuestion='Q5' AND RpsMatrix = 1 AND RpsMention = 17 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent
AND RpsQuestion='Q5' AND RpsMatrix = 1 AND RpsMention = 18 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent
AND RpsQuestion='Q5' AND RpsMatrix = 1 AND RpsMention = 19 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent
AND RpsQuestion='Q5' AND RpsMatrix = 1 AND RpsMention = 20 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent
AND RpsQuestion='Q5' AND RpsMatrix = 1 AND RpsMention = 21 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent
AND RpsQuestion='Q5' AND RpsMatrix = 1 AND RpsMention = 22 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent
AND RpsQuestion='Q5' AND RpsMatrix = 1 AND RpsMention = 23 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q5'
AND RpsMatrix = 1 AND RpsMention = 24 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent
AND RpsQuestion='Q5' AND RpsMatrix = 1 AND RpsMention = 25 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent
AND RpsQuestion='Q5' AND RpsMatrix = 1 AND RpsMention = 26 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q5'
AND RpsMatrix = 1 AND RpsMention = 27 AND RpsType=0 ),'') + ' ' ) as [Q5] , (
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q6'
AND RpsMatrix = 1 AND RpsMention = 1 AND RpsType=0 ),'') + ' ' ) as [Q6] , (
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q7'
AND RpsMatrix = 1 AND RpsMention = 1 AND RpsType=0 ),'') + ' ' ) as [Q7] , (
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8'
AND RpsMatrix = 1 AND RpsMention = 1 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8'
AND RpsMatrix = 1 AND RpsMention = 2 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8'
AND RpsMatrix = 1 AND RpsMention = 3 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8'
AND RpsMatrix = 1 AND RpsMention = 4 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8'
AND RpsMatrix = 1 AND RpsMention = 5 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8'
AND RpsMatrix = 1 AND RpsMention = 6 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8'
AND RpsMatrix = 1 AND RpsMention = 7 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent t FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q5'
AND RpsMatrix = 1 AND RpsMention = 14 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q5'
AND RpsMatrix = 1 AND RpsMention = 15 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q5'
AND RpsMatrix = 1 AND RpsMention = 16 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q5'
AND RpsMatrix = 1 AND RpsMention = 17 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q5'
AND RpsMatrix = 1 AND RpsMention = 18 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q5'
AND RpsMatrix = 1 AND RpsMention = 19 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q5'
AND RpsMatrix = 1 AND RpsMention = 20 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q5'
AND RpsMatrix = 1 AND RpsMention = 21 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q5'
AND RpsMatrix = 1 AND RpsMention = 22 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q5'
AND RpsMatrix = 1 AND RpsMention = 23 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q5'
AND RpsMatrix = 1 AND RpsMention = 24 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q5'
AND RpsMatrix = 1 AND RpsMention = 25 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q5'
AND RpsMatrix = 1 AND RpsMention = 26 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q5'
AND RpsMatrix = 1 AND RpsMention = 27 AND RpsType=0 ),'') + ' ' ) as [Q5] , (
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q6'
AND RpsMatrix = 1 AND RpsMention = 1 AND RpsType=0 ),'') + ' ' ) as [Q6] , (
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q7'
AND RpsMatrix = 1 AND RpsMention = 1 AND RpsType=0 ),'') + ' ' ) as [Q7] , (
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8'
AND RpsMatrix = 1 AND RpsMention = 1 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8'
AND RpsMatrix = 1 AND RpsMention = 2 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8'
AND RpsMatrix = 1 AND RpsMention = 3 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8'
AND RpsMatrix = 1 AND RpsMention = 4 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 5 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 6 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 7 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 8 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 9 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 10 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 11 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 12 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 13 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 14 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 15 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 16 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 17 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 18 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 19 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 20 AND RpsType=0 ),'') + ' ' ) as [X8] , (
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 1 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 2 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 3 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 4 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 5 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 6 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 7 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 8 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 9 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 10 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 11 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 12 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 13 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 14 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 15 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 16 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 17 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 18 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 19 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 20 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 21 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 22 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 23 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 24 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 25 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 26 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 27 AND RpsType=0 ),'') + ' ' ) as [Q8]
from ( select resps.*,IsNull(CASE WHEN (ISNUMERIC(ResIntervCall) = 1) THEN (SELECT [Name] FROM tblObjects_View tblobj WHERE k_id = ResIntervCall) ELSE '' END,'') as ResIntervCallName,
case when t0.rpscontent is null then '' else t0.rpscontent end as Q5_1, case when t1.rpscontent is null
then '' else t1.rpscontent end as Q5_2,
case when t2.rpscontent is null then '' else t2.rpscontent end as Q5_3,
case when t3.rpscontent is null then '' else t3.rpscontent end as Q5_4,
case when t4.rpscontent is null then '' else t4.rpscontent end as Q5_5,
case when t5.rpscontent is null then '' else t5.rpscontent end as Q5_6,
case when t6.rpscontent is null then '' else t6.rpscontent end as Q5_7,
case when t7.rpscontent is null then '' else t7.rpscontent end as Q5_8,
case when t8.rpscontent is null then '' else t8.rpscontent end as Q5_9,
case when t9.rpscontent is null then '' else t9.rpscontent end as Q5_10,
case when t10.rpscontent is null then '' else t10.rpscontent end as Q5_11,
case when t11.rpscontent is null then '' else t11.rpscontent end as Q5_12,
case when t12.rpscontent is null then '' else t12.rpscontent end as Q5_13,
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 9 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 10 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 11 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 12 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 13 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 14 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 15 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 16 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 17 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 18 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 19 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='X8' AND RpsMatrix = 1 AND RpsMention = 20 AND RpsType=0 ),'') + ' ' as [X8] ,
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 1 AND RpsType=0 ),'' + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 2 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 3 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 4 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 5 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 6 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 7 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 8 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 9 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 10 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 11 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 12 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 13 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 14 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 15 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 16 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 17 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 18 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 19 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 20 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 21 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 22 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 23 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 24 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8' AND RpsMatrix = 1 AND RpsMention = 25 AND RpsType=0 ),'') + ' ' +
ISNULL(( SELECT RpsContent FROM Response where RpsRespondent = ResRespondent AND RpsQuestion='Q8'
AND RpsMatrix = 1 AND RpsMention = 26 AND RpsType=0 ),'') + ' ' ) as [Q8]
from ( select resps.*,IsNull(CASE WHEN (ISNUMERIC(ResIntervCall) = 1)
THEN (SELECT [Name] FROM tblObjects_View tblobj WHERE k_id = ResIntervCall) ELSE '' END,'')
as ResIntervCallName,
case when t0.rpscontent is null then '' else t0.rpscontent end as Q5_1,
case when t1.rpscontent is null then '' else t1.rpscontent end as Q5_2,
case when t2.rpscontent is null then '' else t2.rpscontent end as Q5_3,
case when t3.rpscontent is null then '' else t3.rpscontent end as Q5_4,
case when t4.rpscontent is null then '' else t4.rpscontent end as Q5_5,
case when t5.rpscontent is null then '' else t5.rpscontent end as Q5_6,
case when t6.rpscontent is null then '' else t6.rpscontent end as Q5_7,
case when t7.rpscontent is null then '' else t7.rpscontent end as Q5_8,
case when t8.rpscontent is null then '' else t8.rpscontent end as Q5_9,
case when t9.rpscontent is null then '' else t9.rpscontent end as Q5_10,
case when t10.rpscontent is null then '' else t10.rpscontent end as Q5_11,
case when t11.rpscontent is null then '' else t11.rpscontent end as Q5_12,
case when t12.rpscontent is null then '' else t12.rpscontent end as Q5_13,
case when t13.rpscontent is null then '' else t13.rpscontent end as Q5_14,
case when t14.rpscontent is null then '' else t14.rpscontent end as Q5_15,
case when t15.rpscontent is null then '' else t15.rpscontent end as Q5_16,
case when t16.rpscontent is null then '' else t16.rpscontent end as Q5_17,
case when t17.rpscontent is null then '' else t17.rpscontent end as Q5_18,
case when t18.rpscontent is null then '' else t18.rpscontent end as Q5_19,
case when t19.rpscontent is null then '' else t19.rpscontent end as Q5_20,
case when t20.rpscontent is null then '' else t20.rpscontent end as Q5_21,
case when t21.rpscontent is null then '' else t21.rpscontent end as Q5_22,
case when t22.rpscontent is null then '' else t22.rpscontent end as Q5_23,
case when t23.rpscontent is null then '' else t23.rpscontent end as Q5_24,
case when t24.rpscontent is null then '' else t24.rpscontent end as Q5_25,
case when t25.rpscontent is null then '' else t25.rpscontent end as Q5_26,
case when t26.rpscontent is null then '' else t26.rpscontent end as Q5_27,
case when t28.rpscontent is null then '' else t28.rpscontent end as X5_1,
case when t29.rpscontent is null then '' else t29.rpscontent end as X5_2,
case when t30.rpscontent is null then '' else t30.rpscontent end as X5_3,
case when t31.rpscontent is null then '' else t31.rpscontent end as X5_4,
case when t32.rpscontent is null then '' else t32.rpscontent end as X5_5,
case when t33.rpscontent is null then '' else t33.rpscontent end as X5_6,
case when t34.rpscontent is null then '' else t34.rpscontent end as X5_7,
case when t35.rpscontent is null then '' else t35.rpscontent end as X5_8,
case when t36.rpscontent is null then '' else t36.rpscontent end as X5_9,
case when t37.rpscontent is null then '' else t37.rpscontent end as X5_10,
case when t38.rpscontent is null then '' else t38.rpscontent end as X5_11,
case when t39.rpscontent is null then '' else t39.rpscontent end as X5_12,
case when t40.rpscontent is null then '' else t40.rpscontent end as X5_13,
case when t41.rpscontent is null then '' else t41.rpscontent end as X5_14,
case when t42.rpscontent is null then '' else t42.rpscontent end as X5_15,
case when t43.rpscontent is null then '' else t43.rpscontent end as X5_16,
case when t44.rpscontent is null then '' else t44.rpscontent end as X5_17,
case when t45.rpscontent is null then '' else t45.rpscontent end as X5_18,
case when t46.rpscontent is null then '' else t46.rpscontent end as X5_19,
case when t47.rpscontent is null then '' else t47.rpscontent end as X5_20,
case when t48.rpscontent is null then '' else t48.rpscontent end as X5_21,
case when t49.rpscontent is null then '' else t49.rpscontent end as X5_22,
case when t50.rpscontent is null then '' else t50.rpscontent end as X5_23,
case when t51.rpscontent is null then '' else t51.rpscontent end as X5_24,
case when t52.rpscontent is null then '' else t52.rpscontent end as X5_25,
case when t53.rpscontent is null then '' else t53.rpscontent end as X5_26,
case when t54.rpscontent is null then '' else t54.rpscontent end as X5_27,
case when t27.rpscontent is null then '' else t27.rpscontent end as Q6_1
from respondent resps WITH(NOLOCK)
left outer join response t0 WITH(NOLOCK) on t0.rpsquestion='Q5' and t0.rpsrespondent=resrespondent and t0.rpstype=0 and t0.rpsmention=1
left outer join response t1 WITH(NOLOCK) on t1.rpsquestion='Q5' and t1.rpsrespondent=resrespondent and t1.rpstype=0 and t1.rpsmention=2
left outer join response t2 WITH(NOLOCK) on t2.rpsquestion='Q5' and t2.rpsrespondent=resrespondent and t2.rpstype=0 and t2.rpsmention=3
left outer join response t3 WITH(NOLOCK) on t3.rpsquestion='Q5' and t3.rpsrespondent=resrespondent and t3.rpstype=0 and t3.rpsmention=4
left outer join response t4 WITH(NOLOCK) on t4.rpsquestion='Q5' and t4.rpsrespondent=resrespondent and t4.rpstype=0 and t4.rpsmention=5
left outer join response t5 WITH(NOLOCK) on t5.rpsquestion='Q5' and t5.rpsrespondent=resrespondent and t5.rpstype=0 and t5.rpsmention=6
left outer join response t6 WITH(NOLOCK) on t6.rpsquestion='Q5' and t6.rpsrespondent=resrespondent and t6.rpstype=0 and t6.rpsmention=7
left outer join response t7 WITH(NOLOCK) on t7.rpsquestion='Q5' and t7.rpsrespondent=resrespondent and t7.rpstype=0 and t7.rpsmention=8
left outer join response t8 WITH(NOLOCK) on t8.rpsquestion='Q5' and t8.rpsrespondent=resrespondent and t8.rpstype=0 and t8.rpsmention=9
left outer join response t9 WITH(NOLOCK) on t9.rpsquestion='Q5' and t9.rpsrespondent=resrespondent and t9.rpstype=0 and t9.rpsmention=10
left outer join response t10 WITH(NOLOCK) on t10.rpsquestion='Q5' and t10.rpsrespondent=resrespondent and t10.rpstype=0 and t10.rpsmention=11
left outer join response t11 WITH(NOLOCK) on t11.rpsquestion='Q5' and t11.rpsrespondent=resrespondent and t11.rpstype=0 and t11.rpsmention=12
left outer join response t12 WITH(NOLOCK) on t12.rpsquestion='Q5' and t12.rpsrespondent=resrespondent and t12.rpstype=0 and t12.rpsmention=13
left outer join response t13 WITH(NOLOCK) on t13.rpsquestion='Q5' and t13.rpsrespondent=resrespondent and t13.rpstype=0 and t13.rpsmention=14
left outer join response t14 WITH(NOLOCK) on t14.rpsquestion='Q5' and t14.rpsrespondent=resrespondent and t14.rpstype=0 and t14.rpsmention=15
left outer join response t15 WITH(NOLOCK) on t15.rpsquestion='Q5' and t15.rpsrespondent=resrespondent and t15.rpstype=0 and t15.rpsmention=16
left outer join response t16 WITH(NOLOCK) on t16.rpsquestion='Q5' and t16.rpsrespondent=resrespondent and t16.rpstype=0 and t16.rpsmention=17
left outer join response t17 WITH(NOLOCK) on t17.rpsquestion='Q5' and t17.rpsrespondent=resrespondent and t17.rpstype=0 and t17.rpsmention=18
left outer join response t18 WITH(NOLOCK) on t18.rpsquestion='Q5' and t18.rpsrespondent=resrespondent and t18.rpstype=0 and t18.rpsmention=19
left outer join response t19 WITH(NOLOCK) on t19.rpsquestion='Q5' and t19.rpsrespondent=resrespondent and t19.rpstype=0 and t19.rpsmention=20
left outer join response t20 WITH(NOLOCK) on t20.rpsquestion='Q5' and t20.rpsrespondent=resrespondent and t20.rpstype=0 and t20.rpsmention=21
left outer join response t21 WITH(NOLOCK) on t21.rpsquestion='Q5' and t21.rpsrespondent=resrespondent and t21.rpstype=0 and t21.rpsmention=22
left outer join response t22 WITH(NOLOCK) on t22.rpsquestion='Q5' and t22.rpsrespondent=resrespondent and t22.rpstype=0 and t22.rpsmention=23
left outer join response t23 WITH(NOLOCK) on t23.rpsquestion='Q5' and t23.rpsrespondent=resrespondent and t23.rpstype=0 and t23.rpsmention=24
left outer join response t24 WITH(NOLOCK) on t24.rpsquestion='Q5' and t24.rpsrespondent=resrespondent and t24.rpstype=0 and t24.rpsmention=25
left outer join response t25 WITH(NOLOCK) on t25.rpsquestion='Q5' and t25.rpsrespondent=resrespondent and t25.rpstype=0 and t25.rpsmention=26
left outer join response t26 WITH(NOLOCK) on t26.rpsquestion='Q5' and t26.rpsrespondent=resrespondent and t26.rpstype=0 and t26.rpsmention=27
left outer join response t27 WITH(NOLOCK) on t27.rpsquestion='Q6' and t27.rpsrespondent=resrespondent and t27.rpstype=0 and t27.rpsmention=1
left outer join response t28 WITH(NOLOCK) on t28.rpsquestion='X5' and t28.rpsrespondent=resrespondent and t28.rpstype=0 and t28.rpsmention=1
left outer join response t29 WITH(NOLOCK) on t29.rpsquestion='X5' and t29.rpsrespondent=resrespondent and t29.rpstype=0 and t29.rpsmention=2
left outer join response t30 WITH(NOLOCK) on t30.rpsquestion='X5' and t30.rpsrespondent=resrespondent and t30.rpstype=0 and t30.rpsmention=3
left outer join response t31 WITH(NOLOCK) on t31.rpsquestion='X5' and t31.rpsrespondent=resrespondent and t31.rpstype=0 and t31.rpsmention=4
left outer join response t32 WITH(NOLOCK) on t32.rpsquestion='X5' and t32.rpsrespondent=resrespondent and t32.rpstype=0 and t32.rpsmention=5
left outer join response t33 WITH(NOLOCK) on t33.rpsquestion='X5' and t33.rpsRespondent=resrespondent AND RpsQuestion='Q8'
AND RpsMatrix = 1 AND RpsMention = 11 AND RpsType=0))
WHERE source.RowNum between 0 AND 50))
... Yep. Notice the WHERE on that last line? The vendor software does things in batches of 50, for page-by-page results. There were about 20,000 records in this project. So it ran 400 times.
In the end, this giant blob of wonder took about two hours to finish running, and returned the same results as my sample data. One row.
I'd seen amalgamations of sheer absurdity out of the software before, but never anything like this. I think I lost a bit of coding proficiency just looking at it :blink:
EDIT: Also, the code in there won't even run. I'm not sure why or how, but sp_whoisactive pulled back something that won't compile; there's 2300-ish left parentheses in the coding, but only 2200-ish right ones. The coding ran to completion, so I'm not entirely sure what happened there. I really wanted to see the execution plan for this, so I'm slightly disappointed by that :hehe:
- 😀
February 24, 2014 at 2:16 pm
ACK!!! That is hideous!!! I love the NOLOCK hints on some of the queries and not others. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 24, 2014 at 2:21 pm
Oh yes :-D. There's exactly 217 uses of NOLOCK in that query. I have no idea why. Well, maybe... Considering how long this thing ran, and how mangled it is, there was probably a desperate attempt at hitting the "go faster!" button. Many times. As many as humanly possible. Then a few dozen more. The agony!
EDIT: Oh, crud, I just realized this isn't the actual query! I counted 217 NOLOCKs in the version I pulled from sp_whoisactive, and I used some search-replace to chop up the query to make it slightly more readable (it was originally all one line), but checking it now returns 35. Not sure how I amputated some of the query in the process, but I could post the original one-liner if anyone's filled with macabre fascination 😛
- 😀
February 24, 2014 at 2:25 pm
hisakimatama (2/24/2014)
Oh yes :-D. There's exactly 217 uses of NOLOCK in that query. I have no idea why. Well, maybe... Considering how long this thing ran, and how mangled it is, there was probably a desperate attempt at hitting the "go faster!" button. Many times. As many as humanly possible. Then a few dozen more. The agony!
And since they are it they might as well skip about half of the dozens and dozens of nearly identical queries to the same table. I mean if the "go fast" button works in some places we should maintain our inconsistency by not using it everywhere. 😀
I feel your pain. I have worked with some systems like that.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 24, 2014 at 2:40 pm
Haha, yep. The others in my office made a similar remark; "The software is consistent. It's consistently inconsistent!". Which is true; some of the SQL in it is passable (though still littered with NOLOCK), other bits are like the above. Mostly, it does what it's supposed to in a sorta-kinda-maybe-alright way, but the end users are endlessly frustrated with it because some functions will run in 10 seconds or less, and others can take minutes to half an hour (or more) for rather simple things.
Thankfully, while I don't have the permissions to alter their coding and fix things like this, I can use SSRS and intranet pages so the users don't have to use the software at all :-P. The business seems like it's going to be moving to a different vendor soon, also, which is something they hadn't considered before (but complaints in-house and at partner companies have pushed them to do). Still, the very nature of the coding is amazing. Not in a good way, but amazing!
- 😀
February 24, 2014 at 5:11 pm
I tell people that modern hardware is so powerful that it can overcome almost any bad code, then they write something like that!
Proves me wrong, everytime
February 25, 2014 at 4:58 am
That vendor needs to be burned at the stake.
And then we'll scatter the ashes at a cross-roads.
And then sow the ground with salt.
Although, I've seen scarier stuff generated by ORM tools, especially nHibernate.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply