How to use a While Loop in where clause

Question

I have a table like the following with entries in the EntryTime column up to today.

Table1

PersonID|StartTime|EndTime|EntryTime
1       |10:00    |12:00  |2014-01-01
1       |10:00    |12:00  |2014-01-02
1       |10:00    |12:00  |2015-01-03
...

and a Contract Table:

Table2

PersonID|ContractID| ContractStart| ContractType|Hours
1       |1         |2014-01-01    |Student      |8
1       |2         |2015-01-01    |Trainee      |40
1       |3         |2015-03-01    |Student      |20
1       |4         |2015-05-01    |Student      |12

Now I want to sum the time (datediff(minute, StartTime, Endtime)) for the current contractType using the result table mentioned below. The contracttype must be contiguous starting with the last one. So in this example all entries for the contract 3+4 are relevant.

My idea was to use a loop to select the relevant contracts.

WHILE (Select ContractType from Table2) = 'Student'
Begin
Select ContractStart from Table2
Order By ContractStart
End

Unfortunatley the loop is not working yet and if I do not know how to implement it into the where clause.

The result table I am trying to get is a subset of Table1 with all entries starting at the 2015-03-01

Answer 1

Hard to know what do you want without an example but it's a try. Also make a lot of guesses about your types.

Note: Avoid while loops and cursors, stick with set logic =)

declare @Table1 as table
(
    PersonID int
    ,StartTime datetime
    ,EndTime datetime
    ,EntryTime date
)

insert into @Table1
values
 (1       ,'10:00', '12:00', '2014-01-01')
,(1       ,'10:00', '12:00', '2014-01-02')
,(1       ,'10:00', '12:00', '2015-01-03')
,(2       ,'10:00', '12:00', '2015-01-03')
,(2       ,'10:00', '12:00', '2015-01-04')
,(3       ,'10:00', '12:00', '2015-01-03')
,(3       ,'10:00', '12:00', '2015-01-05')


declare @Table2 as table
(
    PersonID int
    ,ContractID int
    ,ContractStart date
    ,ContractType varchar(10)
    ,[Hours] float
)
insert into @Table2
values
 (1       ,1         ,'2014-01-01',    'Student',      8)
,(1       ,2         ,'2015-01-01',    'Trainee',      40)
,(1       ,3         ,'2015-03-01',    'Student',      20)
,(1       ,4         ,'2015-05-01',    'Student',      12)
,(2       ,1         ,'2014-01-01',    'Student',      8)
,(2       ,2         ,'2015-01-01',    'Trainee',      40)
,(2       ,3         ,'2015-03-01',    'Student',      20)
,(2       ,4         ,'2015-05-01',    'Student',      12)
,(3       ,1         ,'2014-01-01',    'Student',      8)
,(3       ,2         ,'2015-01-01',    'Trainee',      40)
,(3       ,3         ,'2015-03-01',    'Student',      20)
,(3       ,4         ,'2015-05-01',    'Student',      12)

--Current contrat (for PersonId = 1)
declare @CurrentType varchar(10) =
(select top 1 ContractType from @Table2 where PersonID = 1 order by ContractStart desc)

--Last different type start (for PersonId = 1)
declare @LastDiff date = 
(select top 1 ContractStart from @Table2 where PersonID = 1 and @CurrentType != ContractType order by ContractStart desc)

--Current type span (for PersonId = 1)
select datediff(minute, min(StartTime + cast(EntryTime as datetime)), max(EndTime + cast(EntryTime as datetime))) as [PesonId = 1 TimeSpan]
from @Table1
where PersonID = 1
and EntryTime > @LastDiff


-- Puting everything togheter
select t1.PersonId, datediff(minute, min(t1.StartTime + cast(t1.EntryTime as datetime)), max(t1.EndTime + cast(t1.EntryTime as datetime))) as TimeSpan
from @Table1 t1
where t1.EntryTime >
 (
    select top 1 t2.ContractStart
    from @Table2 t2
    where t2.PersonID = t1.PersonID
    and t2.ContractType != 
    (
       select top 1 t2b.ContractType from @Table2 t2b where t2b.PersonID = t2.PersonID order by t2b.ContractStart desc
    )
    order by t2.ContractStart desc
 )
group by t1.PersonId

The snippet is self explanatory and gives me:

PersonId TimeSpan
-------- --------
       1    120
       2    1560
       3    3000

Note I added a few data in order to make a better test example

Answer 2

try this:

Select Table1.PersonID, ContractID, datediff(minute, StartTime, Endtime) diff
    from Table2 INNER JOIN Table1 on Table2.PersonID = Table1.PersonID
    WHERE ContractType LIKE 'Student'
    GROUP BY Table1.PersonID, ContractID