نحوه مرتب سازی ستون از نوع رشته در sql

[rezayeman]

با سلام خدمت دوستان عزیز

من یک کوئری با کد زیر نوشتم

select ValidDate, valid, defective, Repair, ISNULL(Rework,0) as Rework
from (select dbo.GetShamsiMonthNameAndYear(BarCode.[date]) as ValidDate,
valid = SUM(case Fault when 'valid' then 1 else 0 end),
defective = SUM(case Fault when 'defective' then 1 else 0 end),
Repair = SUM(case Fault when 'repair' then 1 else 0 end)

from BarCode
group by dbo.GetShamsiMonthNameAndYear(BarCode.[date])) brcode full outer join


(select dbo.GetShamsiMonthNameAndYear(BarCode.[date]) as repairdate, COUNT(BarCode.Fault) as Rework
from BarCode BarCode inner join DescriptBarCode d on BarCode.code = d.code
where d.id in (select MIN(id) from DescriptBarCode where code = BarCode.code) and d.Fault = 'repair' and BarCode.Fault = 'valid'
group by dbo.GetShamsiMonthNameAndYear(BarCode.[date])) repaircount

on ValidDate = repairdate

خروجی بصورت زیر میشه

120315

چطوری مرتب سازی انجام بدم که تاریخ به ترتیب نشون داده بشه؟

[fakhravari]

میتونی سال بگیری SELECT LEFT('1392 فروردین',4)
برای

[fakhravari]

ALTER FUNCTION [dbo].[GetOrder](@Text NVARCHAR(50))RETURNS nvarchar(50)
AS
begin
declare @Row int


SET @Row = case @Text when N'فروردین' then 1
when N'اردیبهشت' THEN 2
when N'خرداد' then 3
when N'تیر' then 4
when N'مرداد' then 5
when N'شهریور' then 6
when N'مهر' then 7
when N'آبان' then 8
when N'آذر' then 9
when N'دی' then 10
when N'بهمن' then 11
when N'اسفند' then 12
ELSE 13 END
RETURN @Row
end


--SELECT [dbo].[GetOrder](N'اسفند')


SELECT * FROM(
SELECT [ID],
[Date],RIGHT([Date],LEN([Date])-4)AS 'Month',LEFT([Date],4) AS 'Year',dbo.GetOrder(RIGHT([Date],LEN([Date])-5))AS 'Sort'
FROM [Table_1]
)AS U
ORDER BY U.Year,U.Sort

[rezayeman]

به این طریق حلش کردم

select dbo.ConvertShamsiMYToMNY(ValidDate), valid, defective, Repair, ISNULL(Rework,0) as Rework
from (select dbo.GetShamsiMonthYear(BarCode.[date]) as ValidDate,
valid = SUM(case Fault when 'valid' then 1 else 0 end),
defective = SUM(case Fault when 'defective' then 1 else 0 end),
Repair = SUM(case Fault when 'repair' then 1 else 0 end)

from BarCode
where IdAxel = 1
group by dbo.GetShamsiMonthYear(BarCode.[date])) brcode full outer join


(select dbo.GetShamsiMonthYear(BarCode.[date]) as repairdate, COUNT(BarCode.Fault) as Rework
from BarCode BarCode inner join DescriptBarCode d on BarCode.code = d.code
where d.id in (select MIN(id) from DescriptBarCode where code = BarCode.code) and d.Fault = 'repair' and BarCode.Fault = 'valid'
group by dbo.GetShamsiMonthYear(BarCode.[date])) repaircount

on ValidDate = repairdate
order by ValidDate asc