Confused on count(*) and self joins

Question

Hello

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.

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

Answer 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.

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.