在count（*）和self join上感到困惑

I want to return all application dates for the current month and for the current year. This must be simple, however I can not figure it out. I know I have 2 dates for the current month and 90 dates for the current year. Right, Left, Outer, Inner I have tried them all, just throwing code at the wall trying to see what will stick and none of it works. I either get 2 for both columns or 180 for both columns. Here is my latest select statement.

我想返回当月和当年的所有申请日期。这一定很简单,但我无法弄清楚。我知道我当前有2个日期,当年有90个日期。右,左,外,内在我已经尝试了所有这些,只是在墙上抛出代码,试图看到什么会坚持,没有一个工作。我要么两列都得2,要么两列得180。这是我最新的选择声明。

SELECT count(a.evdtApplication) AS monthApplicationEntered,  
       count (b.evdtApplication) AS yearApplicationEntered
FROM tblEventDates a 
RIGHT OUTER JOIN tblEventDates b ON a.LOANid = b.loanid 
WHERE datediff(mm,a.evdtApplication,getdate()) = 0 
      AND datediff(yy,a.evdtApplication, getdate()) = 0 
      AND datediff(yy,b.evdtApplication,getdate()) = 0

1 个解决方案

#1

You don't need any joins at all.

您根本不需要任何连接。

You want to count the loanID column from tblEventDates, and you want to do it conditionally based on the date matching the current month or the current year.

您想要从tblEventDates计算loanID列,并且您希望根据与当前月份或当前年份匹配的日期有条件地执行此操作。

SO:

SELECT SUM( CASE WHEN Month(a.evdtApplication) = MONTH(GEtDate() THEN 1 END) as monthTotal,
        count(*)
FROM tblEventDates a
WHERE a.evdtApplication BETWEEN '2008-01-01' AND '2008-12-31'

What that does is select all the event dates this year, and add up the ones which match your conditions. If it doesn't match the current month it won't add 1. Actually, don't even need to do a condition for the year because you're just querying everything for that year.

这样做是选择今年的所有活动日期,并添加符合您条件的活动日期。如果它与当前月份不匹配则不会增加1.实际上,甚至不需要为今年做一个条件,因为你只是查询那一年的所有内容。

#1