DFW Mustang Forums banner

1 - 1 of 1 Posts

·
Timmay
Joined
·
4,898 Posts
Discussion Starter #1
i thought this was pretty cool

Declare @Date smalldatetime
Declare @AgentNo char(6)
Declare @I int
Declare @ColCount int
Declare @ColOne smalldatetime
Declare @ColTwo smalldatetime
Declare @ColThree smalldatetime
Declare @ColFour smalldatetime
Declare @ColOneApptId int
Declare @ColTwoApptId int
Declare @ColThreeApptId int
Declare @ColFourApptId int
Declare @ColOneApptIDTemp int
Declare @ColTwoApptIDTemp int
Declare @ColThreeApptIDTemp int
Declare @ColFourApptIDTemp int
Declare @TempApptId int
Declare @TempCol smalldatetime

Declare AgentCursor cursor
for select distinct [date], agentno from appointments order by agentno
open AgentCursor

fetch from AgentCursor
into @Date, @AgentNo

set @i = 1

WHILE @@FETCH_STATUS = 0
BEGIN
Select @ColOne = [time], @ColOneApptID = [AppointmentId] from Appointments where AgentNo = @AgentNo and [Date] = @Date and Col = 1
Select @ColTwo = [time], @ColTwoApptID = [AppointmentId] from Appointments where AgentNo = @AgentNo and [Date] = @Date and Col = 2
Select @ColThree = [time], @ColThreeApptID = [AppointmentId] from Appointments where AgentNo = @AgentNo and [Date] = @Date and Col = 3
Select @ColFour = [time], @ColFourApptID = [AppointmentId] from Appointments where AgentNo = @AgentNo and [Date] = @Date and Col = 4
Set @ColOneApptIDTemp = @ColOneApptID
Set @ColTwoApptIDTemp = @ColTwoApptID
Set @ColThreeApptIDTemp = @ColThreeApptID
Set @ColFourApptIDTemp = @ColFourApptID
While @i <= 4
BEGIN
If Convert(varchar(15), @ColFour, 114) < Convert(varchar(15), @ColThree, 114)
OR @ColThree is Null and @ColFour is not null
Begin
Set @TempApptID = @ColFourApptID
Set @TempCol = @ColFour
Set @ColFourApptID = @ColThreeApptID
Set @ColFour = @ColThree
Set @ColThreeApptID = @TempApptID
Set @ColThree = @TempCol
Set @TempApptID = Null
Set @TempCol = Null
End

If Convert(varchar(15), @ColThree, 114) < Convert(varchar(15), @ColTwo, 114)
OR @ColTwo is Null and @ColThree is not null
Begin
Set @TempApptID = @ColThreeApptID
Set @TempCol = @ColThree
Set @ColThreeApptID = @ColTwoApptID
Set @ColThree = @ColTwo
Set @ColTwoApptID = @TempApptID
Set @ColTwo = @TempCol
Set @TempApptID = Null
Set @TempCol = Null
End

If Convert(varchar(15), @ColTwo, 114) < Convert(varchar(15), @ColOne, 114)
OR @ColOne is Null and @ColTwo is not null
Begin
Set @TempApptID = @ColTwoApptID
Set @TempCol = @ColTwo
Set @ColTwoApptID = @ColOneApptID
Set @ColTwo = @ColOne
Set @ColOneApptID = @TempApptID
Set @ColOne = @TempCol
Set @TempApptID = Null
Set @TempCol = Null
End
set @i = @i + 1
END

If @ColOneApptIDTemp <> @ColOneApptID or @ColOneApptIDTemp is Null and @ColOneApptID is not null
BEGIN
Update Appointments set Col = 1 where AppointmentID = @ColOneApptID
END

If @ColTwoApptIDTemp <> @ColTwoApptID or @ColTwoApptIDTemp is Null and @ColTwoApptID is not null
BEGIN
Update Appointments set Col = 2 where AppointmentID = @ColTwoApptID
END

If @ColThreeApptIDTemp <> @ColThreeApptID or @ColThreeApptIDTemp is Null and @ColThreeApptID is not null
BEGIN
Update Appointments set Col = 3 where AppointmentID = @ColThreeApptID
END

If @ColFourApptIDTemp <> @ColFourApptID or @ColFourApptIDTemp is Null and @ColFourApptID is not null
BEGIN
Update Appointments set Col = 4 where AppointmentID = @ColFourApptID
END

Set @ColOne = Null
Set @ColTwo = Null
Set @ColThree = Null
Set @ColFour = Null
Set @ColOneApptID = Null
Set @ColTwoApptID = Null
Set @ColThreeApptID = Null
Set @ColFourApptID = Null
Set @i = 0
fetch next from AgentCursor
into @Date, @AgentNo
END

close AgentCursor
DEALLOCATE AgentCursor
 
1 - 1 of 1 Posts
Top