Order By Clause not working in SQL Server for Sorting date value-CodePudding

I have two tables one is called Shelf Table which has the Shelf Number

SEQ	SHELF_NUMBER	QUANTITY
1	ID001	100
2	ID002	20
3	ID003	55
4	ID004	200
5	ID005	38

And the Other Table that i have is Shelf_Data

SEQ	SHELF_NUMBER_FK	FIELD	VALUE
1	ID001	BATCH	100006
2	ID001	SHELF_DATE	08/20/2023
3	ID002	BATCH	110009
4	ID003	BATCH	565644
5	ID003	SHELF_DATE	02/10/2024
6	ID004	BATCH	222389
7	ID004	SHELF_DATE	10/25/2023
8	ID005	BATCH	181865

I have written a query for sorting based on the SHELF_DATE as below

SELECT s.SHELF_NUMBER, SUM(s.QUANTITY), sd.VALUE AS BATCH
  FROM SHELF s
  LEFT JOIN SHELF_DATA sd
    ON s.SHELF_NUMBER = sd.SHELF_NUMBER_FK
   AND sd.FIELD IN ('BATCH')
 WHERE s.QUANTITY > 0
 GROUP BY s.SHELF_NUMBER, sd.VALUE
 ORDER BY (CASE
            WHEN sd.FIELD = 'SHELF_DATE' THEN
             sd.VALUE
           END) ASC

So, what I am trying to accomplish from above query is to get the shelf batch numbers with Valid quantity which are sorted by the SHELF Date in ascending order. But the Order By Clause is not sorting the data with the Shelf_Date Value.

Desired Output::

SHELF_NUMBER	SUM_QUANTITY	BATCH
ID001	100	100006
ID004	200	222389
ID003	55	565644
ID002	20	110009
ID005	38	181865

Really stuck here.. Thanks in Advance

CodePudding user response：

Provided (SHELF_NUMBER_FK, FIELD) is a unique key of the SHELF_DATA table you can join it twice. Not sure which date you want when grouping, assuming max.

SELECT s.SHELF_NUMBER, SUM(s.QUANTITY), sd1.VALUE AS BATCH, max(Convert(DATE, sd2.VALUE, 101) s_date
  FROM SHELF s
  LEFT JOIN SHELF_DATA sd1
    ON s.SHELF_NUMBER = sd1.SHELF_NUMBER_FK
   AND sd1.FIELD ='BATCH'
  LEFT JOIN SHELF_DATA sd2
    ON s.SHELF_NUMBER = sd2.SHELF_NUMBER_FK
   AND sd2.FIELD IN ='SHELF_DATE' 
 WHERE s.QUANTITY > 0
 GROUP BY s.SHELF_NUMBER, sd1.VALUE
 ORDER BY max(Convert(DATE, sd2.VALUE, 101)) ASC

CodePudding user response：

You can write it as 2 separate queries.

;with cte as 
(SELECT s.SHELF_NUMBER, SUM(s.QUANTITY) qty, sd.VALUE AS BATCH
  FROM SHELF s
  LEFT JOIN SHELF_DATA sd
    ON s.SHELF_NUMBER = sd.SHELF_NUMBER_FK
   AND sd.FIELD IN ('BATCH')
 WHERE s.QUANTITY > 0
 GROUP BY s.SHELF_NUMBER, sd.VALUE
)
select c.* from cte c
inner join SHELF_DATA sd
ON s.SHELF_NUMBER = sd.SHELF_NUMBER_FK
where sd.FIELD = 'shelf_date'
order by sd.value