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.
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 aOn a.OrderID=[Order Details].OrderIDGroup by a.OrderIDMy question is what if I wanted to create a formula to something like:UnitPrice * Quantity - DiscountAmount Where DiscountAmount = UnitPrice Quantity * DiscountDo 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] |
|
|
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 OrderAmountFrom [Order Details]Inner Join Orders aOn a.OrderID=[Order Details].OrderIDGroup by a.OrderID,a.OrderDateI'm also going to calculate a running total for this one so is this solution a good one? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2014-04-10 : 21:23:10
|
just change to something like belowSUM( ( UnitPrice * Quantity) - (UnitPrice * Quantity * Discount) ) KH[spoiler]Time is always against us[/spoiler] |
|
|
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)),nullFrom [Order Details]rInner Join Orders aOn a.OrderID=r.OrderIDLeft Join Employees eOn e.EmployeeID=a.EmployeeIDLeft Join Customers cOn c.CustomerID=a.CustomerIDGroup by e.EmployeeID,c.CustomerID,a.OrderID,a.OrderDateOrder by e.EmployeeID,a.OrderIDSelect * 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 @testSet @runQTY = RunningTotal = Case When EmployeeID = @prevCustID then @runQTY + OrderAmount else OrderAmount End,@prevCustID = EmployeeID |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
|
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.aspxsection "C"
ThanksNow I have another question. Suppose I have a query that display this kind of dataEmployee CustomerID OrderIDABC Cust1 Order1ABC Cust1 Order2DEF Cust1 Order3DEF Cust5 Order4GHI Cust3 Order5JKL Cust5 Order6How can I update it so that the duplicate values will appear as null? Do I need to use a cursor? |
|
|
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] |
|
|
|
|
|
|
|