1

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.

2

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.