StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCan this be cleaned up or condensed any?
text
Body
copied!<p>I thought about using a <code>CASE</code> statement to clean up some of up, right now it runs in about 10 seconds. Looking to condense that down as well as the redundant code calls. Wondering if there is a better way than a <code>CASE</code>.</p> <p>There are 3 separate blocks of code. Each block does almost the exact same thing aside from assigning a color depending on how many days it has been in the queue.</p> <p>Green Anything less than 1 day. Yellow anything greater than 1 day but less than 3 days. Red anything greater than 3 days.</p> <pre><code>select m.number, 'status1', 'Green', datediff(day, (select max(datechanged) from statushistory where accountid = m.number), getdate()) from master m with (nolock) inner join customer c with (nolock) on m.customer = c.customer where m.status = 'status1' and datediff(day, (select max(sh.datechanged) from statushistory sh where accountid = m.number), getdate()) <= 1 and qlevel < 998 and (m.desk not like 'ATY%') and (isnull(m.link,0) = 0 or m.linkdriver = 1) and m.desk not in ('param1','param2','param3','param4','param5','param6','param7','param8','param9','param10','param11','param12') select m.number, 'status1', 'Yellow', datediff(day, (select max(datechanged) from statushistory where accountid = m.number), getdate()) from master m with (nolock) inner join customer c with (nolock) on m.customer = c.customer where m.status = 'status1' and datediff(day, (select max(sh.datechanged) from statushistory sh where accountid = m.number), getdate()) between 1 and 2 and qlevel < 998 and (m.desk not like 'ATY%') and (isnull(m.link,0) = 0 or m.linkdriver = 1) and m.desk not in ('param1','param2','param3','param4','param5','param6','param7','param8','param9','param10','param11','param12') select m.number, 'status1', 'Red', datediff(day, (select max(datechanged) from statushistory where accountid = m.number), getdate()) from master m with (nolock) inner join customer c with (nolock) on m.customer = c.customer where m.status = 'status1' and datediff(day, (select max(sh.datechanged) from statushistory sh where accountid = m.number), getdate()) >= 3 and qlevel < 998 and (m.desk not like 'ATY%') and (isnull(m.link,0) = 0 or m.linkdriver = 1) and m.desk not in ('param1','param2','param3','param4','param5','param6','param7','param8','param9','param10','param11','param12') </code></pre> <p>EDIT #1</p> <pre><code>select m.number, 'status1', CASE WHEN datediff(day, (select max(sh.datechanged) from statushistory sh where accountid = m.number), getdate()) <= 1 THEN 'Green' WHEN datediff(day, (select max(sh.datechanged) from statushistory sh where accountid = m.number), getdate()) between 1 and 3 THEN 'Yellow' WHEN datediff(day, (select max(sh.datechanged) from statushistory sh where accountid = m.number), getdate()) > 3 THEN 'Red' END , datediff(day, (select max(datechanged) from statushistory where accountid = m.number), getdate()) from master m with (nolock) inner join customer c with (nolock) on m.customer = c.customer where m.status = 'status1' --and datediff(day, (select max(sh.datechanged) from statushistory sh where accountid = m.number), getdate()) <= 1 and qlevel < 998 and (m.desk not like 'ATY%') and (isnull(m.link,0) = 0 or m.linkdriver = 1) and m.desk not in ('param','param','param','param','param','param','param','param','param','param','param','param') </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload