Joining multiple date fields to calendar table

Question

I have a table, let's call it Records, where the relevant data is organized as such:

| Employee | SubmissionDate | FirstReviewDate | SecondReviewDate |
  Anne       2017-10-02       2017-10-03        2017-10-10          
  Bernard    2017-10-03       2017-10-05        2017-10-10
  Charlene   2017-10-06       2017-10-09        2017-10-09
  Danielle   2017-10-02       2017-10-03        2017-10-09
  Anne       2017-10-03       2017-10-03        2017-10-09

Every time an employee makes a submission, a new entry is added with a SubmissionDate. The record is later edited to include then the first and second reviews take place.

I also have a calendar table called Calendar with a field called TheDate which has dates for every day this year. What I would like to do is associate SubmissionDate, FirstReviewDate, and SecondReview date with Calendar.TheDate so that I can do a count for all three fields for any given day.

I have tried the following code:

SELECT Employee,
       Count(SubmissionDate) AS "Submissions",
       Count(FirstReviewDate) AS "First Reviews",
       Count(SecondReviewDate) AS "Second Reviews"
  FROM Records
  LEFT JOIN Calendar ON Records.SubmissionDate = Calendar.TheDate
   AND Records.FirstReviewDate = Calendar.TheDate
   AND Records.SecondReviewDate = Calendar.TheDate
 WHERE TheDate = '2017-10-11'

All of the variations of this I have tried output nothing:

| Employee | Submissions | First Reviews | Second Reviews |

My desired code would look like this:

SELECT Employee,
       Count(SubmissionDate),
       Count(FirstReviewDate),
       Count(SecondReviewDate)
  FROM ???
 WHERE TheDate = '2017-10-03'

where ??? would be the proper join. Using the desired code block (including the where clause), the desired output would like this for the example data provided:

| Employee | Submissions | First Reviews | Second Reviews |
  Anne       1             2               0
  Bernard    1             0               0
  Charlene   0             0               0
  Danielle   0             1               0

I am not sure how to do this join. I have read many resources about calendar tables, but the examples always include joining tables that each have a single date identifier. My problem is that I have one table with three date fields that need to be associated with Calendar.TheDate.

I am arranging my data like this so that the data can be visualized in Qlik Sense. I would like users to be able to select TheDate from a filter panel and have it aggregate all three fields for the date specified (essentially identical to the WHERE clause in my example code).

Answer 1

Try below code

create table #recs (
  Employee varchar(10), 
  SubmissionDate date, 
  FirstReviewDate date, 
  SecondReviewDate date
)

insert into #recs values
('Anne',       '2017-10-02', '2017-10-03', '2017-10-10'),          
('Bernard',    '2017-10-03', '2017-10-05', '2017-10-10'),
('Charlene',   '2017-10-06', '2017-10-09', '2017-10-09'),
('Danielle',   '2017-10-02', '2017-10-03', '2017-10-09'),
('Anne',       '2017-10-03', '2017-10-03', '2017-10-09')

select Employee
    , sum(IIF(SubD.Date_Value = #recs.SubmissionDate, 1, 0)) AS Submissions 
    , sum(IIF(SubD.Date_Value = #recs.FirstReviewDate, 1, 0)) AS [First Reviews]
    , sum(IIF(SubD.Date_Value = #recs.SecondReviewDate, 1, 0)) AS [Second Reviews]
from #recs
    left join DimDate SubD on SubD.Date_Value = '2017-10-03' and
        (SubD.Date_Value = #recs.SubmissionDate
        or SubD.Date_Value = #recs.FirstReviewDate
        or SubD.Date_Value = #recs.SecondReviewDate)
group by Employee

If your SQL Server is older than 2012, use CASE instead of IIF

select Employee
    , sum(case when SubD.Date_Value = #recs.SubmissionDate then 1 else 0 end) AS Submissions 
    , sum(case when SubD.Date_Value = #recs.FirstReviewDate then 1 else 0 end) AS [First Reviews]
    , sum(case when SubD.Date_Value = #recs.SecondReviewDate then 1 else 0 end) AS [Second Reviews]
from #recs
    left join DimDate SubD on SubD.Date_Value = '2017-10-03' and
        (SubD.Date_Value = #recs.SubmissionDate
        or SubD.Date_Value = #recs.FirstReviewDate
        or SubD.Date_Value = #recs.SecondReviewDate)
group by Employee