Cursor help PLEASE!

[P.D.N. Tame]

Yes, I know that cursors are gauche, but I can't see a solution using
queries and I'm pretty adept at them. This will be running once a day
in the wee small hours when minimal server activity will be taking
place, it will be handling 700-1000ish records. The box is a P4/SQL
2000 with lots of ram and multiple CPUs.

The code for creating and populating the table in question follows the
cursor code.

The application is for medical transport billing. We take people to
the doctor and home again and the health plan pays us. A one-way
trip, home -> doctor, is a single ‘line item'. A round trip, home ->
doctor -> home is rolled up, the charge summed, and billed as a single
line item. A three-leg, home -> doctor -> pharmacy -> home, is billed
as three line items.

This code is just for identifying the data, once this works correctly
I'll add the code in for doing the rollup and I'm confident I can
handle that.

If I ruled the world, every leg of every trip would be a single line
item and I wouldn't have to deal with this rollup BS, but I don't make
the policy, I just have to code it. <g>

A trip is a round trip if the date, account number, and passenger name
is the same and the origin street of record X equals the destination
street of record X+1. The problem is that X+1 could be the start of a
round trip and X+2 could be the completing leg of a round trip. So
you could have a scenario of hospital -> home -> doctor -> home in
which X is one-way and X+1 & X+2 form a round trip.

BLERG! Any help is greatly appreciated, it has me stumped.
/*
select trip_id, acct_number, passenger, street, dest_street,
flat_rate, screated, smeteroff, remark1, remark2
from zvoucherstodbf order by cast(screated as smalldatetime),
acct_number, passenger, smeteroff
*/
declare @roundtrip integer

declare @cmsg char(200)
declare @Trip_ID char(11)
declare @acct_number char(11)
declare @passenger char(12)
declare @created char(22)
declare @meter_off char(22)
declare @street char(32)
declare @dest_street char(32)
declare @remark1 char(32)
declare @remark2 char(32)
declare @flat_rate smallint

declare @prev_cmsg char(200)
declare @prev_Trip_ID char(11)
declare @prev_acct_number char(11)
declare @prev_passenger char(12)
declare @prev_created char(22)
declare @prev_meter_off char(22)
declare @prev_street char(32)
declare @prev_dest_street char(32)
declare @prev_remark1 char(32)
declare @prev_remark2 char(32)
declare @prev_flat_rate smallint

declare cVoucher cursor scroll for
select trip_id, acct_number, passenger, street, dest_street, screated,
smeteroff, remark1, remark2, flat_rate
from zVouchersToDBF
--where screated = '12/03/03'
order by screated, acct_number, passenger, smeteroff

open cVoucher

--Load the @Prev_ (previous record) variables with the first record
fetch from cVoucher into @prev_trip_id, @prev_acct_number,
@prev_passenger,
@prev_street, @prev_dest_street, @prev_created, @prev_meter_off,
@prev_remark1, @prev_remark2, @prev_flat_rate

--Initialize current variables
select @trip_id = @prev_trip_id
select @acct_number = @prev_acct_number
select @passenger = @prev_passenger
select @street = @prev_street
select @dest_street = @prev_dest_street
select @created = @prev_created
select @meter_off = @prev_meter_off
select @remark1 = @prev_remark1
select @remark2 = @prev_remark2

while @@fetch_status = 0 --not EOF
begin
select @roundtrip = 0

if (@prev_created = @created) and (@prev_acct_number = @acct_number)
and (@prev_passenger = @passenger) and (@prev_street =
@dest_street) -- MATCH! Apparent round trip
begin
select @roundtrip = 1

select @prev_trip_id = @trip_id
select @prev_acct_number = @acct_number
select @prev_passenger = @passenger
select @prev_street = @street
select @prev_dest_street = @dest_street
select @prev_created = @created
select @prev_meter_off = @meter_off
select @prev_remark1 = @remark1
select @prev_remark2 = @remark2
end

if (@roundtrip = 0)
begin
fetch next from cVoucher into @trip_id, @acct_number, @passenger,
@street, @dest_street, @created, @meter_off, @remark1, @remark2,
@flat_rate

if (@prev_created = @created) and (@prev_acct_number = @acct_number)
and (@prev_passenger = @passenger) and (@prev_street =
@dest_street) -- MATCH! Apparent round trip
select @roundtrip = 1
else
--definitely one-way trip
select @roundtrip = 0
fetch prior from cVoucher into @trip_id, @acct_number, @passenger,
@street, @dest_street, @created, @meter_off, @remark1, @remark2,
@flat_rate
end
if (@roundtrip = 1)
begin
select @cMsg = 'Start: ' + rtrim(@prev_created) + ' ' +
rtrim(@prev_trip_id) + ': ' + @prev_acct_number + ' ' +
rtrim(@prev_meter_off) + ' ' + @prev_passenger + ' ' +
rtrim(@prev_street) + ' to ' + @prev_dest_street + ' ' + @prev_remark1
+ ' ' + @prev_remark2
print @cMsg
select @cMsg = 'End: ' + rtrim(@created) + ' ' + rtrim(@trip_id) +
': ' + @acct_number + ' ' + rtrim(@meter_off) + ' ' + @passenger + ' '
+ rtrim(@street) + ' to ' + @dest_street + ' ' + @remark1 + ' ' +
@remark2
print @cMsg
print ''
end
else
begin
select @cMsg = 'One-Way: ' + rtrim(@created) + ' ' + rtrim(@trip_id)
+ ': ' + @acct_number + ' ' + rtrim(@meter_off) + ' ' + @passenger + '
' + rtrim(@street) + ' to ' + @dest_street + ' ' + @remark1 + ' ' +
@remark2
print @cMsg
print ''
end

select @prev_trip_id = @trip_id
select @prev_acct_number = @acct_number
select @prev_passenger = @passenger
select @prev_street = @street
select @prev_dest_street = @dest_street
select @prev_created = @created
select @prev_meter_off = @meter_off
select @prev_remark1 = @remark1
select @prev_remark2 = @remark2

fetch next from cVoucher into @trip_id, @acct_number, @passenger,
@street, @dest_street, @created, @meter_off, @remark1, @remark2,
@flat_rate
end

close cVoucher

--/*
--This code works for displaying the data set as a whole
-- so you can visually identify what constitutes a round trip.
open cVoucher

fetch from cVoucher into @trip_id, @acct_number, @passenger,
@street, @dest_street, @created, @meter_off, @remark1, @remark2,
@flat_rate

while @@fetch_status = 0
begin
select @cMsg = rtrim(@created) + ' ' + rtrim(@trip_id) + ': ' +
@acct_number + ' ' + @passenger + ' ' + @street + ' ' + @dest_street +
' ' + @meter_off + ' ' + @remark1 + ' ' + @remark2
print @cMsg
fetch next from cVoucher into @trip_id, @acct_number, @passenger,
@street, @dest_street, @created, @meter_off, @remark1, @remark2,
@flat_rate
end

close cVoucher
--*/
deallocate cVoucher
CREATE TABLE [zVouchersToDBF] (
[house] [char] (5) NULL ,
[street] [char] (32) NULL ,
[district] [char] (8) NULL ,
[passenger] [char] (12) NULL ,
[remark1] [char] (32) NULL ,
[remark2] [char] (32) NULL ,
[dest_house] [char] (5) NULL ,
[dest_street] [char] (32) NULL ,
[dest_district] [char] (13) NULL ,
[acct_number] [char] (11) NULL ,
[sub_acct_number] [char] (15) NULL ,
[flat_rate] [money] NULL ,
[car] [char] (3) NULL ,
[driver_id] [char] (9) NULL ,
[meter_on] [char] (5) NULL ,
[meter_off] [char] (5) NULL ,
[fare] [char] (9) NULL ,
[cancelled] [char] (21) NULL ,
[no_trip] [char] (7) NULL ,
[no_trip_reason] [int] NULL ,
[auth_number] [char] (32) NULL ,
[auth_name] [char] (32) NULL ,
[trip_id] [char] (7) NULL ,
[created] [char] (8) NULL ,
[patient_birthday] [char] (16) NULL ,
[waittime] [char] (3) NULL ,
[sNoTrip] [char] (22) NULL ,
[sMeterOn] [char] (22) NULL ,
[sMeterOff] [char] (22) NULL ,
[sCancelled] [char] (22) NULL ,
[sCreated] [char] (22) NULL ,
[sPatientBirthday] [char] (22) NULL ,
[SystemRate] [money] NULL
) ON [PRIMARY]
GO

Insert zvoucherstodbf
Values (null,'100 E 1st',null,'A','X','Y',null,'200 W
2nd',null,'1000',null,5.00,null,null,null,'13:20', '5',null,null,null,null,null,'1',null,null,null,nu ll,null,'13:20',null,'12/8/2003',null,null)

Insert zvoucherstodbf
Values (null,'200 W 2nd',null,'A','X','Y',null,'100 E
1st',null,'1000',null,5.00,null,null,null,'14:20', '5',null,null,null,null,null,'2',null,null,null,nu ll,null,'14:20',null,'12/8/2003',null,null)

Insert zvoucherstodbf
Values (null,'101 E 101',null,'B','X','Y',null,'202 W
202',null,'2000',null,10.00,null,null,null,'13:50' ,'10',null,null,null,null,null,'3',null,null,null, null,null,'13:50',null,'12/8/2003',null,null)

Insert zvoucherstodbf
Values (null,'123 N 456',null,'C','X','Y',null,'234 S
321',null,'2000',null,5.00,null,null,null,'13:51', '5',null,null,null,null,null,'4',null,null,null,nu ll,null,'13:51',null,'12/8/2003',null,null)

Insert zvoucherstodbf
Values (null,'999 N 666',null,'D','X','Y',null,'666 S
999',null,'1000',null,7.50,null,null,null,'14:00', '7.5',null,null,null,null,null,'5',null,null,null, null,null,'14:00',null,'12/8/2003',null,null)

Insert zvoucherstodbf
Values (null,'666 S 999',null,'D','X','Y',null,'999 N
666',null,'1000',null,8.00,null,null,null,'14:30', '8',null,null,null,null,null,'6',null,null,null,nu ll,null,'14:30',null,'12/8/2003',null,null)

Insert zvoucherstodbf
Values (null,'123 n 456',null,'E','X','Y',null,'456 s
789',null,'3000',null,5.00,null,null,null,'14:30', '5',null,null,null,null,null,'7',null,null,null,nu ll,null,'14:30',null,'12/8/2003',null,null)

[David Branch]

You need to re-design to db
Tables
Account <-- store user info
Trip <-- Stores user info and date
TripDetail <-- stores trip, where , from
Triptype ,-subtable
"P.D.N. Tame" <ww***@hotmail.com> wrote in message
news:bc**************************@posting.google.c om...

Yes, I know that cursors are gauche, but I can't see a solution using
queries and I'm pretty adept at them. This will be running once a day
in the wee small hours when minimal server activity will be taking
place, it will be handling 700-1000ish records. The box is a P4/SQL
2000 with lots of ram and multiple CPUs.

The code for creating and populating the table in question follows the
cursor code.

The application is for medical transport billing. We take people to
the doctor and home again and the health plan pays us. A one-way
trip, home -> doctor, is a single 'line item'. A round trip, home ->
doctor -> home is rolled up, the charge summed, and billed as a single
line item. A three-leg, home -> doctor -> pharmacy -> home, is billed
as three line items.

This code is just for identifying the data, once this works correctly
I'll add the code in for doing the rollup and I'm confident I can
handle that.

If I ruled the world, every leg of every trip would be a single line
item and I wouldn't have to deal with this rollup BS, but I don't make
the policy, I just have to code it. <g>

A trip is a round trip if the date, account number, and passenger name
is the same and the origin street of record X equals the destination
street of record X+1. The problem is that X+1 could be the start of a
round trip and X+2 could be the completing leg of a round trip. So
you could have a scenario of hospital -> home -> doctor -> home in
which X is one-way and X+1 & X+2 form a round trip.

BLERG! Any help is greatly appreciated, it has me stumped.
/*
select trip_id, acct_number, passenger, street, dest_street,
flat_rate, screated, smeteroff, remark1, remark2
from zvoucherstodbf order by cast(screated as smalldatetime),
acct_number, passenger, smeteroff
*/
declare @roundtrip integer

declare @cmsg char(200)
declare @Trip_ID char(11)
declare @acct_number char(11)
declare @passenger char(12)
declare @created char(22)
declare @meter_off char(22)
declare @street char(32)
declare @dest_street char(32)
declare @remark1 char(32)
declare @remark2 char(32)
declare @flat_rate smallint

declare @prev_cmsg char(200)
declare @prev_Trip_ID char(11)
declare @prev_acct_number char(11)
declare @prev_passenger char(12)
declare @prev_created char(22)
declare @prev_meter_off char(22)
declare @prev_street char(32)
declare @prev_dest_street char(32)
declare @prev_remark1 char(32)
declare @prev_remark2 char(32)
declare @prev_flat_rate smallint

declare cVoucher cursor scroll for
select trip_id, acct_number, passenger, street, dest_street, screated,
smeteroff, remark1, remark2, flat_rate
from zVouchersToDBF
--where screated = '12/03/03'
order by screated, acct_number, passenger, smeteroff

open cVoucher

--Load the @Prev_ (previous record) variables with the first record
fetch from cVoucher into @prev_trip_id, @prev_acct_number,
@prev_passenger,
@prev_street, @prev_dest_street, @prev_created, @prev_meter_off,
@prev_remark1, @prev_remark2, @prev_flat_rate

--Initialize current variables
select @trip_id = @prev_trip_id
select @acct_number = @prev_acct_number
select @passenger = @prev_passenger
select @street = @prev_street
select @dest_street = @prev_dest_street
select @created = @prev_created
select @meter_off = @prev_meter_off
select @remark1 = @prev_remark1
select @remark2 = @prev_remark2

while @@fetch_status = 0 --not EOF
begin
select @roundtrip = 0

if (@prev_created = @created) and (@prev_acct_number = @acct_number)
and (@prev_passenger = @passenger) and (@prev_street =
@dest_street) -- MATCH! Apparent round trip
begin
select @roundtrip = 1

select @prev_trip_id = @trip_id
select @prev_acct_number = @acct_number
select @prev_passenger = @passenger
select @prev_street = @street
select @prev_dest_street = @dest_street
select @prev_created = @created
select @prev_meter_off = @meter_off
select @prev_remark1 = @remark1
select @prev_remark2 = @remark2
end

if (@roundtrip = 0)
begin
fetch next from cVoucher into @trip_id, @acct_number, @passenger,
@street, @dest_street, @created, @meter_off, @remark1, @remark2,
@flat_rate

if (@prev_created = @created) and (@prev_acct_number = @acct_number)
and (@prev_passenger = @passenger) and (@prev_street =
@dest_street) -- MATCH! Apparent round trip
select @roundtrip = 1
else
--definitely one-way trip
select @roundtrip = 0
fetch prior from cVoucher into @trip_id, @acct_number, @passenger,
@street, @dest_street, @created, @meter_off, @remark1, @remark2,
@flat_rate
end
if (@roundtrip = 1)
begin
select @cMsg = 'Start: ' + rtrim(@prev_created) + ' ' +
rtrim(@prev_trip_id) + ': ' + @prev_acct_number + ' ' +
rtrim(@prev_meter_off) + ' ' + @prev_passenger + ' ' +
rtrim(@prev_street) + ' to ' + @prev_dest_street + ' ' + @prev_remark1
+ ' ' + @prev_remark2
print @cMsg
select @cMsg = 'End: ' + rtrim(@created) + ' ' + rtrim(@trip_id) +
': ' + @acct_number + ' ' + rtrim(@meter_off) + ' ' + @passenger + ' '
+ rtrim(@street) + ' to ' + @dest_street + ' ' + @remark1 + ' ' +
@remark2
print @cMsg
print ''
end
else
begin
select @cMsg = 'One-Way: ' + rtrim(@created) + ' ' + rtrim(@trip_id)
+ ': ' + @acct_number + ' ' + rtrim(@meter_off) + ' ' + @passenger + '
' + rtrim(@street) + ' to ' + @dest_street + ' ' + @remark1 + ' ' +
@remark2
print @cMsg
print ''
end

select @prev_trip_id = @trip_id
select @prev_acct_number = @acct_number
select @prev_passenger = @passenger
select @prev_street = @street
select @prev_dest_street = @dest_street
select @prev_created = @created
select @prev_meter_off = @meter_off
select @prev_remark1 = @remark1
select @prev_remark2 = @remark2

fetch next from cVoucher into @trip_id, @acct_number, @passenger,
@street, @dest_street, @created, @meter_off, @remark1, @remark2,
@flat_rate
end

close cVoucher

--/*
--This code works for displaying the data set as a whole
-- so you can visually identify what constitutes a round trip.
open cVoucher

fetch from cVoucher into @trip_id, @acct_number, @passenger,
@street, @dest_street, @created, @meter_off, @remark1, @remark2,
@flat_rate

while @@fetch_status = 0
begin
select @cMsg = rtrim(@created) + ' ' + rtrim(@trip_id) + ': ' +
@acct_number + ' ' + @passenger + ' ' + @street + ' ' + @dest_street +
' ' + @meter_off + ' ' + @remark1 + ' ' + @remark2
print @cMsg
fetch next from cVoucher into @trip_id, @acct_number, @passenger,
@street, @dest_street, @created, @meter_off, @remark1, @remark2,
@flat_rate
end

close cVoucher
--*/
deallocate cVoucher
CREATE TABLE [zVouchersToDBF] (
[house] [char] (5) NULL ,
[street] [char] (32) NULL ,
[district] [char] (8) NULL ,
[passenger] [char] (12) NULL ,
[remark1] [char] (32) NULL ,
[remark2] [char] (32) NULL ,
[dest_house] [char] (5) NULL ,
[dest_street] [char] (32) NULL ,
[dest_district] [char] (13) NULL ,
[acct_number] [char] (11) NULL ,
[sub_acct_number] [char] (15) NULL ,
[flat_rate] [money] NULL ,
[car] [char] (3) NULL ,
[driver_id] [char] (9) NULL ,
[meter_on] [char] (5) NULL ,
[meter_off] [char] (5) NULL ,
[fare] [char] (9) NULL ,
[cancelled] [char] (21) NULL ,
[no_trip] [char] (7) NULL ,
[no_trip_reason] [int] NULL ,
[auth_number] [char] (32) NULL ,
[auth_name] [char] (32) NULL ,
[trip_id] [char] (7) NULL ,
[created] [char] (8) NULL ,
[patient_birthday] [char] (16) NULL ,
[waittime] [char] (3) NULL ,
[sNoTrip] [char] (22) NULL ,
[sMeterOn] [char] (22) NULL ,
[sMeterOff] [char] (22) NULL ,
[sCancelled] [char] (22) NULL ,
[sCreated] [char] (22) NULL ,
[sPatientBirthday] [char] (22) NULL ,
[SystemRate] [money] NULL
) ON [PRIMARY]
GO

Insert zvoucherstodbf
Values (null,'100 E 1st',null,'A','X','Y',null,'200 W
2nd',null,'1000',null,5.00,null,null,null,'13:20', '5',null,null,null,null,nu
ll,'1',null,null,null,null,null,'13:20',null,'12/8/2003',null,null)
Insert zvoucherstodbf
Values (null,'200 W 2nd',null,'A','X','Y',null,'100 E
1st',null,'1000',null,5.00,null,null,null,'14:20', '5',null,null,null,null,nu
ll,'2',null,null,null,null,null,'14:20',null,'12/8/2003',null,null)
Insert zvoucherstodbf
Values (null,'101 E 101',null,'B','X','Y',null,'202 W
202',null,'2000',null,10.00,null,null,null,'13:50' ,'10',null,null,null,null,
null,'3',null,null,null,null,null,'13:50',null,'12/8/2003',null,null)
Insert zvoucherstodbf
Values (null,'123 N 456',null,'C','X','Y',null,'234 S
321',null,'2000',null,5.00,null,null,null,'13:51', '5',null,null,null,null,nu
ll,'4',null,null,null,null,null,'13:51',null,'12/8/2003',null,null)
Insert zvoucherstodbf
Values (null,'999 N 666',null,'D','X','Y',null,'666 S
999',null,'1000',null,7.50,null,null,null,'14:00', '7.5',null,null,null,null,
null,'5',null,null,null,null,null,'14:00',null,'12/8/2003',null,null)
Insert zvoucherstodbf
Values (null,'666 S 999',null,'D','X','Y',null,'999 N
666',null,'1000',null,8.00,null,null,null,'14:30', '8',null,null,null,null,nu
ll,'6',null,null,null,null,null,'14:30',null,'12/8/2003',null,null)
Insert zvoucherstodbf
Values (null,'123 n 456',null,'E','X','Y',null,'456 s

789',null,'3000',null,5.00,null,null,null,'14:30', '5',null,null,null,null,nu
ll,'7',null,null,null,null,null,'14:30',null,'12/8/2003',null,null)

[louis nguyen]

> The application is for medical transport billing. We take people to

the doctor and home again and the health plan pays us. A one-way
trip, home -> doctor, is a single ?line item'. A round trip, home ->
doctor -> home is rolled up, the charge summed, and billed as a single
line item. A three-leg, home -> doctor -> pharmacy -> home, is billed
as three line items.

Hi,

I would do something like this. Use the identity function to create a
col i with 1,2,3.... Then use a join to link up leg X with leg X+1.
Unfortunately I don't have time to test. Hope this gives you an idea.
-- Louis

For brevity, I'm going to simplify things.

/*fill the i column with 1,2,3,... - you can also use Update .. set
@i=i=@i+1
and default each leg as a single leg*/
select origin=street, dest=dest_street, date, acct, pax,
i=identity(int,1,1), tripType='single leg'
into #T
from myTable

/*mark the first leg of a roundtrip*/
update #T
set tripType='roundtrip1stLeg'
from #T as a
join #T as b
on a.i=b.i+1 and a.origin=b.dest and a.date=b.date and a.acct=b.acct
and a.pax=b.pax

/*mark the second leg of a roundtrip*/
update #T
set tripType='roundtrip2dLeg'
from #T as a
join #T as b
on a.i=b.i-1 and a.dest=b.origin and a.date=b.date and a.acct=b.acct
and a.pax=b.pax

[P.D.N. Tame]

"David Branch" <MO**********@HOTMAIL.COM> wrote in message news:<3m******************@news4.srv.hcvlny.cv.net >...

You need to re-design to db
Tables
Account <-- store user info
Trip <-- Stores user info and date
TripDetail <-- stores trip, where , from
Triptype ,-subtable

Unfortunately I can't do that. The database schema comes from a
canned vertical app and is imported from Ingress, so it's an area over
which I have pretty much zero control. The big problem is that we
don't have a master database of patients from the variety of medical
plans that we service, if I had that it would be a much simpler
problem, and this has been a problem in many other areas also.

[Erland Sommarskog]

[posted and mailed, please reply in news]

P.D.N. Tame (ww***@hotmail.com) writes:

A trip is a round trip if the date, account number, and passenger name
is the same and the origin street of record X equals the destination
street of record X+1. The problem is that X+1 could be the start of a
round trip and X+2 could be the completing leg of a round trip. So
you could have a scenario of hospital -> home -> doctor -> home in
which X is one-way and X+1 & X+2 form a round trip.

It wasn't clear from the post, but I am assuming that the column
trip_id is actually something that identifies the orders of the trips.

I composed this query. Not really sure whether it matches your needs,
but this is something you can use as a boilerplate for further refinement.

SELECT CASE WHEN b.trip_id IS NULL THEN 'Is one-way '
ELSE 'Is round-trip with trip ' + b.trip_id
END,
a.trip_id, a.acct_number, a.passenger, a.street, a.dest_street,
a.flat_rate, a.sCreated, a.sMeterOff, a.remark1, a.remark2, b.*
FROM zvoucherstodbf a
LEFT JOIN zvoucherstodbf b
ON (convert(int, a.trip_id) + 1 = convert(int, b.trip_id)
AND a.sCreated = b.sCreated
AND a.acct_number = b.acct_number
AND a.passenger = b.passenger
AND a.sMeterOff < b.sMeterOff
AND a.street = b.dest_street)
OR (convert(int, a.trip_id) - 1 = convert(int, b.trip_id)
AND a.sCreated = b.sCreated
AND a.acct_number = b.acct_number
AND a.passenger = b.passenger
AND a.sMeterOff > b.sMeterOff
AND a.dest_street = b.street)
ORDER BY a.trip_id
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp