Check dates within same table

Question

This is my table from SQL and the output that should be. What I need is to merge both dates and their entries.

I tried both queries, IN and OUT, but unable to merge.

IN:

SELECT 
    MAX(one.TOTAL_IN) AS TOTAL_IN, one.Date_In 
FROM
    (SELECT 
         CAST(ST.DateIn AS DATE) AS Date_In, 
         COUNT(ST.ID) OVER (ORDER BY CAST(ST.Datein AS DATE)) AS TOTAL_IN 
     FROM 
         Table ST) AS one 
GROUP BY
    one.Date_In

OUT:

SELECT 
    MAX(two.TOTAL_OUT) AS TOTAL_OUT, two.Date_Out
FROM
    (SELECT 
         CAST(ST.DateOut AS DATE) AS Date_Out,
         COUNT(ST.ID) OVER (PARTITION BY CAST(ST.DateOut AS DATE) 
                            ORDER BY CAST(ST.DateOut AS DATE)) AS TOTAL_OUT
     FROM 
         Table ST) AS two
GROUP BY
    two.Date_Out

ID	Date_IN	Date_OUT
1	2025-06-02	2025-06-13
2	2025-06-02
3	2025-06-03	2025-06-07
4	2025-06-04
5	2025-06-04
6	2025-06-04
7	2025-06-06	2025-06-07
8	2025-06-07
9	2025-06-08	2025-06-08
10	2025-06-11	2025-06-11
11	2025-06-12
12	2025-06-13
13	2025-06-14

Desired output:

TOTAL_IN	DATE	TOTAL_OUT
2	2025-06-02
1	2025-06-03
3	2025-06-04
1	2025-06-06
1	2025-06-07	2
1	2025-06-08	1
1	2025-06-11	1
1	2025-06-12
1	2025-06-13	1
1	2025-06-14

Answer 1

I'd use a union all between two queries of the table, one for the date_ins and one for the date_outs, and then sum them:

SELECT   date, SUM(has_in) AS total_in, SUM(has_out) AS total_out
FROM     (SELECT date_in AS date, 1 AS has_in, NULL AS has_out
          FROM   mytable
          WHERE  date_in IS NOT NULL
          UNION ALL
          SELECT date_out AS date, NULL AS has_in, 1 AS has_out
          FROM   mytable
          WHERE  date_out IS NOT NULL) t
GROUP BY date

Answer 2

The separate IN and OUT queries are more complicated than need be, and I think this is what stops you from getting to the final query.

Let's take the IN query. The subquery is:

SELECT
  CAST(st.datein AS DATE) AS date_in, 
  COUNT(st.id) OVER (ORDER BY CAST(st.datein AS DATE)) AS total_in 
FROM table st

This gets you each row with the date_in reduced to a date (although the name suggests that it is already a date) and a count over the date. Thus your table rows

ID	DATEIN	DATEOUT
1	2025-06-02	2025-06-13
2	2025-06-02

become

DATE_IN	TOTAL_IN
2025-06-02	2
2025-06-02	2

If it were 1000 rows for a date, then you'd get 1000 rows for that date, each with the count 1000. What good does that do?

Then, in your main query you finally aggregate these rows, so as to get only one row per date. You do this with a GROUP BY one.date_in and a MAX(one.total_in), but you could just as well SELECT DISTINCT, because you want to get rid of all the dulicates that you just produced yourself.

COUNT OVER makes no sense here. If you want to aggregate your rows, just GROUP BY and COUNT:

SELECT CAST(st.datein AS DATE) AS date_in, COUNT(*) AS total_in
FROM table st
GROUP BY CAST(st.datein AS DATE);

Now as the the desired result: You want one row per date (whether it be in or out) from the table. This is the base you need, and you can get this easily with a union of the two dates. Then all you need is count matching in and out:

WITH
  all_dates AS 
  (
    SELECT CAST(datein AS DATE) as dt FROM mytable
    UNION
    SELECT CAST(dateout AS DATE) as dt FROM mytable
  )
SELECT 
  ad.dt,
  (
    SELECT COUNT(*)
    FROM mytable t 
    WHERE CAST(t.datein AS DATE) = ad.dt
  ) AS total_in,
  (
    SELECT COUNT(*)
    FROM mytable t 
    WHERE CAST(t.dateout AS DATE) = ad.dt
  ) AS total_out
FROM all_dates ad
ORDER BY ad.dt;

(This can be optimized, because it is usually a bad idea to access rows by a function like CAST.)