Need sql query to get two consucutive slots from a table

Question

I have table name Availableslots. it contains 2 columns

1.WorkerId (int)
2.StartDate (DateTime)

StartDate contains availability of all workers.

Now I need a query that select n number of consucutive slots ... like

WorkerId  StartDate
1         31/01/2018 09:00
1         31/01/2018 10:00
2         31/01/2018 09:00
3         31/01/2018 09:00
3         31/01/2018 10:00
4         31/01/2018 09:00

As per this data only worker 1 and 3 has 2 continuous slots available. I am passing these slots as parameter.

I am using MSSQL.

Note:- I am passing time slot as a parameter and that is not fixed. It may vary from 2 to n numbers...

Which dbms are you using? (When it comes do data/time many products are far from ANSI SQL compliant.) — jarlh, Jan 31 '18 at 09:48

F.Lazarescu · Accepted Answer · 2018-01-31T09:54:59.957

3

Is this what you need?

SELECT WorkerID, count(StartDate)
from Availableslots
where
StartDate in (date, date)
group by WorkerID
having count(StartDate) = 2 (or > 2, how you need)

edited Jan 31 '18 at 09:54

answered Jan 31 '18 at 09:46

F.Lazarescu

1,385
2
16
31

Product specific answer to a question with no dbms specified. At least tell us which dbms this is for. – jarlh Jan 31 '18 at 09:50
Not exactly. I am passing time slots as a parameter like in this case I am passing 09:00 and 10:00. This slots not fixed sometimes 2 sometimes 5. I need to check on specific date all times available. Am I clear? – vikas Jan 31 '18 at 09:50
This is for MSSQL @Jarlh – vikas Jan 31 '18 at 09:51
@vikas as you maybe know, you can use this statement in oracle, mysql, mssql .. and maybe more dbms because I'm not using specific functions or others .. – F.Lazarescu Jan 31 '18 at 10:03
1

Thanks @F.Lazarescu. It is quite close and after few changes working fine. Now I am converting this to linq query – vikas Jan 31 '18 at 10:09
@F.Lazarescu any help to convert this to linq ? – vikas Jan 31 '18 at 10:10
@vikas, is [this](https://stackoverflow.com/questions/296972/sql-to-linq-tool) helping you? – F.Lazarescu Jan 31 '18 at 10:48
1

Yes @F.Lazarescu – vikas Jan 31 '18 at 12:22

score 0 · Answer 2 · answered Jan 31 '18 at 10:12

I think first you should define what is continuous slots and what is not continuous slots with suitable example.

 declare @t table(WorkerId int,StartDate datetime)
insert into @t VALUES
(1, '2018/01/31 09:00')
,(1,'2018/01/31 10:00')
,(2,'2018/01/31 09:00')
,(3,'2018/01/31 09:00')
,(3,'2018/01/31 10:00')
,(4,'2018/01/31 09:00')

declare @timeSlot int=2
;

WITH CTE
AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY workerid ORDER BY StartDate
            ) rn
    FROM @t
    )
SELECT *
FROM @t t
WHERE EXISTS (
        SELECT 1
        FROM cte c
        WHERE c.workerid = t.workerid
            AND c.rn >=@timeSlot --( how you need)
        )

Need sql query to get two consucutive slots from a table

2 Answers2