SQL Count different time with a difference of 30 minutes

Question

I am trying to find an SQL query that will count the number of different start periods that differ by at least 30 minutes.

I have several employees who are paid a loan when they start working at least three times a week, and the start time is at least 30 minutes, different from other starting times. For instance:

select count(distinct (CONVERT(VARCHAR(10), starttime, 108))), employeecode
from schedule 
where CONVERT(VARCHAR(10), starttime, 108) >= 
(select min(CONVERT(VARCHAR(10), dateadd (mi, 30, s2.starttime), 108)) from schedule s2)  
group by starttime, employeecode

I hope to get the result with the employee code and the number of different and different initial periods. eg. Employeecode = 9999, different start times = 4 I tried this and I haven't gotten something working yet ...

Can someone suggest where I'm wrong, or a suitable solution that can help me? Thanks in advance for your help :)

Answer 1

[Update: based on clarifying the problem with the poster in the commentary on this answer, the problem I solved with this answer is clearly not the problem the poster is trying to solve. I leave an answer to show a solution to another problem, and not to delete comments that clarify the problem statement]

Divide the problem into two parts: the identification of “unique” begins (within 30 minutes), and then counting them. The first part is the one with which I think you have problems. Here is the approach:

SELECT employeecode, starttime FROM schedule S1
    WHERE NOT EXISTS (SELECT * FROM schedule S2 
        WHERE S2.employeecode = S1.employeecode AND
              S2.starttime > DATEADD(mi, -29, S1.starttime)

A few notes:

• I copied the math logic of the date from your original query, rather than looking at the syntax.

• I assume the start time is DATETIME.

• 29 , , 30 ( ). , , (29 * 60) + 59. , .

• - (, ):

  SELECT useeecode, count() FROM unique_starts_view  WHERE starttime ( ) ( )  GROUP BY employeecode HAVING COUNT() >= 3

• NOT EXISTS , .

Answer 2

, , . ...

( ), , "" , .

, 00: 00-00: 30 = 1 00: 30-01: 00 = 2 ... 07: 00-07: 30 = 15 ... 23: 30-00: 00 = 48

, ( ) case.

, , . , 07:29 07:31 , 2 . , , 15 45 . , , ...

, , SQL, , , ...

, SQL , SQL ... , .

1) , . 2) , 30 , . 3) 2 , . 4) .

SQL , , , , .

Answer 3

, SQL Server OP, . SQL Server 2005 , - :

With StartTimes As
    (
    Select StartDateTime 
        , Row_Number() Over( Order By StartDateTime ) As Seq 
        , DatePart(hh, StartDateTime) * 60 + DatePart(mi, StartDateTime) As Minutes
    From Schedule
    )
Select *
From StartTimes As S1
Where Exists(
            Select 1
            From StartTimes As S2
            Where S1.Seq <> 1
                And Abs(S2.Minutes - S1.Minutes) >= 30
            )

Answer 4

( Time Bandits), :

CREATE TABLE Start_Periods
(
    begin_time    TIME        NOT NULL,
    end_time      TIME        NOT NULL,
    time_period   TINYINT     NOT NULL
    CONSTRAINT PK_Start_Periods PRIMARY KEY CLUSTERED (begin_time),
    CONSTRAINT CK_Start_Periods_begin_before_end CHECK (begin_time < end_time OR end_time = '00:00:00.000')
)
INSERT INTO Start_Periods (begin_time, end_time, time_period)
SELECT '00:00:00.000', '00:15:00.000', 1 UNION ALL
SELECT '00:15:00.000', '00:45:00.000', 2 UNION ALL
SELECT '00:45:00.000', '01:15:00.000', 3 UNION ALL
SELECT '01:15:00.000', '01:45:00.000', 4 UNION ALL
SELECT '01:45:00.000', '02:15:00.000', 5 UNION ALL
SELECT '02:15:00.000', '02:45:00.000', 6 UNION ALL
SELECT '02:45:00.000', '03:15:00.000', 7 UNION ALL
SELECT '03:15:00.000', '03:45:00.000', 8 UNION ALL
--...
SELECT '23:15:00.000', '23:45:00.000', 48 UNION ALL
SELECT '23:45:00.000', '00:00:00.000', 1

:

SELECT
    SCH.employee_code,
    COUNT(DISTINCT SP.time_period) AS different_time_starts
FROM
    Schedule SCH
INNER JOIN Start_Periods SP ON
    SP.begin_time <= SCH.start_time AND
    SP.end_time > SCH.start_time
GROUP BY
    SCH.employee_code

Answer 5

, :

• SQL
• SELF JOIN of schedule,
• Now count all the differences for DISTINCTmore than 30 minutes for each client → gives you the amount of start time

This will give you the desired result.