asked on

Function Help to Sum up Values

Hi I have a Sql Qeries which Queries Two Tables. and returns the DISTINCT Recrds.
I want to write a Function so I can get the Total of Products in my result (because I want to keep Distinct records I dont want products to show in Query.

I am trying to write a function something like this...

ALTER Function clineTotal
(
@ORDR varchar(200),
@SKU varchar (100)
)
RETURNS varchar(1000)
AS

      Begin

            Declare @output varchar(1000)

                  if Exists(Select ItemSku From Intranet.dbo.po_header_lines where ORDERID=@ORDR AND ItemSku=@SKU)
                        Begin
                              -- ITEM is in that Table Calclute the COST OF IT, Add up to @output Variable
                        END
                  Else
                        Begin
                              -- ITEM is not in Table Grab the Product PO PRICE and Add up to @output variable
                  End

      RETURN -- THE Total of output variable


      End

GO

So i am passing the Order number and the Item SKU and return the Total Cost.
is it possible to do?

Thanks

rafrancisco

>> is it possible to do? <<

Yes it is possible to do.

Why is your output declared as VARCHAR(1000) when you want a total?

Saqib Khan

ASKER

i am sorry, it was a dummy Script, it should be Integer.

Saqib Khan

ASKER

I tried the Following.

1 . Runs Extremely Slow
2 . Returns Strange Records

ALTER Function clineTotal
(
      @ORDR varchar(200) = '',
      @SKU varchar (100) = ''
)
RETURNS float
AS

      Begin

            Declare @output float

            Set @output = 0

            if Exists(Select 1 From intranet.dbo.Po_header_lines where ORDERID=@ORDR AND ItemSku=@SKU)
                  Begin
                        Select @output = @output + ItempoCost From intranet.dbo.Po_header_lines
                                          where ORDERID=@ORDR AND ItemSku=@SKU
                  End
            Else
                  Begin
                        Select @output = @output + Max(ItemPocost/ItemQty) From intranet.dbo.Po_header_lines
                                    Where ItemSku = @sku
                  End

      RETURN @output


      End

GO

rafrancisco

Try this:

ALTER Function clineTotal
(
@ORDR varchar(200) = '',
@SKU varchar (100) = ''
)
RETURNS float
AS

Begin

Declare @output float

Set @output = 0

if Exists(Select 1 From intranet.dbo.Po_header_lines where ORDERID=@ORDR AND ItemSku=@SKU)
Begin
Select @output = SUM(ItempoCost) From intranet.dbo.Po_header_lines
where ORDERID=@ORDR AND ItemSku=@SKU
End
Else
Begin
Select @output = Max(ItemPocost/ItemQty) From intranet.dbo.Po_header_lines
Where ItemSku = @sku
End

RETURN @output

End

GO

I am just confused on what you want to do with the second select:

Select @output = @output + Max(ItemPocost/ItemQty) From intranet.dbo.Po_header_lines
Where ItemSku = @sku

What is this supposed to do?

Saqib Khan

ASKER

Select @output = @output + Max(ItemPocost/ItemQty) From intranet.dbo.Po_header_lines
Where ItemSku = @sku

This Mean the Item was never Shipped so get the Maximum Cost price for this Item.

Now in your Example where am I adding up the Sum of if and Else Statement.

1 Order may have 3 Items.
1 Item was shipped and other two were not shipped.

so for 1 item i will get the price from the if exist Part, For the rest of the 2 items i have to total Cost Price of Both Items.
and final result would be Sum of all Three Products.

Thanks

ASKER CERTIFIED SOLUTION

rafrancisco

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Saqib Khan

ASKER

I was not able to do it. Performance was REALLY slow plus the output was not correct as well.
SO I used ASP to go around this problem.

Thanks