Msg 102, Level 15, State 1, Line 28 Incorrect syntax near 'order'

Question

I'm trying the following query and I get an error. I am trying to Calculate YTD and Previous YTD in the same query.

Msg 102, Level 15, State 1, Line 28 Incorrect syntax near 'order'.

WITH
    grouped_by_date AS
(
    SELECT
        [Sales_Organization],
        [Market_Grp],
        [Delivery_Year],
        [Delivery_Month],
        [Invoicing_Day],
        SUM(QTY_UoM)             AS Weight
    FROM
        tmp.factsales s
    GROUP BY
        [Sales_Organization],
        [Market_Grp],
        [Delivery_Year],
        [Delivery_Month],
        [Invoicing_Day]
),
    cumulative_sum_for_ytd AS
(
    SELECT
        *,


        SUM([Weight]) OVER (PARTITION BY [Delivery_Year] ORDER BY [Delivery_Month], [Invoicing_Day]
                           )
                              AS Weight_YTD
    FROM
        grouped_by_date
),


    hack_to_do_lag AS
(
    SELECT
        *,
        CASE
            WHEN [Delivery_Year]%2=1
            THEN MAX(CASE WHEN [Delivery_Year]%2=0 THEN [Weight_YTD] END) OVER (PARTITION BY ([Delivery_Year]+0)/2)
            ELSE MAX(CASE WHEN [Delivery_Year]%2=1 THEN [Weight_YTD] END) OVER (PARTITION BY ([Delivery_Year]+1)/2)
        END
            AS Weight_PreviousYTD
    FROM
        cumulative_sum_for_ytd
)
SELECT
    *
FROM
    hack_to_do_lag

I searched on google it seems the problem link the version that I used, in fact:

SELECT @@VERSION

Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64) Mar 19 2015 12:32:14 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

How could I resolve my problem? I can't change the version.

Version upgrade is the only solution if you want to be able to use `ORDER BY` with `SUM` and `OVER`. — Damien_The_Unbeliever, Mar 21 '19 at 08:42
[Apperantly, there is an alternative that should work with 2008 version, though.](https://stackoverflow.com/questions/7652511/sql-server-2008-using-sum-overorder-by) — Zohar Peled, Mar 21 '19 at 08:46
There are ways to rewrite the query (probably with a performance impact). However, you should plan to upgrade, because SQL Server 2008 R2 will be out of extended support this year (and that means that no security updates will be issued anymore when a vulnerability is found). — Razvan Socol, Mar 21 '19 at 09:04
Yes, you can do a triangular join, as Zohar said. That can have some performance issues. Alternatively you could look into the [quirky update](http://www.sqlservercentral.com/articles/T-SQL/68467/), but does require a certain level of knowledge. Really though you should be looking at upgrading, or pushing whomever is in charge to look into it. SQL Server 2008 is very close to complete end of support. — Thom A, Mar 21 '19 at 09:05

score 0 · Accepted Answer · answered Mar 21 '19 at 10:59

SQL Server 2008 does not support cumulative window functions, so you need to do the calculation differently. A subquery or apply is a typical method:

WITH grouped_by_date AS (
      SELECT Sales_Organization, Market_Grp,
             Delivery_Year, Delivery_Month, Invoicing_Day,
             SUM(QTY_UoM) as Weight
      FROM tmp.factsales s
      GROUP BY Sales_Organization, Market_Grp,
               Delivery_Year, Delivery_Month, Invoicing_Day
     )
SELECT gbd.*,
       (SELECT SUM(gbd2.Weight)
        FROM grouped_by_date gbd2
        WHERE gbd2.Delivery_Year = gbd.Delivery_Year AND
              (gbd2.Delivery_Month < gbd.Delivery_Month OR
               gbd2.Delivery_Month = gbd.Delivery_Month AND
               gbd2.Invoicing_Day <- gbd.Invoicing_Day
              )
       ) as weight_ytd
FROM grouped_by_date gbd;

Msg 102, Level 15, State 1, Line 28 Incorrect syntax near 'order'

1 Answers1

Linked