0

In a Sql Server Reporting Services, I have a dataset I am using as a parameter for a report.

select distinct (integer#) From table order by integer#.

These integer numbers are a positive and negative number of years and months. For example this month would be 201602 and -201602. Last month would be 201601 and -201601. I have 78 selection going back several years with the - and + of each month.

The problem is I would like to order these parameter selections one after the other. Exactly like thi example:

201602 -201602 201601 -21601

Right now they are listing as -201602 -201601 -201512 and the last selection (78th one) at the bottom of the list is 201602

The problem lies in trying to do this so the user who selects the two selections for each yearmonth doesn't have to scroll From the top negative selections to the bottom of the positive selections to pick the positive and negatives for that particular yearmonth...For example right now I have all the negative numbers on top and all the positive numbers on the bottom due to the ascending order in the select statement above. If I create the select statement I showed above in descending order then the positive selections are on top and the negative to the year and month are on the bottom of the 78 selections.

Is there an easy way to get the negative and positive yearmonth one after another?

user2679045
  • 5
  • 3
  • What if you created a separate sort column with the Interger# and trim that - in front of the negative ones then sort by that sort column. It should place them next to each other. – NewGuy Feb 03 '16 at 13:51
  • Never thought of that. The table is part of a commercial software I dare not touch (I'm new to this), but if I can figure a way to create a view with those distinct (integers) . Now just have to figure how to do that. I'm not that comfortable with sql yet. But I guess that is what Google is for. Thanks for the idea. – user2679045 Feb 05 '16 at 15:20
  • Could dump the results into temp table and add the column to that. – NewGuy Feb 05 '16 at 15:44
  • Way to advanced for me. Not sure how to do that yet. – user2679045 Feb 10 '16 at 18:40
  • See this for a start, http://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table – NewGuy Feb 10 '16 at 18:49

2 Answers2

0

Use the absolute value function when selecting your data.

SELECT my_integer
FROM my_table
ORDER BY ABS(my_integer)

This will order your data by integer magnitude.

Jesse Potter
  • 827
  • 5
  • 20
  • Tried it but due to the select having a distinct clause I kept getting an error. After google-ing some more, I finally ended up using this statement which worked. SELECT postwho FROM CombinedChargesCreditsWithChargeCodes GROUP BY postwho ORDER BY ABS(postwho) DESC – user2679045 Feb 10 '16 at 19:12
0

This ended up being the solution.

SELECT postwho FROM CombinedChargesCreditsWithChargeCodes WHERE (postwho) GROUP BY postwho ORDER BY ABS(postwho) DESC

Thanks for the idea Jesse didn't realize the ABS function might work. But due to the Distinct in the select I kept getting erros. I googled some more and found a way around the distinct error buy using group by.

user2679045
  • 5
  • 3