Organize Period column in SQL in ascending or descending form

Asked

Viewed 129 times

0

Staff I have the following question/problem

I’m doing a select on some accounts and organized by the name of the file, but I need to organize for the period but I’m not able to because it is not a single date. I cannot use the date of their creation.

My select:

SELECT DISTINCT Periodo, Nome_Arquivo, Tel FROM Arquivos WHERE Id_Cliente = 999 ORDER BY Periodo

and the result is this:

23/01/2016 até 22/02/2016   teste73xczx.txt 11 9999-9999
23/01/2017 até 22/02/2017   teste1773zx.txt 11 9999-9999
23/02/2016 até 22/03/2016   testezxczxc.txt 11 9999-9999
23/03/2016 até 22/04/2016   testezxczx.txt  11 9999-9999

I’d like to let it grow but even put, so it would look like this

23/01/2016 até 22/02/2016   teste73xczx.txt 11 9999-9999
23/02/2016 até 22/03/2016   testezxczxc.txt 11 9999-9999
23/03/2016 até 22/04/2016   testezxczx.txt  11 9999-9999
23/01/2017 até 22/02/2017   teste1773zx.txt 11 9999-9999

someone would have some idea?

Perhaps with more example, the total id result: Periods:

23/01/2016 até 22/02/2016
23/01/2017 até 22/02/2017
23/02/2016 até 22/03/2016
23/03/2016 até 22/04/2016
23/04/2016 até 22/05/2016
23/05/2016 até 22/06/2016
23/06/2016 até 22/07/2016
23/07/2016 até 22/08/2016
23/08/2016 até 22/09/2016
23/09/2016 até 22/10/2016
23/10/2016 até 22/11/2016
23/11/2016 até 22/12/2016
23/12/2016 até 22/01/2017
sql query
  • 1

    Idea: Create an initial data_column and another final data_being both timestamp and make a script to scan all rows of this table and fill these two columns in the format yyyy-mm-dd 00:00:00 with the values taken from the string of the column Period(you can use some blasts to get the pieces). After normalizing this table you will be able to sort by date.

  • You want to sort by the number of days between the growing dates?

  • @Murillogoulart I want to organize the way the second result was

  • What is the criterion for saying that one period comes before the other?

  • I asked because even seeing it is difficult to understand the logic.

  • there are several billing files, and each file has a period I’m just demonstrating the result of 4, but there are several records with the reported periods so it returns me all briefly by distinct however the period is not ordered precise that the final date is ordered 09/2015 10/2015 01/2016 02/2016, but the year is wrong in the example I demonstrated

  • I put the whole example of how it’s getting

Show 2 more comments

1 answer

0

I separated the period date by month and by year so got an organized table construction 

 SELECT DISTINCT Periodo, Nome_Arquivo,
    LTRIM(SUBSTRING(Periodo,19,2)) as MesFinal,
    LTRIM(SUBSTRING(Periodo,22,4)) as AnoFinal,
    Tel FROM Vox_Arquivos WHERE Id_Cob_Clientes = 9999 order by AnoFinal desc,MesFinal desc

outworking

23/01/2017 até 22/02/2017
23/12/2016 até 22/01/2017
23/11/2016 até 22/12/2016
23/10/2016 até 22/11/2016
23/09/2016 até 22/10/2016
23/08/2016 até 22/09/2016
23/07/2016 até 22/08/2016

The part of the idea of separation arose based on the comment of Antonio Alexandre(First comment of the question), thank you!

Browser other questions tagged sql query

You are not signed in. Login or sign up in order to post.