Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 calculate

Author  Topic 

randomizer
Starting Member

8 Posts

Posted - 2014-04-10 : 18:53:12
I have a query that calculate the total amount of order details based on a particular order:

Select a.OrderID,SUM(UnitPrice*Quantity-Discount)
From [Order Details]
Inner Join Orders a
On a.OrderID=[Order Details].OrderID
Group by a.OrderID

My question is what if I wanted to create a formula to something like:

UnitPrice * Quantity - DiscountAmount Where DiscountAmount = UnitPrice Quantity * Discount

Do I need to create a function for that? Also is it possible to have m y query as a table variable?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-10 : 20:38:13
you don't need to create a function, just express the formula in your query will do


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

randomizer
Starting Member

8 Posts

Posted - 2014-04-10 : 21:02:45
quote:
Originally posted by khtan

you don't need to create a function, just express the formula in your query will do


KH
[spoiler]Time is always against us[/spoiler]





I ended up creating a function because I don't have a clue how to express the formula in my query and I've just seen your reply lol.

So here how my query looks like right now:

Select a.OrderID,CONVERT(varchar,a.OrderDate,101) as OrderDate,SUM(dbo.OrderAmount1(UnitPrice,Quantity,Discount)) as OrderAmount
From [Order Details]
Inner Join Orders a
On a.OrderID=[Order Details].OrderID
Group by a.OrderID,a.OrderDate

I'm also going to calculate a running total for this one so is this solution a good one?

Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-10 : 21:23:10
just change to something like below

SUM( ( UnitPrice * Quantity) - (UnitPrice * Quantity * Discount) )



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

randomizer
Starting Member

8 Posts

Posted - 2014-04-11 : 01:04:44
Alright, I've updated my query to include an employeeid and a customerid
------------------------------------
Declare @test Table
(
EmployeeID int,
CustomerID varchar(max),
OrderID int,
OrderDate date,
OrderAmount money,
RunningTotal int
)

Insert into @test(EmployeeID,CustomerID,OrderID,OrderDate,OrderAmount,RunningTotal)
Select e.EmployeeID,c.CustomerID,a.OrderID,CONVERT(varchar,a.OrderDate,101) as OrderDate,SUM((UnitPrice * Quantity) - (UnitPrice * Quantity * Discount)),null
From [Order Details]r
Inner Join Orders a
On a.OrderID=r.OrderID
Left Join Employees e
On e.EmployeeID=a.EmployeeID
Left Join Customers c
On c.CustomerID=a.CustomerID

Group by e.EmployeeID,c.CustomerID,a.OrderID,a.OrderDate
Order by e.EmployeeID,a.OrderID

Select * from @test
--------------------------------------------------------

What I'm trying to do right now is to calculate the running total that shows the total of orders based on employee and customer based on the order amount of orderid. I've managed to search for a code and maybe someone can help me out:

Update @test
Set @runQTY = RunningTotal = Case When EmployeeID = @prevCustID then @runQTY + OrderAmount else OrderAmount
End,
@prevCustID = EmployeeID
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-11 : 08:45:14
see:

http://technet.microsoft.com/en-us/library/ms189461.aspx

section "C"
Go to Top of Page

randomizer
Starting Member

8 Posts

Posted - 2014-04-13 : 22:28:54
quote:
Originally posted by gbritton

see:

http://technet.microsoft.com/en-us/library/ms189461.aspx

section "C"



Thanks

Now I have another question. Suppose I have a query that display this kind of data

Employee CustomerID OrderID
ABC Cust1 Order1
ABC Cust1 Order2
DEF Cust1 Order3
DEF Cust5 Order4
GHI Cust3 Order5
JKL Cust5 Order6

How can I update it so that the duplicate values will appear as null? Do I need to use a cursor?
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-13 : 22:44:28
can you identify the which values do you consider it as duplicate ?


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page
   

- Advertisement -