CODEWITHSUNDEEP
sqlsql-servert-sql
user2296463
user2296463

Reputation: 161

Select case convert date

I am trying an SQL statement for a select case. The 'when' has a convert for a date. It is throwing errors. What am I missing?

SELECT DISTINCT
    'detail', n.UserName, n.StatusCodeDesc,
    CASE 
       WHEN n.enddate IS NULL AND (n.statuscode = 'L' or n.statuscode = 'P')
         THEN (CONVERT(VARCHAR(10), getdate(), 101) AS [MM/DD/YYYY])
       ELSE (CONVERT(VARCHAR(10), n.EndDate, 101) AS [MM/DD/YYYY])
    END,
    n.TC_OBT_STAT 
FROM  
    tbl_ActNightlyFeed n 
INNER JOIN
    Tbl_ActExistingFeed g ON n.Empid = g.Empid

Upvotes: 1

Views: 241

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You are not selecting the g table at all. I would suggest writing this as:

SELECT 'detail', n.UserName, n.StatusCodeDesc,
       (CASE WHEN n.enddate IS NULL AND n.statuscode IN ('L', 'P')
             THEN CONVERT(VARCHAR(10), getdate(), 101)
             ELSE CONVERT(VARCHAR(10), n.EndDate, 101) 
        END) AS [MM/DD/YYYY],
       n.TC_OBT_STAT 
FROM tbl_ActNightlyFeed n 
WHERE EXISTS (SELECT 1
              FROM Tbl_ActExistingFeed g 
              WHERE n.Empid = g.Empid 
             );

If the nightly feed table could result in duplicates, you would still need select distinct. I am guessing that table would not have duplicates.

Upvotes: 0

jpw
jpw

Reputation: 44871

The column alias should only appear after the end of the case expression, like this:

SELECT DISTINCT
  'detail',
  n.UserName,
  n.StatusCodeDesc,
  CASE 
    WHEN n.enddate IS NULL AND (n.statuscode = 'L' OR n.statuscode = 'P')
    THEN CONVERT(VARCHAR(10), getdate(), 101) 
    ELSE CONVERT(VARCHAR(10), n.EndDate, 101) 
  END AS [MM/DD/YYYY],
  n.TC_OBT_STAT 
FROM tbl_ActNightlyFeed  n 
JOIN Tbl_ActExistingFeed g ON n.Empid = g.Empid

Upvotes: 4

Related Questions