Confused Case Statement Value-CodePudding

select
    -----
    date_tbl.[Date Name]
    -----
    ,case
        when month(date_tbl.[Date Name]) = 1 and day(date_tbl.[Date Name]) = 1 then concat(cast(year(date_tbl.[Date Name]) as varchar(255)),' - ','Month 1 Start')
        when month(date_tbl.[Date Name]) = 1 and day(date_tbl.[Date Name]) = 31 then concat(cast(year(date_tbl.[Date Name]) as varchar(255)),' - ','Month 1 End')
        -----
        when month(date_tbl.[Date Name]) = 2 and day(date_tbl.[Date Name]) = 1 then concat(cast(year(date_tbl.[Date Name]) as varchar(255)),' - ','Month 2 Start')
        when month(date_tbl.[Date Name]) = 2 and (day(eomonth(date_tbl.[Date Name])) = 28 OR day(eomonth(date_tbl.[Date Name])) = 29) then concat(cast(year(date_tbl.[Date Name]) as varchar(255)),' - ','Month 2 End')
        -----
        when month(date_tbl.[Date Name]) = 3 and day(date_tbl.[Date Name]) = 1 then concat(cast(year(date_tbl.[Date Name]) as varchar(255)),' - ','Month 3 Start')
        when month(date_tbl.[Date Name]) = 3 and day(date_tbl.[Date Name]) = 31 then concat(cast(year(date_tbl.[Date Name]) as varchar(255)),' - ','Month 3 End')
        -----
        when month(date_tbl.[Date Name]) = 4 and day(date_tbl.[Date Name]) = 1 then concat(cast(year(date_tbl.[Date Name]) as varchar(255)),' - ','Month 4 Start')
        when month(date_tbl.[Date Name]) = 4 and day(date_tbl.[Date Name]) = 30 then concat(cast(year(date_tbl.[Date Name]) as varchar(255)),' - ','Month 4 End')
        -----
        when month(date_tbl.[Date Name]) = 5 and day(date_tbl.[Date Name]) = 1 then concat(cast(year(date_tbl.[Date Name]) as varchar(255)),' - ','Month 5 Start')
        when month(date_tbl.[Date Name]) = 5 and day(date_tbl.[Date Name]) = 31 then concat(cast(year(date_tbl.[Date Name]) as varchar(255)),' - ','Month 5 End')
        -----
        when month(date_tbl.[Date Name]) = 6 and day(date_tbl.[Date Name]) = 1 then concat(cast(year(date_tbl.[Date Name]) as varchar(255)),' - ','Month 6 Start')
        when month(date_tbl.[Date Name]) = 6 and day(date_tbl.[Date Name]) = 30 then concat(cast(year(date_tbl.[Date Name]) as varchar(255)),' - ','Month 6 End')
        -----
        when month(date_tbl.[Date Name]) = 7 and day(date_tbl.[Date Name]) = 1 then concat(cast(year(date_tbl.[Date Name]) as varchar(255)),' - ','Month 7 Start')
        when month(date_tbl.[Date Name]) = 7 and day(date_tbl.[Date Name]) = 31 then concat(cast(year(date_tbl.[Date Name]) as varchar(255)),' - ','Month 7 End')
        -----
        when month(date_tbl.[Date Name]) = 8 and day(date_tbl.[Date Name]) = 1 then concat(cast(year(date_tbl.[Date Name]) as varchar(255)),' - ','Month 8 Start')
        when month(date_tbl.[Date Name]) = 8 and day(date_tbl.[Date Name]) = 31 then concat(cast(year(date_tbl.[Date Name]) as varchar(255)),' - ','Month 8 End')
        -----
        when month(date_tbl.[Date Name]) = 9 and day(date_tbl.[Date Name]) = 1 then concat(cast(year(date_tbl.[Date Name]) as varchar(255)),' - ','Month 9 Start')
        when month(date_tbl.[Date Name]) = 9 and day(date_tbl.[Date Name]) = 30 then concat(cast(year(date_tbl.[Date Name]) as varchar(255)),' - ','Month 9 End')
        -----
        when month(date_tbl.[Date Name]) = 10 and day(date_tbl.[Date Name]) = 1 then concat(cast(year(date_tbl.[Date Name]) as varchar(255)),' - ','Month 10 Start')
        when month(date_tbl.[Date Name]) = 10 and day(date_tbl.[Date Name]) = 31 then concat(cast(year(date_tbl.[Date Name]) as varchar(255)),' - ','Month 10 End')
        -----
        when month(date_tbl.[Date Name]) = 11 and day(date_tbl.[Date Name]) = 1 then concat(cast(year(date_tbl.[Date Name]) as varchar(255)),' - ','Month 11 Start')
        when month(date_tbl.[Date Name]) = 11 and day(date_tbl.[Date Name]) = 30 then concat(cast(year(date_tbl.[Date Name]) as varchar(255)),' - ','Month 11 End')
        -----
        when month(date_tbl.[Date Name]) = 12 and day(date_tbl.[Date Name]) = 1 then concat(cast(year(date_tbl.[Date Name]) as varchar(255)),' - ','Month 12 Start')
        when month(date_tbl.[Date Name]) = 12 and day(date_tbl.[Date Name]) = 31 then concat(cast(year(date_tbl.[Date Name]) as varchar(255)),' - ','Month 12 End')
        -----
        else NULL
        end as [month_label]
    -----
from
    [dbo].[DATES] as date_tbl

My results look like this:

Date Name     month_label
1900-01-01    1900 - Month 1 Start
1900-01-02    NULL
...           ...
1900-01-31    1900 - Month 1 End
1900-02-01    1900 - Month 2 Start
1900-02-02    1900 - Month 2 End
1900-02-03    1900 - Month 2 End
1900-02-04    1900 - Month 2 End
1900-02-28    1900 - Month 2 End
1900-03-01    1900 - Month 3 Start
1900-03-02    NULL
.....         .....
1900-03-31    1900 - Month 3 End

Why does my February labels have the "end" value for every row instead of the last day of the month? How do I fix it so that it only labels the 1st and last days of each month?

CodePudding user response：

You don't need all those CASE expression for every month.

Just check for DAY (DateCol) = 1 for first of the month and DateCol = EOMONTH(DateCol) for end of the month date

CONCAT(DATENAME(year, [DateCol ]),
       ' - Month ',
       month([DateCol ]),
       case when day([DateCol ]) = 1             then ' Start'
            when [DateCol] = EOMONTH([DateCol ]) then ' End'
            end) as [month_label]

CodePudding user response：

because you have eomonth function in your february case statement. The EOMONTH function gives last of the month for any date. So all dates satisfy the last date case statement.

you need to change the below line

when month(date_tbl.[Date Name]) = 2 and (day(eomonth(date_tbl.[Date Name])) = 28 OR day(eomonth(date_tbl.[Date Name])) = 29) then concat(cast(year(date_tbl.[Date Name]) as varchar(255)),' - ','Month 2 End')

    when month(date_tbl.[Date Name]) = 2 and (day(date_tbl.[Date Name]) = 28 OR day(date_tbl.[Date Name]) = 29) then concat(cast(year(date_tbl.[Date Name]) as varchar(255)),' - ','Month 2 End')