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 |
jtw5074
Starting Member
4 Posts |
Posted - 2013-06-20 : 18:52:32
|
I need help with automating a process in SQL Server. I want to use a loop to exclude certain records. Calculate the impact of each record to the “Average Selling Price” in the attached excel file. For example, If you were to exclude the Aguirre purchase of SKU A in the US, what would be the “Average Selling Price” for A in the US? Additionally, after itinerating over the 4 transaction for SKU B in the US, what is the ranking of each transaction regarding the impact to the ASP?country sku customer units sales unit price Average Selling Priceus a aguirre 10 100 10 15.55555556us a wirt 100 2000 20us a emrich 3 50 16.66667us b aguirre 34 100 2.941176 9.190627744us b wirt 67 2000 29.85075us b emrich 34 50 1.470588us b jory 20 50 2.5? |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-06-20 : 21:51:11
|
quote: If you were to exclude the Aguirre purchase of SKU A in the US, what would be the “Average Selling Price” for A in the US?
typically, you can add a WHERE condition to your query, exampleWHERE customer <> 'aguirre' quote: Additionally, after itinerating over the 4 transaction for SKU B in the US, what is the ranking of each transaction regarding the impact to the ASP?
What do you want to see / result that you want ? KH[spoiler]Time is always against us[/spoiler] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-21 : 02:04:18
|
what is the ranking of each transaction regarding the impact to the ASPsounds like usage of rank(),dense_rank() functions to me. One question here is on what basis you determine the impact?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
jtw5074
Starting Member
4 Posts |
Posted - 2013-06-21 : 13:38:15
|
1) I am trying to figure out how to use a loop to see how the average selling price is affected if you take out one of the customer's orders. For example, if you were to exclude one of the Aguirre purchases of SKU A, what would be the “Average Selling Price” for A in the US? I would like to use a loop so I can automate a process. 2) I am trying to figure out the ranking of each order's ASP. Meaning which order's ASP affects the total ASP of the product as a whole. Sorry for the confusion earlier. I pretty much copied and pasted his email. Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-22 : 02:28:32
|
1, you dont need a loop for that. You can apply a correlated subquery to find out the Average Selling Price excluding the selected SKU2, Again the earlier subquery method with a RANK query will give you ranking after excluding it.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|