CODEWITHSUNDEEP
sqlsql-serverinsertcase
Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

Inserting values using a case statement SQL Server

What I am looking to do is when the figure is 'NULL', I want it to display yesterdays date in the date column.

             Date                 Person     Team   Figure
    -------------------------------------------------------
    2017-09-01 00:00:00.000        Joe         1     NULL
    2017-09-01 00:00:00.000        Tim         1     NULL
    2017-09-01 00:00:00.000        Sam         1     NULL
    2017-09-04 00:00:00.000        Ben         1     NULL
    2017-09-04 00:00:00.000        Matt        1     NULL
    2017-09-05 00:00:00.000        Alex        1     NULL

Result:

             Date                 Person     Team   Figure
    2017-09-13 00:00:00.000        Joe         1     NULL
    2017-09-13 00:00:00.000        Tim         1     NULL

I have tried 

    DECLARE @Yesterday DATETIME
    SET @Yesterday = DateAdd(DD, DateDiff(DD, 0, GETDATE())-1, 0)

    CASE WHEN D.[Figure] IS NULL THEN
    @Yesterday
    ELSE D.[DATE] END

But I get this result

    Date                       Person   Team       Figure   Month    (No column name)
    2017-09-01 00:00:00.000     Joe      1          NULL    1125    2017-09-13 00:00:00.000
    2017-09-01 00:00:00.000     Alex     1          NULL    177     2017-09-13 00:00:00.000

How do I change this in the date column in this case statement?

Upvotes: 0

Views: 218

Answers (3)

Boydus
Boydus

Reputation: 86

I think the code might be slightly over-complicated for what you're doing. I would simply say:

select  case when DF.[Figure] is null
        then DateAdd(day,-1,getdate())
        else DF.[Date]
        end as [Date],...

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

What i am looking to do is when the figure is 'NULL' i want it to display yesterdays date in the date field.

If this is what you want, then you would do:

select . . .,
       coalesce(figure, cast(getdate() - 1 as date)) as figure

Based on your example data, I'm not sure this is what you really want, but this is what you are asking for.

Upvotes: 0

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9063

So instead of selecting date directely, you have to use CASE expression in following:

SELECT CASE WHEN D.[Figure] IS NULL THEN DATEADD(DAY, -1, GETDATE())
            ELSE D.[DATE] 
       END AS [Date],
       Person,
       Team,
       Figure, 
       Month
FROM Tbl AS D

Upvotes: 0

Related Questions