在这种情况下，我相当确定可以在不使用任何CASE语句的情况下编写查询，实际上是:

I'm fairly certain in this case the query can be written without the use of any CASE statements, actually:

DECLARE @From DATE = '20140101'
declare @To DATE = '20140201'

SELECT Mortgage.lender, Mortgage.amountRequested, Profile.caseTypeId,
       COUNT(Issue.issued) as issued,
       COUNT(Pass.passed) as passed,
       COUNT(Receive.received) as received,
       COUNT(Offer.offered) as offered
FROM BPS.dbo.tbl_Profile_Mortgage as Mortgage
JOIN BPS.dbo.tbl_Profile as Profile
  ON Mortgage.fk_profileId = Profile.id
     AND Profile.caseTypeId = 2
LEFT JOIN (VALUES (1, @From, @To)) Issue(issued, rangeFrom, rangeTo)
       ON Mortgage.DateAppIssued >= Issue.rangeFrom
          AND Mortgage.DateAppIssued < Issue.rangeTo
LEFT JOIN (VALUES (2, @From, @To)) Pass(passed, rangeFrom, rangeTo)
       ON Mortgage.DatePassed >= Pass.rangeFrom
          AND Mortgage.DatePassed < Pass.rangeTo
LEFT JOIN (VALUES (3, @From, @To)) Receive(received, rangeFrom, rangeTo)
       ON Mortgage.DateAppRcvd >= Receive.rangeFrom
          AND Mortgage.DateAppRcvd < Receive.rangeTo
LEFT JOIN (VALUES (4, @From, @To)) Offer(offered, rangeFrom, rangeTo)
       ON Mortgage.DateOffered >= Offer.rangeFrom
          AND Mortgage.DateOffered < Offer.rangeTo
WHERE Mortgage.lender > ''
      AND (Issue.issued IS NOT NULL 
           OR Pass.passed IS NOT NULL
           OR Receive.received IS NOT NULL
           OR Offer.offered IS NOT NULL)
GROUP BY Mortgage.lender, Mortgage.amountRequested, Profile.caseTypeId

(未经测试，因为我缺少提供的数据集).

(not tested, as I lack a provided data set).

...好吧，一些解释是有条理的，因为其中有些是不直观的.

... Okay, some explanations are in order, because some of this is slightly non-intuitive.

首先，

First off, read this blog entry for tips about dealing with date/time/timestamp ranges (interestingly, this also applies to all other non-integral types). This is why I modified the @To date - so the range could be safely queried without needing to convert types (and thus ignore indices). I've also made sure to choose a safe format - depending on how you're calling this query, this is a non issue (ie, parameterized queries taking an actual Date type are essentially format-less).

       ......
       COUNT(Issue.issued) as issued,
    ......
LEFT JOIN (VALUES (1, @From, @To)) Issue(issued, rangeFrom, rangeTo)
       ON Mortgage.DateAppIssued >= Issue.rangeFrom
          AND Mortgage.DateAppIssued < Issue.rangeTo
.......

COUNT(*)和COUNT(<expression>)有什么区别?如果<expression>计算为null，则将其忽略.因此LEFT JOIN s;如果抵押贷款条目不在该列的给定日期范围内，则虚拟表不会附加，也没有可计数的列.不幸的是，我不确定虚拟表，LEFT JOIN和COUNT()之间的相互作用在优化程序中会如何出现-联接应该能够使用索引，但是我不知道它是否足够智能也可以将其用于COUNT().

What's the difference between COUNT(*) and COUNT(<expression>)? If <expression> evaluates to null, it's ignored. Hence the LEFT JOINs; if the entry for the mortgage isn't in the given date range for the column, the dummy table doesn't attach, and there's no column to count. Unfortunately, I'm not sure how the interplay between the dummy table, LEFT JOIN, and COUNT() here will appear to the optimizer - the joins should be able to use indices, but I don't know if it's smart enough to be able to use that for the COUNT() here too....

          (Issue.issued IS NOT NULL 
           OR Pass.passed IS NOT NULL
           OR Receive.received IS NOT NULL
           OR Offer.offered IS NOT NULL)

这实际上是在告诉它忽略没有至少一个列的行.在任何情况下都不会计数"(嗯，它们很可能具有0)-没有要考虑的功能数据-但是它们会会显示在结果中，从而可能不是您想要的.我不确定优化器是否足够聪明，可以使用它来限制它对哪些行进行操作-也就是说，将JOIN条件转换为一种限制各种日期列的方式，就像它们在WHERE中一样子句也一样.如果查询运行缓慢，请尝试将日期限制添加到WHERE子句中，看看是否有帮助.

This is essentially telling it to ignore rows that don't have at least one of the columns. They wouldn't be "counted" in any case (well, they'd likely have 0) - there's no data for the function to consider - but they would show up in the results, which probably isn't what you want. I'm not sure if the optimizer is smart enough to use this to restrict which rows it operates over - that is, turn the JOIN conditions into a way to restrict the various date columns, as if they were in the WHERE clause too. If the query runs slow, try adding the date restrictions to the WHERE clause and see if it helps.