Friday conundrum - How to create a suitable sort order.
Hi
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:
AdditionalActivitySchedule
AdditionalActivityOutstand
AdditionalActivityActive bit true / false if there is one Active
AdditionalActivitySchedule
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
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:
AdditionalActivitySchedule
AdditionalActivityOutstand
AdditionalActivityActive bit true / false if there is one Active
AdditionalActivitySchedule
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
ASKER
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)
ANdy
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)
ANdy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great thanks - works fine. Just couldn't get my head around the logic.
Andy
Andy
>>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.
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.
Thanks for the grade, good luck with your project. -Jim
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 :-)
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.
ASKER
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.
Andy
I'm in wet / raining Worcester UK waiting for beer o'clock.
Andy
@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?
https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
I like simple solutions to complex problems. LOL
Included a screen shot of how I was thinking the data could be sorted...
2015-10-30_0917_SampleUnpivot.png
https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
I like simple solutions to complex problems. LOL
Included a screen shot of how I was thinking the data could be sorted...
2015-10-30_0917_SampleUnpivot.png
@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.
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:
However, I agree with your statement:
Regards
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...
Regards
@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..'
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 : -)
Looking at the question it's not clear how 'suitable sort order' is defined.