混淆案例陳述值

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

我的結果如下所示：

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

為什么我的二月標簽每行都有“結束”值，而不是當月的最后一天？如何修復它，使其僅標記每個月的第一天和最后一天？

uj5u.com熱心網友回復：

你不需要CASE每個月的所有這些表達。

只需檢查DAY (DateCol) = 1月初和DateCol = EOMONTH(DateCol)月底的日期

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

uj5u.com熱心網友回復：

因為您的 2 月案例陳述中有 eomonth 功能。EOMONTH 函式給出任何日期的最后一個月。所以所有日期都滿足最后一個日期案例陳述。

您需要更改以下行

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')

標籤：sql sql服务器 tsql

上一篇：SQLServer：如何獲取以前的資料

下一篇：使用JOIN從三個表中獲取資料