All geek questions in one place

Sort week in row format

I have data in a table with two columns weekand amountas shown below

Week                      Amount
--------------------------------
1w - 1m - 2015             1000
4w - 8m - 2015             1000
2w - 2m - 2015             1000
4w - 6m - 2015             1000
3w - 3m - 2015             1000
3w - 10m - 2015            1000
1w - 10m - 2015            1000
3w - 12m - 2015            1000
1w - 6m - 2016             1000
2w - 6m - 2016             1000
3w - 6m - 2016             1000
4w - 6m - 2016             1000
3w - 8m - 2016             1000
2w - 9m - 2016             1000

How can I sort this table by week (like date)?

My desired result:

     Week                    Amount
    --------------------------------
    1w - 1m - 2015             1000
    2w - 2m - 2015             1000
    3w - 3m - 2015             1000
    4w - 6m - 2015             1000
    4w - 8m - 2015             1000
    1w - 10m - 2015            1000
    3w - 10m - 2015            1000
    3w - 12m - 2015            1000
    1w - 6m - 2016             1000
    2w - 6m - 2016             1000
    3w - 6m - 2016             1000
    4w - 6m - 2016             1000
    3w - 8m - 2016             1000
    2w - 9m - 2016             1000
+4
source share
2 answers
Select *
 From  YourTable
 Order by right([Week],4)
         ,cast(substring([Week],charIndex('m',[Week])-2,2) as int)
         ,left([Week],1)

Returns

week            Amount
1w - 1m - 2015  1000
2w - 2m - 2015  1000
3w - 3m - 2015  1000
4w - 6m - 2015  1000
4w - 8m - 2015  1000
1w - 10m - 2015 1000
3w - 10m - 2015 1000
3w - 12m - 2015 1000
1w - 6m - 2016  1000
2w - 6m - 2016  1000
3w - 6m - 2016  1000
4w - 6m - 2016  1000
3w - 8m - 2016  1000
2w - 9m - 2016  1000
+5
source

, ( ), , . , , . , , .

:

create table WeekData
(
    WeekStr VARCHAR(16) NOT NULL,
    Amount INT,
    YearVal AS CAST (Right(WeekStr,4) AS SMALLINT) PERSISTED,
    MonthVal AS cast(substring(WeekStr,charIndex('m', WeekStr)-2,2) as SMALLINT),
    WeekVal AS CAST(left(WeekStr,1) AS TINYINT)
)

insert into WeekData (WeekStr, Amount) 
VALUES ('1w - 1m - 2015', 1000),
('4w - 8m - 2015', 1000),
('2w - 2m - 2015', 1000),
('4w - 6m - 2015', 1000),
('3w - 3m - 2015', 1000),
('3w - 10m - 2015', 1000),
('1w - 10m - 2015', 1000),
('3w - 12m - 2015', 1000),
('1w - 6m - 2016', 1000),
('2w - 6m - 2016', 1000),
('3w - 6m - 2016', 1000),
('4w - 6m - 2016', 1000),
('3w - 8m - 2016', 1000),
('2w - 9m - 2016', 1000)
GO


select WeekStr, Amount 
from WeekData
order by YearVal, MonthVal, WeekVal
+1

Source: https://habr.com/ru/post/1665536/

More articles:


All Articles