CODEWITHSUNDEEP
sqlsql-server
lsx
lsx

Reputation: 3

SQL joining two tables based on date

Here is my situation: I have 2 tables, Question and Answer tables both containing a post_date column. I want to get the count of the questions and answers for the recent 7 days whether or not the count is 0. Refer this for the desired output

I have tried the following code:

SELECT CONVERT(varchar, DATEADD(DAY,0, DATEDIFF(day,0, post_date)),23) AS q_date, COUNT(id) AS q_count
FROM [Question]
WHERE post_date >= DATEADD(day,-7, GETDATE())
GROUP BY DATEADD(DAY,0, DATEDIFF(day,0, post_date))
UNION
SELECT CONVERT(varchar, DATEADD(DAY,0, DATEDIFF(day,0, post_date)),23)AS a_date, COUNT(id) AS a_count
FROM [Answer]
WHERE post_date >= DATEADD(day,-7, GETDATE())
GROUP BY DATEADD(DAY,0, DATEDIFF(day,0, post_date))

But end up getting question's and answer's count merged into one column. Plus, the result is not showing for each day but only the days with count exists. The output is shown as below:

  q_date     q_count
----------   -------
2021-06-11      1
2021-06-12      1
2021-06-13      1
2021-06-13      2
2021-06-14      1

Any suggestions are much appreciated!

Solution

Solution suggested by @Gordon works perfectly fine for this result:

   date      q_count     a_count
----------   -------     -------
2021-06-11      1           0
2021-06-12      0           1
2021-06-13      2           1
2021-06-14      0           1

To display for all 7 days even if no data for some days, this is the way:

;WITH DateTable
AS
(
    SELECT DATEADD(day,-6, CONVERT(date, GETDATE())) AS [DATE]
    UNION ALL
    SELECT DATEADD(dd, 1, [DATE])
    FROM DateTable
    WHERE DATEADD(dd, 1, [DATE]) <= CONVERT(date, GETDATE())
)
SELECT COALESCE(ques.date, ans.date) as date,
   COALESCE(ques.q_count, 0) as q_count,
   COALESCE(ans.a_count, 0) as a_count
FROM 
    (SELECT dt.[DATE], COUNT(q.id) as [Q_COUNT]
    FROM [DateTable] dt
    LEFT JOIN [Question] q
    ON dt.[DATE] = CONVERT(date, q.post_date)
    GROUP BY dt.[DATE]) ques
FULL JOIN
    (SELECT dt.[DATE], COUNT(a.id) as [A_COUNT]
    FROM [DateTable] dt
    LEFT JOIN [Answer] a
    ON dt.[DATE] = CONVERT(date, a.post_date)
    GROUP BY dt.[DATE]) ans
ON ques.DATE = ans.DATE

Output:

   date      q_count     a_count
----------   -------     -------
2021-06-10      0           0
2021-06-11      1           0
2021-06-12      0           1
2021-06-13      2           1
2021-06-14      0           1
2021-06-15      0           0
2021-06-16      0           0

Lastly, big THANK YOU to @Gordon for clearing up my doubt!

Upvotes: 0

Views: 56

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

Use JOIN, FULL JOIN in fact:

SELECT COALESCE(q.date, a.date),
       COALESCE(q.q_count, 0) as q_count,
       COALESCE(a.a_count, 0) a a_count
FROM (SELECT CONVERT(date, post_date) as date, COUNT(id) AS q_count
      FROM [Question]
      WHERE post_date >= DATEADD(day, -7, CONVERT(date, GETDATE()))
      GROUP BY ONVERT(date, post_date)
     ) q FULL JOIN
     (SELECT CONVERT(date, post_date) as date, COUNT(id) AS a_count
      FROM [Answer]
      WHERE post_date >= DATEADD(day, -7, CONVERT(date, GETDATE()))
      GROUP BY CONVERT(date, post_date)
     ) a
     ON a.date = q.date;

Your code has a couple of issues:

  • You can just convert a date/time to a date to remove the time component. Much better approach than converting to a string.
  • GETDATE() has a time component, so the comparison to GETDATE() doesn't do what you (probably) intend).

Upvotes: 1

Related Questions