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