Replace NULL with string if another cell isn't NULL

Question

I'm working on a query:

SELECT D.PointPerson AS Person, R.[Name] AS Project, ISNULL(P.[Name]+' - '+P.[Description], 'KanBan') AS Sprint, COALESCE(S.[Number], N.IncidentNumber) AS Story, T.[Name] AS Task,
   sum(CASE WHEN DatePart(dw, D.ActivityDate) = 1 THEN D.[Hours] ELSE 0 END) AS Monday,
   sum(CASE WHEN DatePart(dw, D.ActivityDate) = 2 THEN D.[Hours] ELSE 0 END) AS Tuesday,
   sum(CASE WHEN DatePart(dw, D.ActivityDate) = 3 THEN D.[Hours] ELSE 0 END) AS Wednesday,
   sum(CASE WHEN DatePart(dw, D.ActivityDate) = 4 THEN D.[Hours] ELSE 0 END) AS Thursday,
   sum(CASE WHEN DatePart(dw, D.ActivityDate) = 5 THEN D.[Hours] ELSE 0 END) AS Friday,
   sum(CASE WHEN DatePart(dw, D.ActivityDate) = 6 THEN D.[Hours] ELSE 0 END) AS Saturday,
   sum(CASE WHEN DatePart(dw, D.ActivityDate) = 7 THEN D.[Hours] ELSE 0 END) AS Sunday,
   sum(D.[Hours]) AS Total
FROM DailyTaskHours D
LEFT JOIN Task T ON D.TaskId = T.PK_Task 
LEFT JOIN Story S ON T.StoryId = S.PK_Story 
LEFT JOIN NonScrumStory N ON D.NonScrumStoryId = N.PK_NonScrumStory 
LEFT JOIN Sprint P ON S.SprintId = P.PK_Sprint 
LEFT JOIN Product R ON S.ProductId = R.PK_Product
GROUP BY D.PointPerson, R.[Name], P.[Name], P.[Description], S.[Number], N.IncidentNumber, T.[Name]
ORDER BY CASE WHEN ISNULL(P.[Name]+' - '+P.[Description], 'KanBan') = 'KanBan'
    THEN 1
    ELSE 0 END,
    Project ASC,
    Sprint ASC,
    Story ASC,
    Task ASC

If Name and Dexcription are NULL, the cell is populated with 'KanBan ' ISNULL(P.[Name]+' - '+P.[Description], 'KanBan')

This is working how I want it to. But, If a 3rd cell is NULL (T.[Name]) I would also like this cell is NULL.

So if P.[Name]+' - '+P.[Description] (Sprint) IS NULL then populate with KanBan, UNLESS T.[Name] IS NULL in which case I would also like (Sprint) to be NULL

How can I achieve this?

Gordon Linoff · Accepted Answer · 2013-09-18 14:38:40Z

3

This is a good reason to use case:

select (case when T.Name is null then NULL
             when P.[Name]+' - '+P.[Description] is NULL then 'KanBan'
             else P.[Name]+' - '+P.[Description]
        end) AS Sprint

answered Sep 18, 2013 at 14:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

paul Over a year ago

perhaps:

select (case when T.Name is null then NULL              else coalesce(P.[Name]+' - '+P.[Description], 'KanBan')         end) AS Sprint

Bohemian Over a year ago

why the brackets around the case? Before you say "clarity" - it isn't more clear. It's more cluttered.

Gordon Linoff Over a year ago

@Bohemian . . . Not to my eye. I use parentheses to see where statements end. Nested case statements are a particular pain.

Bohemian Over a year ago

Isn't that why god invented the end keyword? When you have syntactic delimiters case ... end brackets are visually and logically redundant.

Collectives™ on Stack Overflow

Replace NULL with string if another cell isn't NULL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related