sorting in sql - DFWstangs Forums
 
LinkBack Thread Tools Display Modes
post #1 of 1 (permalink) Old 01-28-2004, 11:18 AM Thread Starter
Timmay
 
Tiny Tim's Avatar
 
Join Date: Aug 2001
Posts: 4,898
sorting in sql

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
Tiny Tim is offline  
Sponsored Links
Advertisement
 
Reply

Bookmarks

Quick Reply
Message:
Options

Register Now



In order to be able to post messages on the DFWstangs Forums forums, you must first register.
Please enter your desired user name, your email address and other required details in the form below.

User Name:
Password
Please enter a password for your user account. Note that passwords are case-sensitive.

Password:


Confirm Password:
Email Address
Please enter a valid email address for yourself.

Email Address:
OR

Log-in










Thread Tools
Show Printable Version Show Printable Version
Email this Page Email this Page
Display Modes
Linear Mode Linear Mode



Posting Rules  
You may post new threads
You may post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On
Trackbacks are On
Pingbacks are On
Refbacks are On

 
For the best viewing experience please update your browser to Google Chrome