Function Help

I am using SQL 2000.
I have a table with Shipping Transactions in it (below).
Each record listed is a package, but the Amount shown is the total shipping
cost for all packages within a Shipment (Tracking)
I am trying to write a FUNCTION that will return the Total Shipment Amount
for a given PT (462714). Any Amount associated with a record that is voided
would not be included.
So, using the data below the function should return:
61.73
(61.73 = 46.04 (from 352414201) + 15.69 (from 354111785))
354925898 was voided so the 46.04 associated with it is excluded.
Recorded PT Order Tracking Total Weight Amount Void
1 462714 1589651 352414201 247 46.04 NO
2 462714 1589651 352414201 247 46.04 NO
3 462714 1589651 352414201 247 46.04 NO
19 462714 1589651 354925898 247 46.04 NO
20 462714 1589651 354925898 247 46.04 NO
21 462714 1589651 354925898 247 46.04 NO
22 462714 1589651 354925898 247 46.04 NO
23 462714 1589651 354925898 247 46.04 NO
24 462714 1589651 354925898 247 46.04 NO
25 462714 1589651 354925898 247 46.04 NO
26 462714 1589651 354925898 247 46.04 NO
27 462714 1589651 354925898 247 46.04 NO
28 462714 1589651 354925898 247 46.04 Y
29 462714 1589651 354925898 247 46.04 Y
30 462714 1589651 354925898 247 46.04 Y
31 462714 1589651 354925898 247 46.04 Y
32 462714 1589651 354925898 247 46.04 Y
33 462714 1589651 354925898 247 46.04 Y
34 462714 1589651 354925898 247 46.04 Y
35 462714 1589651 354925898 247 46.04 Y
36 462714 1589651 354925898 247 46.04 Y
37 462714 1589651 354111785 56 15.69 N
38 462714 1589651 354111785 56 15.69 NO
Any help would be greatly appreciated.
Thank youA number of ways, here is one (untested):
SELECT SUM(amount)
FROM
(
SELECT order,amount = MIN(amount)
FROM tablename
WHERE Void = 'N'
AND PT = 462714
GROUP BY order
) x
(Try and get the query itself working before incorporating it into a
function. Much easier to debug in QA that way, imho.)
A
"Kevin L" <no_spam@.not_real_email.com> wrote in message
news:%23yxDhihPGHA.720@.TK2MSFTNGP14.phx.gbl...
>I am using SQL 2000.
> I have a table with Shipping Transactions in it (below).
> Each record listed is a package, but the Amount shown is the total
> shipping cost for all packages within a Shipment (Tracking)
> I am trying to write a FUNCTION that will return the Total Shipment Amount
> for a given PT (462714). Any Amount associated with a record that is
> voided would not be included.
> So, using the data below the function should return:
> 61.73
> (61.73 = 46.04 (from 352414201) + 15.69 (from 354111785))
> 354925898 was voided so the 46.04 associated with it is excluded.
>
> Recorded PT Order Tracking Total Weight Amount Void
> 1 462714 1589651 352414201 247 46.04 NO
> 2 462714 1589651 352414201 247 46.04 NO
> 3 462714 1589651 352414201 247 46.04 NO
> 19 462714 1589651 354925898 247 46.04 NO
> 20 462714 1589651 354925898 247 46.04 NO
> 21 462714 1589651 354925898 247 46.04 NO
> 22 462714 1589651 354925898 247 46.04 NO
> 23 462714 1589651 354925898 247 46.04 NO
> 24 462714 1589651 354925898 247 46.04 NO
> 25 462714 1589651 354925898 247 46.04 NO
> 26 462714 1589651 354925898 247 46.04 NO
> 27 462714 1589651 354925898 247 46.04 NO
> 28 462714 1589651 354925898 247 46.04 Y
> 29 462714 1589651 354925898 247 46.04 Y
> 30 462714 1589651 354925898 247 46.04 Y
> 31 462714 1589651 354925898 247 46.04 Y
> 32 462714 1589651 354925898 247 46.04 Y
> 33 462714 1589651 354925898 247 46.04 Y
> 34 462714 1589651 354925898 247 46.04 Y
> 35 462714 1589651 354925898 247 46.04 Y
> 36 462714 1589651 354925898 247 46.04 Y
> 37 462714 1589651 354111785 56 15.69 N
> 38 462714 1589651 354111785 56 15.69 NO
>
> Any help would be greatly appreciated.
> Thank you
>|||There is some duplicate data (as far as the fields you are looking at for
returning the total) - the following function weeds through them to return
the sum. I might suggest finding some relation to this table to filter
rather than having to use the distincts in the function. This should get yo
u
started:
CREATE FUNCTION [dbo].[GetPTTotal] (
@.PT int
)
RETURNS money AS
BEGIN
declare @.amount money
declare @.trackingamounts table (tracking int, amount money, void bit default
0)
--grab all the records which might indicate a valid amount
insert @.trackingamounts (tracking, amount)
select distinct tracking, amount
from dbo.shippingtransaction
where PT = @.pt
and void like 'n%'
--go back and void any tracking number which has at least one void
update ta
set void = 1
FROM @.trackingamounts ta
join ( select distinct tracking, void
from dbo.shippingtransaction
where pt = @.pt
and void = 'Y'
) tab on ta.tracking = tab.tracking
-- output the sum of any remaining valid amounts
select @.amount = sum(amount) from @.trackingamounts where void = 0
RETURN @.amount
END
GO
"Kevin L" wrote:

> I am using SQL 2000.
> I have a table with Shipping Transactions in it (below).
> Each record listed is a package, but the Amount shown is the total shippin
g
> cost for all packages within a Shipment (Tracking)
> I am trying to write a FUNCTION that will return the Total Shipment Amount
> for a given PT (462714). Any Amount associated with a record that is voide
d
> would not be included.
> So, using the data below the function should return:
> 61.73
> (61.73 = 46.04 (from 352414201) + 15.69 (from 354111785))
> 354925898 was voided so the 46.04 associated with it is excluded.
>
> Recorded PT Order Tracking Total Weight Amount Void
> 1 462714 1589651 352414201 247 46.04 NO
> 2 462714 1589651 352414201 247 46.04 NO
> 3 462714 1589651 352414201 247 46.04 NO
> 19 462714 1589651 354925898 247 46.04 NO
> 20 462714 1589651 354925898 247 46.04 NO
> 21 462714 1589651 354925898 247 46.04 NO
> 22 462714 1589651 354925898 247 46.04 NO
> 23 462714 1589651 354925898 247 46.04 NO
> 24 462714 1589651 354925898 247 46.04 NO
> 25 462714 1589651 354925898 247 46.04 NO
> 26 462714 1589651 354925898 247 46.04 NO
> 27 462714 1589651 354925898 247 46.04 NO
> 28 462714 1589651 354925898 247 46.04 Y
> 29 462714 1589651 354925898 247 46.04 Y
> 30 462714 1589651 354925898 247 46.04 Y
> 31 462714 1589651 354925898 247 46.04 Y
> 32 462714 1589651 354925898 247 46.04 Y
> 33 462714 1589651 354925898 247 46.04 Y
> 34 462714 1589651 354925898 247 46.04 Y
> 35 462714 1589651 354925898 247 46.04 Y
> 36 462714 1589651 354925898 247 46.04 Y
> 37 462714 1589651 354111785 56 15.69 N
> 38 462714 1589651 354111785 56 15.69 NO
>
> Any help would be greatly appreciated.
> Thank you
>
>|||On Thu, 2 Mar 2006 12:51:27 -0500, Aaron Bertrand [SQL Server MVP]
wrote:

>A number of ways, here is one (untested):
>SELECT SUM(amount)
>FROM
>(
> SELECT order,amount = MIN(amount)
> FROM tablename
> WHERE Void = 'N'
> AND PT = 462714
> GROUP BY order
> ) x
>(Try and get the query itself working before incorporating it into a
>function. Much easier to debug in QA that way, imho.)
Hi Aaron,
Looking at the sample data Kevin posted, I suspect that it should be
SELECT SUM(amount)
FROM
(
SELECT order,amount = MIN(amount)
FROM tablename
AND PT = 462714
GROUP BY order
HAVING MAX(Void) < 'Y'
) x
One of the orders had 'NO' for voided in some rows and 'Y' in some
others, and he wanted to exclude the whole order.
BTW, Kevin: Please add a CHCEK constraint to the Void column so that
you'll have to deal with only one value for yes and one for no - this
mess with NO, N and Y (end maybe YES as well) all interspersed just
gives you unneeded problems.
Hugo Kornelis, SQL Server MVP|||> One of the orders had 'NO' for voided in some rows and 'Y' in some
> others, and he wanted to exclude the whole order.
Ah, good catch.