[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Function Help to Sum up Values

Posted on 2005-04-29
7
Medium Priority
?
202 Views
Last Modified: 2010-03-19
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
0
Comment
Question by:Saqib Khan
  • 4
  • 3
7 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13897212
>> 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
 
LVL 23

Author Comment

by:Saqib Khan
ID: 13897257
i am sorry, it was a dummy Script, it should be Integer.
0
 
LVL 23

Author Comment

by:Saqib Khan
ID: 13897542
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 28

Expert Comment

by:rafrancisco
ID: 13897566
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
 
LVL 23

Author Comment

by:Saqib Khan
ID: 13897595
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
 
LVL 28

Accepted Solution

by:
rafrancisco earned 2000 total points
ID: 13897730
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
 
LVL 23

Author Comment

by:Saqib Khan
ID: 13953127
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question