How to get minimum value with added string in SQL Server?

user_13814912 0
Viewed 44

I have a table with 3 fields: Item Number, Promised Date and Promised Ship Date. I wanted to get pull the columns Item Number with the earliest promised date so my query is

SELECT 
    ITEMNMBR, 
    MIN(PRMDATE) AS [Earliest Promised Date] 
FROM 
    Table1 
GROUP BY 
    ITEMNMBR

This works fine but I wanted to add a string with that [Earliest Promised Date]: Confirmed or Unconfirmed. It is determined by Promised Ship Date. If the promised ship date is equal to the promised date, it is unconfirmed and if they are unequal then it is confirmed.

I have tried but does not work:

SELECT
    ITEMNMBR,
    MIN(CASE
           WHEN PRMDATE = PRMSHPDTE 
              THEN CONVERT(varchar, PRMDATE, 101) + ' - Unconfirmed'
           WHEN PRMDATE <> PRMSHPDTE 
              THEN CONVERT(varchar, PRMDATE, 101) + ' - Confirmed'
           ELSE 'Error'
        END) AS [Promised Date]
FROM 
    Table1
GROUP BY 
    ITEMNMBR

Can you point out what I have done wrong in the second query?

sql-server
1 Answers

I believe the problem is rooted in the use of format 101 and alphabetical sorting. You might need to expound on the "(un)confirmed" logic but something like this should get you in the right direction.

SELECT
    ITEMNMBR,
    CONVERT(VARCHAR(12), MIN(PRMDATE), 101) + ' - ' +
        MAX(CASE
            WHEN PRMDATE = PRMSHPDTE THEN 'Unconfirmed'
            WHEN PRMDATE <> PRMSHPDTE THEN 'Confirmed'
            ELSE 'XX Error'
        END) AS [Promised Date]
FROM 
    Table1
GROUP BY 
    ITEMNMBR;
user_13222680
Related