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
LVL 23
Saqib KhanSenior DeveloperAsked:
Who is Participating?
 
rafranciscoCommented:
Try this:

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

     Begin

          Declare @output float
          Declare @output2 float
         
          Set @output = 0
          Set @output2 = 0
         
          if Exists(Select 1 From intranet.dbo.Po_header_lines where ORDERID=@ORDR AND ItemSku=@SKU)
           Begin
                    Select @output2 =  SUM(ItempoCost)  From intranet.dbo.Po_header_lines
                                   where ORDERID=@ORDR AND ItemSku=@SKU
          End
         
          Set @output = @output + @output2

          Select @output2 = SUM(ItemPocost) From intranet.dbo.Po_header_lines
          Where ItemSku = @sku AND OrderID != @ORDR

          Set @output = @output + @output2

     RETURN @output
                         

     End

GO
0
 
rafranciscoCommented:
>> is it possible to do? <<

Yes it is possible to do.

Why is your output declared as VARCHAR(1000) when you want a total?
0
 
Saqib KhanSenior DeveloperAuthor Commented:
i am sorry, it was a dummy Script, it should be Integer.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Saqib KhanSenior DeveloperAuthor Commented:
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
0
 
rafranciscoCommented:
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?
0
 
Saqib KhanSenior DeveloperAuthor Commented:
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
0
 
Saqib KhanSenior DeveloperAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.