SQL从特定月份开始订购

Question

我有以下查询，它返回以下结果：

SELECT T0.Name AS Period, 
       SUM(T2.LineTotal) AS CurrentDebtors, 
       MAX(T1.DocRate) AS ExchangeRate,
       SUM(CASE WHEN DATEDIFF(day, T1.DocDate, T1.DocDueDate) > 30
                THEN T2.LineTotal 
           END) AS NonCurrentDebtors, 
       T3.TurnoverMonth
  FROM OFPR T0 
 INNER JOIN OINV T1
    ON T0.AbsEntry = T1.FinncPriod 
 INNER JOIN INV1 T2 
    ON T1.DocEntry = T2.DocEntry
 INNER JOIN 
 (
    SELECT ORCT.FinncPriod, 
           SUM(ORCT.DocTotal) AS TurnoverMonth
      FROM ORCT
     WHERE YEAR(ORCT.DocDate) = '2009'
     GROUP BY ORCT.FinncPriod
 )  T3 
    ON T0.AbsEntry = T3.FinncPriod
 WHERE YEAR(T1.DocDate) = '2009'
 GROUP BY T0.Name, T3.TurnoverMonth
 ORDER BY T0.Name

结果：

Period  CurrentDebtors  ExchangeRate    NonCurrentDebtors   TurnoverMonth
01.2009 82221785.530000     68.420000   11365437.180000 105030603.650000
02.2009 103432923.420000    68.420000   14444391.890000 84554821.480000
03.2009 118881620.200000    68.420000   16292534.760000 105662045.900000
04.2009 115400360.770000    68.420000   28278615.950000 127528038.680000
05.2009 120497298.090000    68.420000   21524834.230000 94912946.080000
06.2009 124426393.780000    68.420000   31814427.220000 92526329.440000
07.2009 107881741.630000    68.420000   25339512.290000 125756922.560000
08.2009 147938452.250000    68.420000   35773934.510000 102350989.510000
09.2009 137295113.320000    126.500000  30572992.390000 141742820.230000
10.2009 134955455.780000    68.420000   34060549.000000 88356896.710000
11.2009 151664995.160000    68.420000   35388019.370000 112424543.000000
12.2009 169973243.880000    68.420000   47526181.370000 165130664.260000

我正在对一家公司使用此查询，该公司的财政年度从每年的8月开始。如何对上述查询进行排序，使其从任意年份(8-7月)的Period 08.2009, 09.2009...until the last row 07.2009进行排序？结果来自SAP Business One数据库，其中Period列为nvarchar。(Microsoft SQL Server 2008)。

Answer 1

试试这个：

逗乐你的T0.Name是一个字符字段

您的Order By子句将替换为以下代码

order by
 case when cast(left (T0.Name,2) as int)>=8 
      then cast(left (T0.Name,2) as int)-8
      else cast(left (T0.Name,2) as int)+4 
 end  

Answer 2

您可以按OFPR RefDate/DueDate/postingDate字段对(对不起，但我记不住实际名称)排序，因为这些是日期字段，它们告诉您周期的开始和结束。