Joining tables based on two columns based on MAX for another column-CodePudding

I have a table HistoricCall and another Responses. Both tables can be joined using the query below:

SELECT *
FROM HistoricCall h
INNER JOIN Responses r ON h.projectid = r.projectId 
                       AND h.caseId = r.caseId

The HistoricCall table has a column for number of attempts for each time that we've called someone (h.callNumber).

I'm trying to write a query that would display:

SELECT 
    h.callDate,
    r.cellmap,
    COUNT(*)

...but only displays the h.callDate that corresponds to the highest callNumber for each record.

Example data from HistoricCall:

projectId	caseId	callNumber	callDate
100018	0000000001	1	2014-09-11 16:32:11.000
100018	0000000001	2	2014-09-11 20:43:01.000
100018	0000000001	3	2014-09-12 21:09:55.000
100018	0000000001	4	2014-09-13 14:58:14.000
100018	0000000001	5	2014-09-14 16:42:27.000
100018	0000000001	6	2014-09-16 21:54:09.000
100018	0000000001	7	2014-09-16 22:30:24.000
100018	0000000002	1	2014-09-11 16:36:17.000
100018	0000000002	2	2014-09-13 18:05:44.000
100018	0000000002	3	2014-09-14 16:39:03.000

Example data from Responses:

projectId	caseId	cellmap
637850	0000000001	9101
637850	0000000002	9052
637850	0000000003	5071
637850	0000000004	5173
637850	0000000005
637850	0000000006	8062
637850	0000000007	0012
637850	0000000008	5292
637850	0000000009	9230
637850	0000000010	5187

What I'm hoping for is something like this:

projectId	callDate	cellmap	Count
637850	2014-09-11	0012	3
637850	2014-09-11	0014	7
637850	2014-09-12	0012	4
637850	2014-09-12	0014	2

I hope this makes sense and I appreciate your time.

CodePudding user response：

You can convert the celldate to date from datetime and simple group by should work.

SELECT 
    Max(h.projectid) projectid, -- if you need project id of call you can also include it in the group by clause
    h.callDate,
    r.cellmap,
    COUNT(*)
FROM HistoricCall h
INNER JOIN Responses r ON h.projectid = r.projectId 
                       AND h.caseId = r.caseId
group by cast(h.callDate as date), r.cellmap

CodePudding user response：

You can create a join using a subquery to get the max callNumber by projectId and caseId grouping. There's no need to do a count since the callNumber is incremented according to your example above.

Fiddle for reference

SELECT h.projectId              AS "projectId",
       cast(h.callDate AS date) AS "callDate", --cast as date
       r.cellmap                AS "cellmap",
       m.maxCall                AS "Count"
FROM   HistoricCall h
       INNER JOIN Responses r
               ON h.projectid = r.projectId
                  AND h.caseId = r.caseId
       INNER JOIN (SELECT projectid,
                          caseId,
                          max(callNumber) maxCall --get max call number
                   FROM   HistoricCall
                   GROUP  BY projectid,
                             caseId) m
               ON h.projectId = m.projectId
                  AND h.caseId = m.caseId
                  AND h.callNumber = m.maxCall
WHERE  r.cellmap IS NOT NULL --remove if empty cellmap values can be included
GROUP  BY h.projectId,
          cast(h.callDate AS date),
          r.cellmap,
          m.maxCall
ORDER  BY h.projectId,
          cast(h.callDate AS date),
          r.cellmap ASC