Order By in an SQL query with aggregate function and CASE.. WHEN...END

Question

Order By in an SQL query with aggregate function and CASE.. WHEN...END

saintor1

Mr or Mrs. 500

Points: 562
More actions
June 14, 2019 at 12:09 pm

#3653752

I have this query which works OK.
SELECT D.MStockCode AS StockCode, CASE
WHEN [W_SE-MFG].dbo.[YearWeek](D.MLatestDueDate) < [W_SE-MFG].dbo.[YearWeek](GETDATE()) THEN 'PAST'
WHEN [W_SE-MFG].dbo.[YearWeek](D.MLatestDueDate) > [W_SE-MFG].dbo.[YearWeek](DATEADD(month, 7, GETDATE())) THEN 'LATER'
ELSE [W_SE-MFG].dbo.[YearWeek](D.MLatestDueDate) END AS DueDate, SUM(D.MOrderQty - D.MReceivedQty) AS QtyOuts
FROM dbo.PorMasterHdr AS H INNER JOIN dbo.PorMasterDetail AS D ON H.PurchaseOrder = D.PurchaseOrder
WHERE (D.MOrderQty - D.MReceivedQty > 0) AND (D.MCompleteFlag <> 'Y') AND (H.CancelledFlag <> 'Y') AND (H.DatePoCompleted IS NULL) AND (D.MStockCode = 'QFH12018-B')
GROUP BY D.MStockCode, CASE WHEN [W_SE-MFG].dbo.[YearWeek](D.MLatestDueDate) < [W_SE-MFG].dbo.[YearWeek](GETDATE())
THEN 'PAST' WHEN [W_SE-MFG].dbo.[YearWeek](D.MLatestDueDate) > [W_SE-MFG].dbo.[YearWeek](DATEADD(month, 7, GETDATE())) THEN 'LATER' ELSE [W_SE-MFG].dbo.[YearWeek](MLatestDueDate) END
The results is
QFH12018-B 2019-27 13.000000
QFH12018-B 2019-32 13.000000
QFH12018-B 2019-36 13.000000
QFH12018-B 2019-41 13.000000
QFH12018-B 2019-45 13.000000
QFH12018-B 2019-49 13.000000
QFH12018-B 2020-02 13.000000
QFH12018-B LATER 39.000000
QFH12018-B PAST 81.000000
My problem is that I want PAST to be the first and the rest of the recordset is OK as is, LATER being the last.
No matter what I try, I can't get it right.
If I add
ORDER BY D.MLatestDueDate
at the end
I get this error
Msg 8127, Level 16, State 1, Line 10
Column "dbo.PorMasterDetail.MLatestDueDate" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
If I add D.MLatestDueDate in either my SELECT or GROUP BY fields, then it doesn't really aggregate data.
SELECT D.MStockCode AS StockCode, CASE
WHEN [W_SE-MFG].dbo.[YearWeek](D.MLatestDueDate) < [W_SE-MFG].dbo.[YearWeek](GETDATE()) THEN 'PAST'
WHEN [W_SE-MFG].dbo.[YearWeek](D.MLatestDueDate) > [W_SE-MFG].dbo.[YearWeek](DATEADD(month, 7, GETDATE())) THEN 'LATER'
ELSE [W_SE-MFG].dbo.[YearWeek](D.MLatestDueDate) END AS DueDate, SUM(D.MOrderQty - D.MReceivedQty) AS QtyOuts
FROM dbo.PorMasterHdr AS H INNER JOIN dbo.PorMasterDetail AS D ON H.PurchaseOrder = D.PurchaseOrder
WHERE (D.MOrderQty - D.MReceivedQty > 0) AND (D.MCompleteFlag <> 'Y') AND (H.CancelledFlag <> 'Y') AND (H.DatePoCompleted IS NULL) AND (D.MStockCode = 'QFH12018-B')
GROUP BY D.MStockCode, D.MLatestDueDate, CASE WHEN [W_SE-MFG].dbo.[YearWeek](D.MLatestDueDate) < [W_SE-MFG].dbo.[YearWeek](GETDATE())
THEN 'PAST' WHEN [W_SE-MFG].dbo.[YearWeek](D.MLatestDueDate) > [W_SE-MFG].dbo.[YearWeek](DATEADD(month, 7, GETDATE())) THEN 'LATER' ELSE [W_SE-MFG].dbo.[YearWeek](MLatestDueDate) END
ORDER BY D.MLatestDueDate
Result
QFH12018-B PAST 3.000000
QFH12018-B PAST 13.000000
QFH12018-B PAST 13.000000
QFH12018-B PAST 13.000000
QFH12018-B PAST 13.000000
QFH12018-B PAST 13.000000
QFH12018-B PAST 13.000000
QFH12018-B 2019-27 13.000000
QFH12018-B 2019-32 13.000000
QFH12018-B 2019-36 13.000000
QFH12018-B 2019-41 13.000000
QFH12018-B 2019-45 13.000000
QFH12018-B 2019-49 13.000000
QFH12018-B 2020-02 13.000000
QFH12018-B LATER 13.000000
QFH12018-B LATER 13.000000
QFH12018-B LATER 13.000000
What can be done to have my initial recordset with PAST as first record and LATER the last one?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

Maybe, at a total guess, have an ORDER BY of:

ORDER BY CASE DueDate WHEN 'PAST' THEN 1
                      WHEN 'LATER' THEN 3
                      ELSE 2
         END;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

saintor1 Mr or Mrs. 500 Points: 562 More actions · Answer 2

saintor1

Mr or Mrs. 500

Points: 562

June 14, 2019 at 12:51 pm

#3653784

I tried it and got

Invalid column name 'DueDate'.

Thom A SSC Guru Points: 99126 More actions · Answer 3

saintor1 wrote:

I tried it and got Invalid column name 'DueDate'.

Use a CTE then:

WITH CTE AS
    (SELECT D.MStockCode AS StockCode,
            CASE
                 WHEN [W_SE-MFG].dbo.[YearWeek](D.MLatestDueDate) < [W_SE-MFG].dbo.[YearWeek](GETDATE()) THEN 'PAST'
                 WHEN [W_SE-MFG].dbo.[YearWeek](D.MLatestDueDate) > [W_SE-MFG].dbo.[YearWeek](DATEADD(MONTH, 7, GETDATE())) THEN 'LATER'
                 ELSE [W_SE-MFG].dbo.[YearWeek](D.MLatestDueDate)
            END AS DueDate,
            SUM(D.MOrderQty - D.MReceivedQty) AS QtyOuts
     FROM dbo.PorMasterHdr H
          INNER JOIN dbo.PorMasterDetail D ON H.PurchaseOrder = D.PurchaseOrder
     WHERE (D.MOrderQty - D.MReceivedQty > 0)
       AND (D.MCompleteFlag <> 'Y')
       AND (H.CancelledFlag <> 'Y')
       AND (H.DatePoCompleted IS NULL)
       AND (D.MStockCode = 'QFH12018-B')
     GROUP BY D.MStockCode,
              CASE
                   WHEN [W_SE-MFG].dbo.[YearWeek](D.MLatestDueDate) < [W_SE-MFG].dbo.[YearWeek](GETDATE()) THEN 'PAST'
                   WHEN [W_SE-MFG].dbo.[YearWeek](D.MLatestDueDate) > [W_SE-MFG].dbo.[YearWeek](DATEADD(MONTH, 7, GETDATE())) THEN 'LATER'
                   ELSE [W_SE-MFG].dbo.[YearWeek](MLatestDueDate)
              END)
SELECT CTE.StockCode,
       CTE.DueDate,
       CTE.QtyOuts
FROM CTE
ORDER BY CASE DueDate WHEN 'PAST' THEN 1 WHEN 'LATER' THEN 3 ELSE 2 END;

This reply was modified 4 years, 10 months ago by Thom A.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

saintor1 Mr or Mrs. 500 Points: 562 More actions · Answer 4

I copied and pasted from your reply and again, same result.

More specifically

Msg 207, Level 16, State 1, Line 41
Invalid column name 'DueDate'.
Msg 207, Level 16, State 1, Line 41
Invalid column name 'DueDate'.

This reply was modified 4 years, 10 months ago by saintor1.

saintor1 Mr or Mrs. 500 Points: 562 More actions · Answer 5

The CTE version did the trick, thank you!

Also using "-PAST" instead of "PAST" did put it first