need help with query

• rockmoose

  SSCarpal Tunnel

  Points: 4571

  More actions

  June 13, 2004 at 3:24 pm

  #510192

  Yup Jeff, just wanted to help some, I didn't see your solution at first.

  Probably barsuk just wanted to get the job done .

  And also barsuk - It would help to provide some more information like table layout and such ( datatypes ). Now I just assumed that the times were stored as datetime. And if there are specifics - like the midnight problem, it helps to mention little tidbits of information like that too.

  /rockmoose

  
  You must unlearn what You have learnt

• Bavineni

  SSC Enthusiast

  Points: 137

  More actions

  June 14, 2004 at 1:44 pm

  #510337

  Try this.....

  select t1.Client_Rep ,t1.begin_work,max(t2.end_work) as workend,datediff(hour,max(t2.end_work),t1.begin_work)

   as diff  from tempdata t1

  join tempdata t2 on t1.Client_Rep =t2.Client_Rep 

  where t1.begin_work > t2.end_work

  group by t1.Client_Rep ,t1.begin_work

  having datediff(hour,max(t2.end_work),t1.begin_work) > 1

   

   

   

• barsuk

  SSChampion

  Points: 12280

  More actions

  June 14, 2004 at 2:41 pm

  #510344

  Thanks again, Guys.

   I appreciate your help and comments!

• Peter Larsson

  SSC Enthusiast

  Points: 192

  More actions

  June 16, 2004 at 3:12 am

  #510600

  CREATE TABLE #PossibleWorkMinutes

    (

     Client_Rep VARCHAR(50),

     Begin_Work DATETIME,

     End_Work DATETIME,

     TotalWorkDayMinutes INT

   &nbsp

  INSERT INTO #PossibleWorkMinutes

  SELECT  Client_Rep,

    MIN(Begin_Work),

    MAX(End_Work),

    NULL

  FROM  MyTable

  GROUP BY Client_Rep

  UPDATE  #PossibleWorkMinutes

  SET  TotalWorkDayMinutes = DATEDIFF(mi, Begin_Work, End_Work)

  CREATE TABLE #WorkDoneMinutes

    (

     Client_Rep VARCHAR(50),

     WorkDoneMinutes INT

   &nbsp

  INSERT INTO #WorkDoneMinutes

  SELECT  Client_Rep,

    SUM(DATEDIFF(mi, Begin_Work, End_Work))

  FROM  MyTable

  GROUP BY  Client_Rep

  SELECT  #PossibleWorkMinutes.Client_Rep,

    #PossibleWorkMinutes.TotalWorkDayMinutes,

    #WorkDoneMinutes.WorkDoneMinutes,

    #PossibleWorkMinutes.TotalWorkDayMinutes - #WorkDoneMinutes.WorkDoneMinutes AS LunchMinutes

  FROM  #PossibleWorkMinutes

  INNER JOIN #WorkDoneMinutes ON #PossibleWorkMinutes.Client_Rep = #WorkDoneMinutes.Client_Rep

  WHERE #PossibleWorkMinutes.TotalWorkDayMinutes - #WorkDoneMinutes.WorkDoneMinutes > 60

  ORDER BY #PossibleWorkMinutes.Client_Rep

  DROP TABLE #PossibleWorkMinutes

  DROP TABLE #WorkDoneMinutes

• Jonathan Tomkins

  Ten Centuries

  Points: 1268

  More actions

  June 16, 2004 at 4:08 am

  #510604

  ( didn't notice it the extra pages of posts )

  assuming the begin and end fields are datetimes....

  select Client_Rep, datediff(mi, s, e) minsInWork, d minsWorked, datediff(mi, s, e) - d breakTime

  from

   (

   select  Client_Rep,

    min(Begin_Work)as s,

    max(End_Work) as e,

    sum(datediff(mi, Begin_Work, End_Work)) d

    

    --DateDiff(mi,Begin_Work, End_Work)

   from tblTimes

   group by Client_Rep

  &nbsp t

  where (datediff(mi, s, e) - d) > 30

   

• Dalibor Karnas

  Newbie

  Points: 2

  More actions

  June 16, 2004 at 6:28 am

  #510621

  Hello Tia, I simplify your example with translation time values to a decimal values (time 9.45 is decimal 9.75)

  Run this script

  Declare @Table table (Client_Rep  varchar(50), Begin_Work decimal (5,2), End_Work decimal (5,2))

  Insert into @table (Client_Rep, Begin_Work, End_Work) values ('Vitalio Arini', 9.00, 9.75)

  Insert into @table (Client_Rep, Begin_Work, End_Work) values ('Vitalio Arini', 10.00, 12.00)

  Insert into @table (Client_Rep, Begin_Work, End_Work) values ('Nino Verto', 8.50, 11.00)

  Insert into @table (Client_Rep, Begin_Work, End_Work) values ('Vitalio Arini', 14.50, 17.00)

  Insert into @table (Client_Rep, Begin_Work, End_Work) values ('Nino Verto', 15.00, 17.00)

  Insert into @table (Client_Rep, Begin_Work, End_Work) values ('Ken Coles', 9.00, 12.50)

  Insert into @table (Client_Rep, Begin_Work, End_Work) values ('Ken Coles', 13.00, 17.00)

  select * from @Table

  -- key solution

  select *,

   (select Min (B.Begin_Work)

   from @Table as B

   where B.Client_Rep = A.Client_Rep

   and B.Begin_Work > A.End_Work) as Next_Work

  from @Table as A

  -- complex solution

  select *

  from

  (

   select Client_Rep, Sum (Break_Work) as Break_Time

   from

    (select Client_Rep,

     (select Min (B.Begin_Work)

     from @Table as B

     where B.Client_Rep = A.Client_Rep

     and B.Begin_Work > A.End_Work) - A.End_Work as Break_Work

     from @Table as A

   &nbsp  C

   group by Client_Rep

  ) D

  where Break_Time > 1

  order by Break_Time desc