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日

• Thorsten Kettner: The columns are called datein and dateout. Sounds like dates to me. Why do you think you must CAST AS DATE? Either these column names are misnomers or the cast is not appropriate.

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.)

• user31123186: Many TKS Thorsten, really simple as elegant. You are right, the name suggests that it is already a date, and I should not use CAST but field is defined as datetime and I did want to remove the time.

Answer 3

SELECT a.id AS booking1,
 b.id AS booking2,
 a.start_date, a.end_date,
 b.start_date, b.end_date
FROM bookings a
JOIN bookings b
ON a.id < b.id
AND a.start_date <= b.end_date
AND a.end_date >= b.start_date;

• Toby Speight: Welcome to Stack Overflow! Thank you for this code snippet, which might provide some limited short-term help. A proper explanation would greatly improve its long-term value by showing why this is a good solution to the problem, and would make it more useful to future readers with other, similar questions. Please edit your answer to add some explanation, including the assumptions you've made.