SQL数据透视日期未按正确的顺序显示

Question

我正在尝试使用以下代码获取从上个月的最后一天到本月的最后一天的记录

    SELECT [DAY],
       [N-1 HSD],
       [N-2 HSD],
       [N-6 HSD],
       DIP,
       [STOCK LTR],
       [PURRCHASE LTR],
       [STOCK LTR] + [PURRCHASE LTR] AS [Total Ltr],
       Isnull([TESTING], 0) AS Testing,
       0 AS [Sales As Reading],
       0 AS [Sales As Dip],
       0 AS [Diff Ltr],

  (SELECT TOP (1) _RATE
   FROM _PRODUCTRATE
   WHERE _ISDELETED = N'1'
     AND _PRODUCTID_PK = N'154eeb16-0c5c-44e4-b5cc-b0f30a22098f'
     AND _DATE <= _RATEDATE
   ORDER BY _DATE DESC) AS [Rate Rs],
       0 AS [Amount Rs]
FROM
  (SELECT TOP (100) PERCENT Day(DBO._DSRENTRY._DSRENTRYDATE) AS [Day],
                            DBO._DSRENTRYDET._CLOSING,
                            DBO._DSRENTRY._DIPSCALE AS DIP,
                            DBO._DSRENTRY._DIP AS [Stock Ltr],
                            DBO._DSRENTRY._TESTINGQTY AS [Testing],
                            DBO._NOZZLE._NOZZLENAME,

     (SELECT Isnull(Sum(_QTY), 0) AS Expr1
      FROM DBO._TANKVACCANT
      WHERE (_ISDELETED = N'1')
        AND (_TANKID_PK = N'88e31d8c-acf5-4c09-a91f-28577c66d070')
        AND (_TANKVACCANTDATE = _DSRENTRYDATE)
        AND (_COMPANYID_PK = N'747c28ad-2924-4456-91fe-6f4f7b0db3fd')
        AND (_WORKGROUPNAME = N'SRV')) AS [Purrchase Ltr],
                            DBO._DSRENTRY._DSRENTRYDATE AS _RATEDATE
   FROM DBO._NOZZLE
   RIGHT OUTER JOIN DBO._DSRENTRYDET ON DBO._NOZZLE._NOZZLEID_PK = DBO._DSRENTRYDET._NOZZLEID_PK
   RIGHT OUTER JOIN DBO._DSRENTRY ON DBO._DSRENTRYDET._DSRENTRYID_PK = DBO._DSRENTRY._DSRENTRYID_PK
   WHERE (DBO._DSRENTRYDET._ISDELETED = N'1')
     AND (DBO._DSRENTRY._COMPANYID_PK = N'747c28ad-2924-4456-91fe-6f4f7b0db3fd')
     AND (DBO._DSRENTRYDET._COMPANYID_PK = N'747c28ad-2924-4456-91fe-6f4f7b0db3fd')
     AND (DBO._NOZZLE._COMPANYID_PK = N'747c28ad-2924-4456-91fe-6f4f7b0db3fd')
     AND (DBO._DSRENTRY._WORKGROUPNAME = N'SRV')
     AND (DBO._DSRENTRYDET._WORKGROUPNAME = N'SRV')
     AND (DBO._NOZZLE._WORKGROUPNAME = N'SRV')
     AND (DBO._DSRENTRY._ISDELETED = N'1')
     AND (DBO._NOZZLE._ISDELETED = N'1')
     AND (DBO._DSRENTRY._DSRENTRYDATE >= CONVERT(DATETIME, '31.08.2017 00:00:00', 105))
     AND (DBO._DSRENTRY._DSRENTRYDATE <= CONVERT(DATETIME, '30.09.2017 23:59:59', 105))
     AND (DBO._DSRENTRY._TANKID_PK = N'88e31d8c-acf5-4c09-a91f-28577c66d070')
   ORDER BY DBO._DSRENTRY._DSRENTRYDATE) AS DERIVEDTBL_1 PIVOT(Sum(_CLOSING)
                                                               FOR _NOZZLENAME IN ([N-1 HSD], [N-2 HSD], [N-6 HSD])) AS PVTTABLE

下面是输出

DAY N-1 HSD N-2 HSD N-6 HSD DIP STOCK LTR   PURRCHASE LTR   Total Ltr   Testing Sales As Reading    Sales As Dip    Diff Ltr    Rate Rs Amount Rs
1   1886247.00  1139460.00  391836.00   140.00  15319.00    0.00    15319.00    10.00   0   0   0   59.86   0
.........
.........
.........
29  1909720.00  1147850.00  397467.00   102.40  10473.00    0.00    10473.00    10.00   0   0   0   61.57   0
30  1910934.00  1148180.00  397467.00   90.60   8932.00 6000.00 14932.00    10.00   0   0   0   61.65   0
31  1885620.00  1139191.00  391788.00   70.00   6310.00 10000.00    16310.00    10.00   0   0   0   59.78   0

如输出中所示，最后一个记录是31 1885620.00 1139191.00 391788.00 70.00 6310.00 10000.00 16310.00 10.00 0 0 0 59.78 0，它显示在底部，但这应该是第一个，

如果我将Day(DBO._DSRENTRY._DSRENTRYDATE)更改为DBO._DSRENTRY._DSRENTRYDATE，那么记录将按正确的顺序开始，但它会给出完整的日期和时间。如下所示

DAY N-1 HSD N-2 HSD N-6 HSD DIP STOCK LTR   PURRCHASE LTR   Total Ltr   Testing Sales As Reading    Sales As Dip    Diff Ltr    Rate Rs Amount Rs
2017-08-31 00:00:00.000 1885620.00  1139191.00  391788.00   70.00   6310.00 10000.00    16310.00    10.00   0   0   0   59.78   0
2017-09-01 00:00:00.000 1886247.00  1139460.00  391836.00   140.00  15319.00    0.00    15319.00    10.00   0   0   0   59.86   0
2017-09-02 00:00:00.000 1887258.00  1139601.00  391938.00   130.00  14062.00    0.00    14062.00    10.00   0   0   0   59.94   0
----------------------------
----------------------------
2017-09-29 00:00:00.000 1909720.00  1147850.00  397467.00   102.40  10473.00    0.00    10473.00    10.00   0   0   0   61.57   0
2017-09-30 00:00:00.000 1910934.00  1148180.00  397467.00   90.60   8932.00 6000.00 14932.00    10.00   0   0   0   61.65   0

但我只需要一天而不是完整的约会时间，

我是怎么把这个存档的。

Answer 1

使用DATEPART(DAY, [Day])可以帮助您对输出进行排序。请将其添加到select子句中并按同一列排序，以获得排序后的输出。