在SQL中将月份标志转换为日期

Question

我有以下表格格式；

tripID  
departureDate (date)  
availableJan (bit)  
availableFeb (bit)  
availableMar (bit)  
availableApr (bit)  
availableMay (bit)  
availableJun (bit)  
availableJul (bit)     
availableAug (bit)    
availableSep (bit)  
availableOct (bit)  
availableNov (bit)  
availableDec (bit)  

旅行将有任一"可用"标志的选择或出发日期。

我想做的是一个数据库函数，它将根据当前日期将标志转换为可用日期。

因此，如果一次旅行的出发日期为空，并且可以在8月、9月、10月进行，那么给定当前日期为2016年5月8日。我想要一个函数来返回日期2016年8月1日。

这里的逻辑是，函数返回当前日期后下一个可用月份的第一天。

更多的例子，例如今天的日期是2016年6月6日；

月份标记为true：1月、2月、3月
预计产量：2017年1月1日

月份标记为true：1月、6月
预期产量：2016年6月1日

月份标记为true：六月、七月、八月、十二月
预期产量：2016年6月1日

我曾尝试过大if语句、临时表，但我想知道是否有一种更聪明的方法可以实现这一点，而不会杀死处理器。

非常感谢您的帮助，

谢谢，

Nick

Answer 1

每当你想写一堆这样的UNION语句时，请考虑fold。你想要的是旅行的最短日期。所以，首先确定日期，然后把它们放在一列中，然后选择最小的一个。整个shebang可以是一个select语句中的一个。

首先，让自己成为一个由12行组成的静态表，其中包含1-12的整数。让我们称之为Months。然后

with start as (
    select dt = dateadd( YEAR,
                         datediff(YEAR, '20000101', getdate()),
                         '20000101' )
)
select TripId, min(departure) as Departure
    select TripID
         , case month
           when 1 then case availableJan 
                       when 1 then dateadd(MONTH,month,start.dt) end
           when 2 then case availableFeb 
                       when 1 then dateadd(MONTH,month,start.dt) end
           --- 10 more times --
           end as departure
    from itinerary cross join Months
) as M
where departure >= getdate()
group by TripID

这种技术被称为折叠，可能被认为是枢转的反面（即将行排列为列）。DBMS通常比UNIONS序列执行效率高得多，因为即使有很多SQL，也只提到了一个表。可以通过对数据进行单次传递来产生结果；服务器唯一的问题是如何处理每一行。它也不那么冗长，一旦你习惯了，它是一个很容易识别的成语。

Answer 2

从今天开始的第一个可用的月1日

with start as (
  select dt = dateadd(YEAR,datediff(YEAR,'20000101',getdate()),'20000101')
)
select tripID 
 , firstDate = (
   select min(d) 
   from (
      select d = start.dt where availableJan = 1
      union all
      select d = dateadd(MONTH,1,start.dt) where availableFeb = 1
      union all
      select d = dateadd(MONTH,2,start.dt) where availableMar = 1
      -- ...
   )
   where d >= getdate() )
from mytable
join start;

Answer 3

根据Serg的回答（谢谢！），我通过以下内容达到了预期的结果；

DECLARE @firstDayOfCurrentYear date = dateadd(YEAR,datediff(YEAR,'20000101',getdate()),'20000101')
DECLARE @firstOfCurrenyMonth date = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101)
SELECT 
    itinerary.*,            
    COALESCE 
    (
        departureDate,
        (
            SELECT MIN(d) FROM 
            (
                SELECT @firstDayOfCurrentYear                   AS d WHERE availableJan = 1 UNION ALL
                SELECT DATEADD(MONTH,1,@firstDayOfCurrentYear)   AS d WHERE availableFeb = 1 UNION ALL
                SELECT DATEADD(MONTH,2,@firstDayOfCurrentYear)   AS d WHERE availableMar = 1 UNION ALL
                SELECT DATEADD(MONTH,3,@firstDayOfCurrentYear)   AS d WHERE availableApr = 1 UNION ALL
                SELECT DATEADD(MONTH,4,@firstDayOfCurrentYear)   AS d WHERE availableMay = 1 UNION ALL
                SELECT DATEADD(MONTH,5,@firstDayOfCurrentYear)   AS d WHERE availableJun = 1 UNION ALL
                SELECT DATEADD(MONTH,6,@firstDayOfCurrentYear)   AS d WHERE availableJul = 1 UNION ALL
                SELECT DATEADD(MONTH,7,@firstDayOfCurrentYear)   AS d WHERE availableAug = 1 UNION ALL
                SELECT DATEADD(MONTH,8,@firstDayOfCurrentYear)   AS d WHERE availableSep = 1 UNION ALL
                SELECT DATEADD(MONTH,9,@firstDayOfCurrentYear)   AS d WHERE availableOct = 1 UNION ALL
                SELECT DATEADD(MONTH,10,@firstDayOfCurrentYear)  AS d WHERE availableNov = 1 UNION ALL
                SELECT DATEADD(MONTH,11,@firstDayOfCurrentYear)  AS d WHERE availableDec = 1 UNION ALL
                SELECT DATEADD(MONTH,12,@firstDayOfCurrentYear)  AS d WHERE availableJan = 1 UNION ALL
                SELECT DATEADD(MONTH,13,@firstDayOfCurrentYear)  AS d WHERE availableFeb = 1 UNION ALL
                SELECT DATEADD(MONTH,14,@firstDayOfCurrentYear)  AS d WHERE availableMar = 1 UNION ALL
                SELECT DATEADD(MONTH,15,@firstDayOfCurrentYear)  AS d WHERE availableApr = 1 UNION ALL
                SELECT DATEADD(MONTH,16,@firstDayOfCurrentYear)  AS d WHERE availableMay = 1 UNION ALL
                SELECT DATEADD(MONTH,17,@firstDayOfCurrentYear)  AS d WHERE availableJun = 1 UNION ALL
                SELECT DATEADD(MONTH,18,@firstDayOfCurrentYear)  AS d WHERE availableJul = 1 UNION ALL
                SELECT DATEADD(MONTH,19,@firstDayOfCurrentYear)  AS d WHERE availableAug = 1 UNION ALL
                SELECT DATEADD(MONTH,20,@firstDayOfCurrentYear)  AS d WHERE availableSep = 1 UNION ALL
                SELECT DATEADD(MONTH,21,@firstDayOfCurrentYear)  AS d WHERE availableOct = 1 UNION ALL
                SELECT DATEADD(MONTH,22,@firstDayOfCurrentYear)  AS d WHERE availableNov = 1 UNION ALL
                SELECT DATEADD(MONTH,23,@firstDayOfCurrentYear)  AS d WHERE availableDec = 1
            ) AS dateTable where d >= @firstOfCurrenyMonth
        )
    ) AS inclusiveDepartureDate
FROM 
    itinerary

这说明了当月份是一年中的一部分时，所以下一个出发日期是在下一年（即我联合了24行，而不仅仅是12行）。

我使用COALESCE仅在正常departureDate为null时执行此操作。