Friday conundrum - How to create a suitable sort order.


SQL Server 2008

I have 3 rows in a data table depending on the data I show suitable icons in a grid, the logic for this is a series of if thens in the code behind,

There is now a requirement to sort on these icons to group them together, Its just a simple column sort, but the question is I need a single column of data to sort on.

This is a sample of the data:

AdditionalActivityScheduled int (Number of activities)
AdditionalActivityOutstanding int (Number of above - completed ones
AdditionalActivityActive bit true / false if there is one Active

AdditionalActivityScheduled  AdditionalActivityOutstanding   AdditionalActivityActive
1                                                                                1                                            1
1                                                                                 0                                           0
3                                                                              2                                             1

I can't think of a suitable way to munge all the above into a single column to sort on.

Once done I can rewrite the if / then statements in the code behind to use a select case on this new column, which will make the code simper.

Andy GreenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
Give us a sample data set of the desired sorting, and the logic behind it.
Looking at the question it's not clear how 'suitable sort order' is defined.
Andy GreenAuthor Commented:
Thanks JIm

You are right not enough info does this help?

From OP:

Row 1 - 1,1,1 this is 1 scheduled, 1 outstanding and 1 Active this show the Active Icon(1)
Row 2 - 1,0,0 this is 1 scheduled, 0 outstanding and 0 Active this show the All complete Icon(2)
Row 3 - 3,2,1 this is 3 scheduled, 2 outstanding and 0 Active this show the Send To Icon(3)

The logic is like this:
Scheduled and outstanding show the Send To Icon(3)
If anyone is active show the Active Icon(1)
If Outstanding = 0 show the All complete Icon(2)

Jim HornSQL Server Data DudeCommented:
Still not getting the relationship between icons and sort, but based on the logic it'll be a CASE block like this...
   WHEN Scheduled=1 AND Outstanding=1 AND Active=1 THEN 1 -- if all 1 then assign value of 1
   WHEN Active = 1 THEN 2  --  if Active and not the above then assign a value of 2
   WHEN Outstanding = 0 THEN 3  -- If not outstanding and not the above then 3
   ELSE 4 -- all other

Open in new window

You can include the above in the SELECT clause and return the value (give it a column name after END), or include the above in an ORDER BY clause to sort by that logic.

For a more complete tutorial on CASE blocks check out my article SQL Server CASE Solutions.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Andy GreenAuthor Commented:
Great thanks - works fine. Just couldn't get my head around the logic.

awking00Information Technology SpecialistCommented:
>>Row 3 - 3,2,1 this is 3 scheduled, 2 outstanding and 0 Active this show the Send To Icon(3)<<
It seems to me that this should be 3 scheduled, 2 outstanding and 1 Active

>>Scheduled and outstanding show the Send To Icon(3)
 If anyone is active show the Active Icon(1)
 If Outstanding = 0 show the All complete Icon(2)<<

So what takes precedence? Because of the conflict I see for Row 3, it's difficult to tell. If any active, then Icon(1), but row 3 seems to be active.
Jim HornSQL Server Data DudeCommented:
Thanks for the grade, good luck with your project.  -Jim
awking00Information Technology SpecialistCommented:
I was really confused before but, now that I see the solution, I understand what was needed. I'm glad Jim answered so quickly, so I didn't have to look like a  complete idiot :-)
Jim HornSQL Server Data DudeCommented:
I'm glad my answer helped you, as I'm currently sitting in PASS Summit 2015 in Seattle in a Spark SQL class, and divided attention span can be a b*tch.
Andy GreenAuthor Commented:
Have just changed it a bit as I don't need all 3 in the first CASE. Couldn't see the wood for the trees.

I'm in wet / raining Worcester UK waiting for beer o'clock.

@Jim Horn - Before seeing your answer I was considering recommending Pivot/Unpivot like an example as seen on TechNet. Do you think it's overkill for what Andy was trying to accomplish?

I like simple solutions to complex problems. LOL

Included a screen shot of how I was thinking the data could be sorted...
Jim HornSQL Server Data DudeCommented:
@Lajuan - My first reaction to your comment is that you posted a 'blind link', where the comment does not answer the question other than a just 'go see this link'.   When the link goes bad, as they always do, now EE has a comment without value.

Aside from that I didn't immediately equate the requirements as stated to utilizing a PIVOT, as I didn't see the value of a separate set, so I stuck with a customizable CASE block figuring if nothing else it would either be 'good enough' or flush out more requirements.
@Jim Horn - Okay, Thanks.

Regarding the TechNet link I read the following comment:
The syntax for PIVOT provides is simpler and more readable than the syntax that may otherwise be specified in a complex series of SELECT...CASE statements. For a complete description of the syntax for PIVOT, see...

However, I agree with your statement:
I didn't immediately equate the requirements as stated to utilizing a PIVOT...

Jim HornSQL Server Data DudeCommented:
@Lajuan - Correct, but again that quote was in the link, and not in the comment.   If/when the link goes bad, a user would not be able to navigate from the comment to that page, and view the quote.

This may not be relevant now as the link works, but fast forward five years from now when another member has the same problem, searches and finds this question, reads it, and now because of the bad link the question does not contain an answer because it's in a bad link.   Add to that Google penalizes EE for having these questions, which affects their SEO rankings, which they deeply care about.

Also it's generally accepted that a link-only answer is viewed as inferior, as in effect it requires the user to spend extra work fishing through the link to get the answer.  I have some *unofficial color commentary* on that in ​Top 10 Ways to Ask Better Questions, scroll way down to 'Super Bonus Material:  When you start answering questions please avoid these..'
@Jim Horn - Go it : -)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.