One last question on this... I have been trying to figure this out for a while, but I can't seem to come up with the correct SQLite code. This code works correctly:
```
WITH LastEntries (ProjID, pl_insert, pe_insert)
AS
(
SELECT projid,
max(InsertDate),
(SELECT max(insertdate) FROM project_extras where projid = e.projid)
FROM project_list e
GROUP BY projid
)
SELECT count(c.CreatedDate) AS Aug, a.Project_Type , a.ProjID
FROM Project_List AS a
LEFT JOIN Project_Extras AS b
ON a.ProjID == b.ProjID
AND
b.FinCarryOver != 'y'
AND
b.MonthlyRpt = 'y'
AND
b.BudgetYear = '2021'
LEFT JOIN Project_Highlights AS c
ON b.ProjID = c.ProjID
AND
c.CreatedDate LIKE '2021-08%'
LEFT JOIN LastEntries AS f
WHERE
a.Project_Delivered != 'Yes'
AND
a.PMO_Board_Report != 'No'
AND
a.Status == 'Acknowledged'
AND
a.InsertDate = f.pl_insert
AND
b.InsertDate = f.pe_insert
AND
a.ProjID = f.projid
GROUP BY a.ProjID
ORDER BY a.Manager, a.ProjID
;
which results to:
2|Activity|PR0000019131
3|Project|PR0000019132
3|Project|PR0000019155
3|Project|PR0000019175
3|Project|PR0000019586
0|Project|PR0000018388
2|Project|PR0000018390
2|Project|PR0000018391
0|Activity|PR0000019764
This is correct.
```
But this other code gives the wrong result,
```
WITH LastEntries (ProjID, pl_insert, pe_insert)
AS
(
SELECT projid,
max(InsertDate),
(SELECT max(insertdate) FROM project_extras where projid = e.projid)
FROM project_list e
GROUP BY projid
)
SELECT count(d.CreatedDate) AS Jul, count(c.CreatedDate) AS Aug, a.Project_Type , a.ProjID
FROM Project_List AS a
LEFT JOIN Project_Extras AS b
ON a.ProjID == b.ProjID
AND
b.FinCarryOver != 'y'
AND
b.MonthlyRpt = 'y'
AND
b.BudgetYear = '2021'
LEFT JOIN Project_Highlights AS c
ON b.ProjID = c.ProjID
AND
c.CreatedDate LIKE '2021-08%'
LEFT JOIN Project_Highlights AS d
ON b.ProjID = d.ProjID
AND
d.CreatedDate LIKE '2021-07%'
LEFT JOIN LastEntries AS f
WHERE
a.Project_Delivered != 'Yes'
AND
a.PMO_Board_Report != 'No'
AND
a.Status == 'Acknowledged'
AND
a.InsertDate = f.pl_insert
AND
b.InsertDate = f.pe_insert
AND
a.ProjID = f.projid
GROUP BY a.ProjID
ORDER BY a.Manager, a.ProjID
;
which results to:
4|4|Activity|PR0000019131
6|6|Project|PR0000019132
6|6|Project|PR0000019155
6|6|Project|PR0000019175
6|6|Project|PR0000019586
2|0|Project|PR0000018388
4|4|Project|PR0000018390
4|4|Project|PR0000018391
0|0|Activity|PR0000019764
I am expecting:
2|2|Activity|PR0000019131
2|3|Project|PR0000019132
2|3|Project|PR0000019155
2|3|Project|PR0000019175
2|3|Project|PR0000019586
2|0|Project|PR0000018388
2|2|Project|PR0000018390
2|2|Project|PR0000018391
0|0|Activity|PR0000019764
```
I know it's something simple, again, but I can't seem to put my finger on the problem. Any thoughts? Thanks.
josé