SQL选择大小写组合

Question

我有两个select case语句，我想以某种方式合并在一起。我已经尝试了不同的嵌套，到目前为止，我得到的都是错误。我使用SQL Server 2008为此，并希望它是向后兼容SQL 2005如果可能的话，因为我们有第二个实例设置，那是2005。

我正在做的是查看账户上的付款数量，并试图决定是每周，两周，每月还是两个月。

取决于计划付款的数量，取决于我使用哪个日期来计算付款之间的天数。因为没有真正的每周、每两周等选择，所以我们使用的天数范围是基于他们第一次计划付款和最后一次计划付款之间的天数，除以付款次数。

这是我的两个case语句:第一个语句告诉我两次付款之间的天数。

    case NoPymnts
    when 1 then 0
    when 2 then (datediff(d, DueDt1, DueDt2)/NoPymnts)
    when 3 then (datediff(d, DueDt1, DueDt3)/NoPymnts)
    when 4 then (datediff(d, DueDt1, DueDt4)/NoPymnts)
    when 5 then (datediff(d, DueDt1, DueDt5)/NoPymnts)
    when 6 then (datediff(d, DueDt1, DueDt6)/NoPymnts)
    when 7 then (datediff(d, DueDt1, DueDt7)/NoPymnts)
    when 8 then (datediff(d, DueDt1, DueDt8)/NoPymnts)
    when 9 then (datediff(d, DueDt1, DueDt9)/NoPymnts)
    when 10 then (datediff(d, DueDt1, DueDt10)/NoPymnts)
    when 11 then (datediff(d, DueDt1, DueDt11)/NoPymnts)
    when 12 then (datediff(d, DueDt1, DueDt12)/NoPymnts)
    end as DayCount

这个包含决定支付频率的逻辑:

    case
    when DayCount = 0 then 'SinglePayment'
    when DayCount <=7 then 'Weekly'
    when DayCount >7 and DayCount <= 21 then 'Bi-Weekly'
    when DayCount >21 and Daycount <= 42 then 'Monthly'
    when DayCount > 42 then 'Bi-Monthly'
    End As Frequency

我知道有一个可行的选项，但它又大又乱，那就是将第二个case放入第一个case语句中的每个选项的子case语句中。我真的不想这样。我想避免的第二个选项是使用临时表。虽然，如果不可能做到我在这里尝试的，我可能会被迫使用一个。

谢谢你的帮助!

Answer 1

为第二个case创建一个用户定义函数

CREATE FUNCTION GetFrequencyFromDayCount 
(
    @DayCount INT
)
RETURNS VARCHAR(50)
AS
BEGIN
    DECLARE @Freq NVARCHAR(50)
    SELECT @Freq = case
    when @DayCount = 0 then 'SinglePayment'
    when @DayCount <=7 then 'Weekly'
    when @DayCount >7 and @DayCount <= 21 then 'Bi-Weekly'
    when @DayCount >21 and @DayCount <= 42 then 'Monthly'
    when @DayCount > 42 then 'Bi-Monthly'
    End 
    RETURN @Freq
END
GO

在第一个case语句中调用函数，类似下面的

select
  case NoPymnts
    when 1 then 0
    when 2 then GetFrequencyFromDayCount((datediff(d, DueDt1, DueDt2)/NoPymnts))
    when 3 then GetFrequencyFromDayCount((datediff(d, DueDt1, DueDt3)/NoPymnts))
    when 4 then GetFrequencyFromDayCount((datediff(d, DueDt1, DueDt4)/NoPymnts))
    when 5 then GetFrequencyFromDayCount((datediff(d, DueDt1, DueDt5)/NoPymnts))
    when 6 then GetFrequencyFromDayCount((datediff(d, DueDt1, DueDt6)/NoPymnts))
    when 7 then GetFrequencyFromDayCount((datediff(d, DueDt1, DueDt7)/NoPymnts))
    when 8 then GetFrequencyFromDayCount((datediff(d, DueDt1, DueDt8)/NoPymnts))
    when 9 then GetFrequencyFromDayCount((datediff(d, DueDt1, DueDt9)/NoPymnts))
    when 10 then GetFrequencyFromDayCount((datediff(d, DueDt1, DueDt10)/NoPymnts))
    when 11 then GetFrequencyFromDayCount((datediff(d, DueDt1, DueDt11)/NoPymnts))
    when 12 then GetFrequencyFromDayCount((datediff(d, DueDt1, DueDt12)/NoPymnts))
    end as DayCount 

Answer 2

只需将第二种情况下DayCount的值替换为第一种情况下使用的计算，因为除了单笔付款外，无论NoOfPymts如何，您似乎都在进行相同的计算:

case
    when NoPymnts = 1 then 'SinglePayment'
    when (datediff(d, DueDt1, DueDt2)/NoPymnts)<=7 then 'Weekly'
    when (datediff(d, DueDt1, DueDt2)/NoPymnts)>7 
         and (datediff(d, DueDt1, DueDt2)/NoPymnts)<= 21 then 'Bi-Weekly'
    when (datediff(d, DueDt1, DueDt2)/NoPymnts)>21 
         and (datediff(d, DueDt1, DueDt2)/NoPymnts)<= 42 then 'Monthly'
    when (datediff(d, DueDt1, DueDt2)/NoPymnts)> 42 then 'Bi-Monthly'
    End As Frequency

Answer 3

你只是在寻找一种方法先找到天数计数，然后在此基础上进行构建吗?然后使用派生表查询:

select
  some_column,
  other_column,
  case
    when DayCount = 0 then 'SinglePayment'
    when DayCount <=7 then 'Weekly'
    when DayCount >7 and DayCount <= 21 then 'Bi-Weekly'
    when DayCount >21 and Daycount <= 42 then 'Monthly'
    when DayCount > 42 then 'Bi-Monthly'
  End As Frequency
from
(
  select 
    some_column,
    other_column,
    case NoPymnts
      when 1 then 0
      when 2 then (datediff(d, DueDt1, DueDt2)/NoPymnts)
      when 3 then (datediff(d, DueDt1, DueDt3)/NoPymnts)
      when 4 then (datediff(d, DueDt1, DueDt4)/NoPymnts)
      when 5 then (datediff(d, DueDt1, DueDt5)/NoPymnts)
      when 6 then (datediff(d, DueDt1, DueDt6)/NoPymnts)
      when 7 then (datediff(d, DueDt1, DueDt7)/NoPymnts)
      when 8 then (datediff(d, DueDt1, DueDt8)/NoPymnts)
      when 9 then (datediff(d, DueDt1, DueDt9)/NoPymnts)
      when 10 then (datediff(d, DueDt1, DueDt10)/NoPymnts)
      when 11 then (datediff(d, DueDt1, DueDt11)/NoPymnts)
      when 12 then (datediff(d, DueDt1, DueDt12)/NoPymnts)
    end as DayCount
  from mytable
) myderivedtable;