OR (bfblue_data.Q5 > 3) and bfblue_data.contact = 0 and bfblue_data.updateddate > getdate()

I'm not extremely familiar with SQL syntax and stuff but maybe we can work through the logical aspect.

What's in Q5, Contact and updatedate? Also what does GetDate() look like?

Aloha,

James

>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()