Sql-server – Weird order by behavior on SQL Server 2014 SP2

sql serversql server 2014

I am having an issue with a existing query running on a 2008 R2 instance, migrated to 2014 SP2 (No CU).

So far I have tried to use from position, alias, etc. So far I have narrow the event on the DATEPART any ideas?

SELECT DISTINCT CAST(DATEDIFF(HH,DATE_RECEIVED,TEST_DATE)  / 24.0 AS 
DECIMAL(5,2))   AS TAT,
DATENAME(MONTH,TEST_DATE) AS MONTHNAME,
DATEPART(WW,TEST_DATE) AS WEEK,
DATEPART(YYYY,TEST_DATE) AS YEAR,
DATEPART(MM,TEST_DATE) AS MONTHNUMBER,
PRODUCT_CODE,TEST_PLAN_NAME,COUNT(PRODUCT_CODE) AS COUNT
FROM CO_SAMPLES 
WHERE SAMPLE_TYPE IN ('CA')
AND PROFILE_NAME <> 'Shelf Life' AND RECLASS_NUMBER IS NULL
GROUP BY DATEPART(WW,TEST_DATE), 
DATEPART(YYYY,TEST_DATE),
DATEPART(MM,TEST_DATE),
PRODUCT_CODE,TEST_PLAN_NAME,TEST_DATE,DATE_RECEIVED
ORDER BY  DATEPART(YYYY,TEST_DATE)
,DATEPART(MM,TEST_DATE)
,DATEPART(WW,TEST_DATE),PRODUCT_CODE

The return error is as follows

Msg 145, Level 15, State 1, Line 1 ORDER BY items must appear in the
select list if SELECT DISTINCT is specified.

Best Answer

It is because you need the entire definition for the [TAT] field in the Group By:

CAST(DATEDIFF(HH, DATE_RECEIVED, TEST_DATE) / 24.0 AS DECIMAL(5, 2))

Something like this:

SELECT DISTINCT
        [TAT] = CAST(DATEDIFF(HH, DATE_RECEIVED, TEST_DATE) / 24.0 AS DECIMAL(5, 2))
        , [MonthName] = DATENAME(MONTH, TEST_DATE) 
        , [Week] = DATEPART(WW, TEST_DATE) 
        , [Year] = DATEPART(YYYY, TEST_DATE) 
        , [MonthNumber] = DATEPART(MM, TEST_DATE)
        , PRODUCT_CODE 
        , TEST_PLAN_NAME 
        , [Count] = COUNT(PRODUCT_CODE)
FROM    CO_SAMPLES
WHERE   SAMPLE_TYPE IN ( 'CA' )
        AND PROFILE_NAME <> 'Shelf Life'
        AND RECLASS_NUMBER IS NULL
GROUP BY CAST(DATEDIFF(HH, DATE_RECEIVED, TEST_DATE) / 24.0 AS DECIMAL(5, 2)) ,
        DATENAME(MONTH, TEST_DATE) ,
        DATEPART(WW, TEST_DATE) ,
        DATEPART(YYYY, TEST_DATE) ,
        DATEPART(MM, TEST_DATE) ,
        TEST_PLAN_NAME
ORDER BY [Year] ,
        [MonthNumber] ,
        [Week] ,
        PRODUCT_CODE;
Related Question