Home All Groups Group Topic Archive Search About

Function Help

microsoft.public.sqlserver.programming
Author
2 Mar 2006 4:44 PM
Kevin L
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

Author
2 Mar 2006 5:51 PM
Aaron Bertrand [SQL Server MVP]
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.)

A


Show quote
"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
>
Author
2 Mar 2006 9:35 PM
Hugo Kornelis
On Thu, 2 Mar 2006 12:51:27 -0500, Aaron Bertrand [SQL Server MVP]
wrote:

Show quote
>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
Author
2 Mar 2006 9:40 PM
Aaron Bertrand [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.
Author
2 Mar 2006 5:55 PM
tonysell@nospam.nospam
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 you
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



Show quote
"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 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
>
>
>

AddThis Social Bookmark Button

Post Other interesting topics
use trigger
Is subquery bad?
Generating the perfect Primary Key: Using CRC32? Using MD5?
block comment???
Increased Datetime Precision