Showing posts with label solution. Show all posts
Showing posts with label solution. Show all posts

Sunday, March 25, 2012

Cursor in MS Yukon / SQL 2005.

Is Cursor Included in Yukon/SQL 2005. or any alternet solution for that?Yes 2005 still supports cursors. There is already an alternative solution in
earlier versions: set-based SQL code. If you tell us what you want to do
then we might be able to give you a specific solution.
David Portas
SQL Server MVP
--|||Thanks for your reply......
Just i want to know MS has included cursor in Yukon? if yes then any
new feature in cursor? and if no what is the alernet solution for that
thats what i wanted to know....
HItendra|||I'm not aware of any new features of cursors in 2005 but then I haven't
looked for them. If it's important to you then the best place for that
question is the SQL Server 2005 beta newsgroups.
Cursors are almost never necessary in SQL Server and they are seldom a good
idea. Since you haven't told us what you want to do I can't tell you what th
e
alternatives are. "Cursor" is not a specification of a problem - it's just
one type of solution. The better solutions are usually set-based. Set-based
statements operate on the whole set of rows (like SELECT/UPDATE/DELETE/INSER
T
statements) instead of one row at a time (like a cursor). Most of the time i
t
pays to find a set-based solution rather than a cursor because set-based cod
e
is generally easier to develop, test, support and maintain as well as being
much more efficient than most cursor solutions.
David Portas
SQL Server MVP
--
"hitendra15@.gmail.com" wrote:

> Thanks for your reply......
> Just i want to know MS has included cursor in Yukon? if yes then any
> new feature in cursor? and if no what is the alernet solution for that
> thats what i wanted to know....
> --
> HItendra
>|||(hitendra15@.gmail.com) writes:
> Thanks for your reply......
> Just i want to know MS has included cursor in Yukon? if yes then any
> new feature in cursor?
There are no new features with cursors, as far as I know. The version
with the feature-craze on cursors was SQL 7, and I still haven't understood
all of that. Keep your cursors INSENSITIVE, STATIC or FAST_FORWARD, and
stay away from the default keyset cursors.

> and if no what is the alernet solution for that thats what i wanted to
> know....
Rather cursors are the alternate solution when you simply cannot find a
set-based solution. That happens, but it is not that frequent. Here is a
quick example of a set-based solution. Say that you have an employee
table, and you are to update the salaries. Generally there is a two
percent increase, but staff that earns less than 15000 SEK/month gets
300 SEK extra, and all women receive an extra 0.5% raise. This can be
done in one UPDATE:
UPDATE employees
SET salary = 1.02 * salary +
CASE WHEN salary < 15000 THEN 300 ELSE 0 END +
CASE sex WHEN 'K' THEN 1.005 * salary ELSE 0 END
Ah, when I think of it, if you really like iterattion, yes there is now
a new way to do this in SQL 2005, as you now can write stored procedures
in a .Net language such as C#. This gives new oppurtunities to implement
good solutions in SQL Server - and also a lot of new oppurtunities to
implement really poor solutions if implemented improperly.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> all women receive an extra 0.5% raise. This can be
> done in one UPDATE:
Fine example, but is it legal in any jurisdiction you know of?
As you've mentioned, iteration is a feature of cursors and we do have new
non-cursor features in SQL2005 that are relevant to those types of problem.
The CROSS APPLY operator means we no longer need to use cursors for expandin
g
an adjacency list and similar tasks. Also, the ROW_NUMBER function is a
convenient way to implement sequences over data. So there should be even les
s
reason to use cursors in SQL2005.
David Portas
SQL Server MVP
--|||Yes, cursors are included in SQL 2005. I do not know of any new features of
cursors. They are not the preferred way of doing work (since SQL 7.0), so I
do not use them unless I absolutely have to. This follows MS suggestions and
may be the reason cursors do not change much.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"hitendra15@.gmail.com" wrote:

> Is Cursor Included in Yukon/SQL 2005. or any alternet solution for that?
>|||Yes, they have cursors, but if you had been writing good SQL, you would
not be using them more than a few times in 10-20 years of production
coding anyway.|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Fine example, but is it legal in any jurisdiction you know of?
Interesting question. If an employer over here would do this, but give the
extra raise to men, there would be one h*ll of a noise. But for women?
My guess, is that they would get away with it. Even more so, if this was
the result of negotiations with the union. Oh, well, wrong newsgroup
I think.

> As you've mentioned, iteration is a feature of cursors and we do have
> new non-cursor features in SQL2005 that are relevant to those types of
> problem. The CROSS APPLY operator means we no longer need to use cursors
> for expanding an adjacency list and similar tasks. Also, the ROW_NUMBER
> function is a convenient way to implement sequences over data. So there
> should be even less reason to use cursors in SQL2005.
Great examples!
Of what I can think of, left is the case when you have a stored procedure
which you want to reuse, and you don't find it worth the effort to
rewrite the stored procedure for handle an input set. And then of course
the few exceptional cases where cursors actually are faster.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||thank you guys i understood that there is a cursor but its not improved
no new features to it,
so it seems me that Procedures, UDT, Functions, triggers etc are Part
of SQL CLR[We can make], cursor is not....am i correct?
Hitendra

Cursor help PLEASE!

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)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" <wwphx@.hotmail.com> wrote in message
news:bcc5d277.0312081450.10e74cfc@.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)|||> 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|||"David Branch" <MOVING_ON_UP@.HOTMAIL.COM> wrote in message news:<3mcBb.328$T24.143624@.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.|||[posted and mailed, please reply in news]

P.D.N. Tame (wwphx@.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, sommar@.algonet.se

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

Thursday, March 8, 2012

Cubic Equation

Does anyone have or know of an sql implementation of a general solution to a cubic equation. I've been searching and have found implementations in several other languages (C++, Fortran, basic...) but not SQLYou should be able to transfer the logic from the C++ example to TSQL, though it might be kind of clunky. TSQL is not designed to solve equations.

blindman

Tuesday, February 14, 2012

CS1595: 'Sanghi.Global' is defined in multiple

hi when i try to run my c#.net web application i m getting following
error
if any one know the solution pls let me know
Compiler Error Message: *CS1595: 'Sanghi.Global' is defined in multiple
places; using definition from
'c:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\Temporary
ASP. NETFiles\sanghiweb\3cb18c71\212986e7\ass
embly\dl2\0730bee1\0028ca9a_b915
c501\sanghiweb.DLL'
*Source Error:*
Line 26:
Line 27:
[System.Runtime.CompilerServices.CompilerGlobalScopeAttribute()]
Line 28: public class Global_asax : Sanghi.Global {
Line 29:
Line 30: private static bool __initialized = false;I think you posted in the wrong newsgroup.
Anyways, clear the temporary internet files and try.
"Mahesh" wrote:

> hi when i try to run my c#.net web application i m getting following
> error
> if any one know the solution pls let me know
> Compiler Error Message: *CS1595: 'Sanghi.Global' is defined in multiple
> places; using definition from
> 'c:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\Temporary
> ASP. NETFiles\sanghiweb\3cb18c71\212986e7\ass
embly\dl2\0730bee1\0028ca9a_b9
15c501\sanghiweb.DLL'
> *Source Error:*
> Line 26:
> Line 27:
> [System.Runtime.CompilerServices.CompilerGlobalScopeAttribute()]
> Line 28: public class Global_asax : Sanghi.Global {
> Line 29:
> Line 30: private static bool __initialized = false;
>