sqlsql-serverselect

Check dates within same table


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

Solution

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