0

I need to write a query in MS Access to get a count of operation by 2 dates column. Structure of my table is

start_date end_date f1 f2 f3
2022-12-01 2022-12-05 f1 f2 f3
2022-11-15 2022-12-07 f1 f2 f3
2022-12-06 2022-12-07 f1 f2 f3
2022-11-15 2022-12-06 f1 f2 f3

I need output as for all dates of a month

date Total start_date on date Total end_date on date
2022-11-15 1 0
2022-12-01 1 0
2022-12-05 0 1
2022-12-06 1 1
2022-12-07 0 2

What I have done is (which is incorrect, since it is possible on a given start_date, the count is zero and for some end_date count is zero)

SELECT start_date, count(start_date) as startcount, tbl2.endcount FROM tbl
FULL OUTER JOIN (SELECT end_date, count(end_date) as endcount FROM tbl GROUP BY end_date) as tbl2
ON start_date=tbl2.end_date
GROUP BY start_date
ORDER BY start_date

Any suggestions?

I have already checked for the existing answers, but they do not match the requirements, since in other answers they are doing a UNION of result, which is not my question.

Sawan
  • 2,152
  • 1
  • 14
  • 14
  • To get your question reopened, please provide sample data and expected result. [How to ask a good SQL question](https://meta.stackoverflow.com/a/271056/3820271). – Andre Dec 07 '22 at 15:56
  • Sure. I have added sample input and expected output. – Sawan Dec 07 '22 at 17:24
  • ... sample data *in table format*. :-o https://stackoverflow.com/editing-help#tables – Andre Dec 08 '22 at 10:19
  • Apologies. Thanks for the pointers. Made it readable in tabular format. – Sawan Dec 08 '22 at 18:13
  • The sample data has no record with end_date = 2022-12-06, I guess you are missing a row there? – Andre Dec 08 '22 at 23:54
  • And: I don't see why the linked duplicate doesn't apply. Access has no `FULL OUTER JOIN`, so your SQL cannot work. It must be simulated with a UNION query. But this UNION query should give you the desired result. – Andre Dec 08 '22 at 23:56
  • How would a UNION add total_end_date column to the result? One query can give me result of total_start_date, but merging with the next column is the challenge hence different from the linked duplicate question. – Sawan Dec 09 '22 at 02:18
  • You need to GROUP BY and COUNT separately for both columns, providing 0 as value for the other column. You can UNION both cases, and then SUM over the results to merge the rows where dates exists in both columns - this is quite similar to a question I asked, see the answer here: https://stackoverflow.com/a/32274957/3820271 – Andre Dec 09 '22 at 16:00
  • Or you can treat the special case separately, with a 3rd subquery using INNER JOIN. Then UNION ALL the 3 results. This is the 2nd approach in the top answer in the linked duplicate. – Andre Dec 09 '22 at 16:03

1 Answers1

2

I didn't realize that only one vote was needed to reopen and answer...
I wrote most in the comments, but the first approach would look like this:

SELECT TheDate, SUM(startcount) AS TotalStart, SUM(endcount) AS TotalEnd
FROM (
    SELECT start_date as TheDate, count(*) as startcount, 0 AS endcount 
    FROM tbl
    GROUP BY start_date
    
    UNION ALL
    
    SELECT end_date as TheDate, 0 as startcount, count(*) as endcount 
    FROM tbl 
    GROUP BY end_date
)
GROUP BY TheDate

And JJ32 is correct, this can be simplified to this:

SELECT TheDate, SUM(startcount) AS TotalStart, SUM(endcount) AS TotalEnd
FROM (
    SELECT start_date as TheDate, 1 as startcount, 0 AS endcount 
    FROM tbl
    
    UNION ALL
    
    SELECT end_date as TheDate, 0 as startcount, 1 as endcount 
    FROM tbl 
)
GROUP BY TheDate

This is basically an UNPIVOT operation.

Andre
  • 26,751
  • 7
  • 36
  • 80
  • 1
    This works even without grouping in the inner select. Just 1 as startcount/1 as endcount wil suffice. – JJ32 Dec 09 '22 at 17:48
  • This doesn't handle the last row of my output. (2022-12-07 0 2) – Sawan Dec 11 '22 at 11:43
  • @Sawan Did you use UNION ALL and not UNION? Please show the results in your question if it does not work. – JJ32 Dec 11 '22 at 14:54
  • @Sawan: it does for me, using your test data (without columns f1..f3, they don't seem to play any role). Using count(*) is better, but both versions worked for me. – Andre Dec 11 '22 at 21:32