Summarised View Of Call Out Rota 1

[edsuk]

We have a call-out schedule which details the employee on call for any day by the hour, the table looks something like this

ID EmpID CallDate CallHr
1 55 2006-12-01 1
2 55 2006-12-01 2
3 55 2006-12-01 3
4 55 2006-12-01 4
5 55 2006-12-01 5
6 55 2006-12-01 6
7 55 2006-12-01 7
8 55 2006-12-01 8
9 55 2006-12-01 9
10 55 2006-12-01 10
11 55 2006-12-01 11
12 55 2006-12-01 12
13 55 2006-12-01 13
14 68 2006-12-01 14
15 68 2006-12-01 15
16 68 2006-12-01 16
17 55 2006-12-01 17
18 55 2006-12-01 18
19 70 2006-12-01 19
20 70 2006-12-01 20
21 70 2006-12-01 21
22 70 2006-12-01 22
23 70 2006-12-01 23
24 70 2006-12-01 24
25 70 2006-12-02 1
26 70 2006-12-02 2
27 70 2006-12-02 3
28 70 2006-12-02 4
29 70 2006-12-02 5
30 70 2006-12-02 6

What I am trying to achieve is a summarised view of start/end of call

i.e.

EmpID CallDate St Fin
55 2006-12-01 1 13
68 2006-12-01 14 16
55 2006-12-01 17 18
70 2006-12-01 19 24
70 2006-12-02 1 6


Obviously MIN and MAX aren't suitable because it is possible for the same employee to be on call at different parts of the day.

I achieved this in VB using the following code:

...

Set rst1 = New ADODB.Recordset

sql = "SELECT * FROM CallOutTbl ORDER BY 1"

With rst1
    .Source = sql
    Set .ActiveConnection = GCNN
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open Options:=adCmdText
End With

If rst1.RecordCount = 0 Then
    RotaPhase2 = 1
    rst1.close
    Set rst1 = Nothing
    Exit Function
End If

GCNN.BeginTrans
GCNN.Execute "DELETE FROM [CallOutRotaTbl]"
GCNN.CommitTrans

Set rst = New ADODB.Recordset

With rst
    .Source = "CallOutRotaTbl"
    Set .ActiveConnection = GCNN
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open Options:=adCmdTable
End With

rst1.MoveFirst

GCNN.BeginTrans
z = 1
Do
    
    Sdate = rst1(1)
    Emp = rst1(4)
    sSeg = rst1(3)
    
    rst.AddNew
    rst(1) = Sdate
    rst(2) = rst1(2)
    rst(3) = rst1(3)
    rst(5) = Emp
    rst(6) = UserID
    
    Do
        eSeg = rst1(3)
        rst1.MoveNext
        If rst1.EOF = True Then Exit Do
        sSeg = sSeg + 1
    Loop Until Sdate <> rst1(1) Or Emp <> rst1(4) Or sSeg <> rst1(3)

    rst(4) = eSeg
    rst.Update
Loop Until rst1.EOF = True

GCNN.CommitTrans

I have tried in vain to convert this to a stored procedure - any advice would be very much appreciated.

Thanks Mark

 

[johnv20]

try

select a.EmpID, a.CallDate, min(a.CallHr) AS ST,  max(a.CallHr) as ED
from TestTable a
group by EmpID, CallDate

 

[PWise]

johnv20:
You must have missed this line!

Obviously MIN and MAX aren't suitable because it is possible for the same employee to be on call at different parts of the day.

 

[gmmastros]

I hope you ate your Wheaties this morning, because what you are trying to do is not easy. The solution I present here involves table variables, subqueries, and self joins. At the beginning of the code, I create a table variable to store your sample data. This means you can Copy/Paste the entire contents of the code block so you can see how this works. This query does not use anything from your real table. If you are satisfied that the query works for the sample data, simply change @Temp to your actual table name and run the query.

[green]-- Sample data starts here[/green]
declare @Temp Table(Id Int, EmpId Int, CallDate DateTime, CallHr Int)

Insert Into @Temp Values(1,     55,    '2006-12-01',    1        )
Insert Into @Temp Values(2,     55,    '2006-12-01',    2)
Insert Into @Temp Values(3,     55,    '2006-12-01',    3)
Insert Into @Temp Values(4,     55,    '2006-12-01',    4)
Insert Into @Temp Values(5,     55,    '2006-12-01',    5)
Insert Into @Temp Values(6,     55,    '2006-12-01',    6)
Insert Into @Temp Values(7,     55,    '2006-12-01',    7)
Insert Into @Temp Values(8,     55,    '2006-12-01',    8)
Insert Into @Temp Values(9,     55,    '2006-12-01',    9)
Insert Into @Temp Values(10,    55,    '2006-12-01',    10)
Insert Into @Temp Values(11,    55,    '2006-12-01',    11)
Insert Into @Temp Values(12,    55,    '2006-12-01',    12)
Insert Into @Temp Values(13,    55,    '2006-12-01',    13)
Insert Into @Temp Values(14,    68,    '2006-12-01',    14)
Insert Into @Temp Values(15,    68,    '2006-12-01',    15)
Insert Into @Temp Values(16,    68,    '2006-12-01',    16)
Insert Into @Temp Values(17,    55,    '2006-12-01',    17)
Insert Into @Temp Values(18,    55,    '2006-12-01',    18)
Insert Into @Temp Values(19,    70,    '2006-12-01',    19)
Insert Into @Temp Values(20,    70,    '2006-12-01',    20)
Insert Into @Temp Values(21,    70,    '2006-12-01',    21)
Insert Into @Temp Values(22,    70,    '2006-12-01',    22)
Insert Into @Temp Values(23,    70,    '2006-12-01',    23)
Insert Into @Temp Values(24,    70,    '2006-12-01',    24)
Insert Into @Temp Values(25,    70,    '2006-12-02',    1)
Insert Into @Temp Values(26,    70,    '2006-12-02',    2)
Insert Into @Temp Values(27,    70,    '2006-12-02',    3)
Insert Into @Temp Values(28,    70,    '2006-12-02',    4)
Insert Into @Temp Values(29,    70,    '2006-12-02',    5)
Insert Into @Temp Values(30,    70,    '2006-12-02',    6)

[green]-- Actual query starts here[/green]

Declare @Data Table(RowId Integer Identity(1,1), EmpId Int, CallDate DateTime, Callhr Int)

Insert Into @Data(EmpId, CallDate, CallHr)
Select EmpId, CallDate, CallHr
From   @Temp
Order By EmpId, CallDate, CallHr

Declare @Output Table(RowId Integer Identity(1,1), EndRowId Int, EmpId Int, CallDate DateTime, CallHr Int)

Insert  Into 
@Output (EndRowId, EmpId, CallDate, CallHr)
Select  A.RowId, A.EmpId, A.CallDate, A.CallHr
From    @Data A
        Left Join @Data B
          On A.RowId = B.RowId - 1
          And A.EmpId = B.EmpId
          And A.CallDate = B.CallDate
          And A.CallHr = B.CallHr - 1
Where   B.RowId Is NULL

Select  A.EmpId, 
        A.CallDate, 
        A.CallHr As StartHr, 
        B.CallHr As FinishHr
From    (Select IsNull(B.EndRowId + 1, 1) As StartRowId,     A.EndRowId 
         From   @Output A
                Left Join @Output B
                  On A.RowId = B.RowId + 1) As Range
        Inner Join @Data A
          On Range.StartRowId = A.RowId
        Inner Join @Data B
          On Range.EndRowId = B.RowId
Order By A.callDate, StartHr

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[edsuk]

George

Many thanks for taking the time to solve this complex issue, Your solution works great - now just trying to get to grips with understanding the code itself !

As mentioned I managed this using recordsets by moving through each record and comparing to previous to see if still same person/day - therefore I thought the only solution would involve cursors - but your code is awesome.

Star thoroughly deserved.

Thanks

Mark...

 

[gmmastros]

Mark,

Thank you for the star.

If you indicate the part (or parts) of the code that you don't understand, I will attempt to explain it for you.

I should point out that you may be able to improve the performance of this code by making a couple of subtle changes. Most people don't realize that you can create clustered indexes on a table variable. If your table is large, you would certainly benefit from using an index. Here's how...

Change:
Declare @Data Table(RowId Integer Identity(1,1), EmpId Int, CallDate DateTime, Callhr Int)

To:
Declare @Data Table(RowId Integer Identity(1,1) [!]Primary Key Clustered[/!], EmpId Int, CallDate DateTime, Callhr Int)

And Change:
Declare @Output Table(RowId Integer Identity(1,1), EndRowId Int, EmpId Int, CallDate DateTime, CallHr Int)

To:
Declare @Output Table(RowId Integer Identity(1,1) [!]Primary Key Clustered[/!], EndRowId Int, EmpId Int, CallDate DateTime, CallHr Int)



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[edsuk]

George

Thanks for the kind offer.

I actually went through the SQL in detail and eventually grasped the logic of the self join - as mentioned the data I published was an example so the code had to be modified slightly to suit the actual table structure - and it works great - the tables are not gigantic and performance is not an issue, but thanks anyway for the additional info.

I must admit what interests me more is how you actually came up with the solution - understanding the code is one thing - but the thought process that goes into developing the solution is another - I don't think I would have been able to come up with this in a month of sundays! (I must start thinking outside of the box!)

Once again thanks - it really was appreciated.

Mark...