I have this Big Ugly SQL Statement that works except for one problem. When I added the following line to the end "and bfblue_data.updateddate > getdate()" I was expecting not to return any records. But you guessed it I retrieved them all. So if anybody would like to wade through this and make any suggestions I would appreciate it very much..


I have been backwards and forwards over this, please excuse the ugliness of the statement but it seemed to work until they wanted to add a date parameted to the statement. If there is a better cleaner way to write this I would love to hear about it. I have no control over the data as it's not mine so I cannot change it's layout etc.

THanks



SELECT distinct SUBSTRING((CASE WHEN (CASE WHEN q8a >= 4 THEN 1 ELSE 0 END)
+ (CASE WHEN q8b >= 4 THEN 1 ELSE 0 END) + (CASE WHEN q8c >= 4 THEN 1 ELSE 0 END)
+ (CASE WHEN q8d >= 4 THEN 1 ELSE 0 END) >= 2 THEN 'Q8,' ELSE '' END) +
(CASE WHEN (CASE WHEN q9a >= 4 THEN 1 ELSE 0 END) +
(CASE WHEN q9b >= 4 THEN 1 ELSE 0 END) + (CASE WHEN q9c >= 4 THEN 1 ELSE 0 END) +
(CASE WHEN q9d >= 4 THEN 1 ELSE 0 END) >= 2 THEN 'Q9,' ELSE '' END) +
(CASE WHEN (CASE WHEN rating_a >= 4 THEN 1 ELSE 0 END) +
(CASE WHEN rating_b >= 4 THEN 1 ELSE 0 END) + (CASE WHEN rating_c > 4 THEN 1 ELSE 0 END) +
(CASE WHEN rating_d >= 4 THEN 1 ELSE 0 END) + (CASE WHEN rating_e > 4 THEN 1 ELSE 0 END) +
(CASE WHEN rating_f >= 4 THEN 1 ELSE 0 END) + (CASE WHEN rating_g > 4 THEN 1 ELSE 0 END) +
(CASE WHEN rating_h >= 4 THEN 1 ELSE 0 END) + (CASE WHEN rating_i > 4 THEN 1 ELSE 0 END) +
(CASE WHEN rating_j >= 4 THEN 1 ELSE 0 END) + (CASE WHEN rating_k > 4 THEN 1 ELSE 0 END) +
(CASE WHEN rating_l >= 4 THEN 1 ELSE 0 END) + (CASE WHEN rating_m > 4 THEN 1 ELSE 0 END) +
(CASE WHEN rating_n >= 4 THEN 1 ELSE 0 END) >= 4 THEN 'Q17,' ELSE '' END) +
(CASE WHEN bfblue_data.Q5 > 3 THEN 'Q5,' ELSE '' END), 1,
LEN((CASE WHEN (CASE WHEN q8a >= 4 THEN 1 ELSE 0 END) + (CASE WHEN q8b >= 4 THEN 1 ELSE 0 END)
+ (CASE WHEN q8c >= 4 THEN 1 ELSE 0 END) + (CASE WHEN q8d >= 4 THEN 1 ELSE 0 END) >= 2 THEN 'Q8,' ELSE '' END)
+ (CASE WHEN (CASE WHEN q9a >= 4 THEN 1 ELSE 0 END) + (CASE WHEN q9b >= 4 THEN 1 ELSE 0 END)
+ (CASE WHEN q9c >= 4 THEN 1 ELSE 0 END) + (CASE WHEN q9d >= 4 THEN 1 ELSE 0 END) >= 2 THEN 'Q9,' ELSE '' END) +
(CASE WHEN (CASE WHEN rating_a >= 4 THEN 1 ELSE 0 END) + (CASE WHEN rating_b >= 4 THEN 1 ELSE 0 END) +
(CASE WHEN rating_c >= 4 THEN 1 ELSE 0 END) + (CASE WHEN rating_d >= 4 THEN 1 ELSE 0 END) +
(CASE WHEN rating_e >= 4 THEN 1 ELSE 0 END) + (CASE WHEN rating_f >= 4 THEN 1 ELSE 0 END) +
(CASE WHEN rating_g >= 4 THEN 1 ELSE 0 END) + (CASE WHEN rating_h >= 4 THEN 1 ELSE 0 END) +
(CASE WHEN rating_i >= 4 THEN 1 ELSE 0 END) + (CASE WHEN rating_j >= 4 THEN 1 ELSE 0 END) +
(CASE WHEN rating_k >= 4 THEN 1 ELSE 0 END) + (CASE WHEN rating_l >= 4 THEN 1 ELSE 0 END) +
(CASE WHEN rating_m >= 4 THEN 1 ELSE 0 END) + (CASE WHEN rating_n >= 4 THEN 1 ELSE 0 END) >= 4 THEN 'Q17,' ELSE '' END) +
(CASE WHEN bfblue_data.Q5 > 3 THEN 'Q5,' ELSE '' END))-1 ) AS Problem, bfblue_data.FAIR_ID
, 'http://www.something.com/images/page1/' + RTRIM(SUBSTRING(bfblue_data.page_1, CHARINDEX('\', bfblue_data.page_1, 27) + 1, 50)) AS url,
'http://www.something.com/images/page2/' + RTRIM(SUBSTRING(bfblue_data.page_2, CHARINDEX('\', bfblue_data.page_2, 27) + 1, 50)) AS url2,
bfblue_data.updateddate FROM bfblue_data
left JOIN master ON bfblue_data.FAIR_ID = master.FairNumber
WHERE ((CASE WHEN q8a >= 4 THEN 1 ELSE 0 END) + (CASE WHEN q8b >=4 THEN 1 ELSE 0 END) +
(CASE WHEN q8c >=4 THEN 1 ELSE 0 END) + (CASE WHEN q8d >=4 THEN 1 ELSE 0 END) >= 2)
OR ((CASE WHEN q9a >= 4 THEN 1 ELSE 0 END) + (CASE WHEN q9b >= 4 THEN 1 ELSE 0 END) +
(CASE WHEN q9c >= 4 THEN 1 ELSE 0 END) + (CASE WHEN q9d >= 4 THEN 1 ELSE 0 END) >= 2)
OR ((CASE WHEN rating_a >= 4 THEN 1 ELSE 0 END)
+ (CASE WHEN rating_b >= 4 THEN 1 ELSE 0 END) + (CASE WHEN rating_c >= 4 THEN 1 ELSE 0 END) +
(CASE WHEN rating_d >= 4 THEN 1 ELSE 0 END) + (CASE WHEN rating_e >= 4 THEN 1 ELSE 0 END) +
(CASE WHEN rating_f >= 4 THEN 1 ELSE 0 END) + (CASE WHEN rating_g >= 4 THEN 1 ELSE 0 END) +
(CASE WHEN rating_h >= 4 THEN 1 ELSE 0 END) + (CASE WHEN rating_i >= 4 THEN 1 ELSE 0 END) +
(CASE WHEN rating_j >= 4 THEN 1 ELSE 0 END) + (CASE WHEN rating_k >= 4 THEN 1 ELSE 0 END) +
(CASE WHEN rating_l >= 4 THEN 1 ELSE 0 END) + (CASE WHEN rating_m >= 4 THEN 1 ELSE 0 END) +
(CASE WHEN rating_n >= 4 THEN 1 ELSE 0 END) > 4) OR (bfblue_data.Q5 > 3) and bfblue_data.contact = 0 and bfblue_data.updateddate > getdate()